I am developing a system that requires me to query an SQL database, it is located on the local network but NOT on the same system (not SQLExpress.)
I have managed to query a user for input on the webpage and send that information to Main.JS, however I am not sure on the most effective way to query the database.
I'm very new to both SQL and electron.js, so lots of detail would be preferred, thank you!
Following other Stack Overflow questions (notably), I have:
preload.js:
const invoke = (channel, args, cb = () => { return }) => {
ipcRenderer.invoke(channel, args).then((res) => {
cb(res);
});
};
const handle = (channel, cb) => {
ipcRenderer.on(channel, function (Event, message) {
cb(Event, message);
});
};
contextBridge.exposeInMainWorld("GlobalApi", {
invoke: invoke,
handle: handle
});
let get = function (path) {
let data = dbFuncions.readSomeDatafromDB("path");
return data; // Returning the function itself is a no-no shown below
// return dbFunctions.readSomeDatafromDB("path"); Don't do this
}
contextBridge.exposeInMainWorld("myCoolApi", {
get: get
});
renderer.js:
const { ipcRenderer } = require('electron');
const loginForm = document.getElementById('login-form');
const usernameInput = document.getElementById('username');
const passwordInput = document.getElementById('password');
loginForm.addEventListener('submit', (event) => {
event.preventDefault();
const username = usernameInput.value;
const password = passwordInput.value;
// This runs when the user submits their username and password.
ipcRenderer.send('login', { username, password });
});
// This runs when the username and password has been checked (in main.js) and a success (true) or failure (false) has been recieved.
ipcRenderer.on('login-status', (event, isSuccess) => {
const failureMessage = document.createElement('p');
const successMessage = document.createElement('p');
if (isSuccess) {
successMessage.textContent = 'Login successful';
successMessage.style.color = 'green';
loginForm.appendChild(successMessage);
setTimeout(() => {
loginForm.removeChild(successMessage);
}, 2000);
} else {
failureMessage.textContent = 'Incorrect details.';
failureMessage.style.color = 'red';
loginForm.appendChild(failureMessage);
setTimeout(() => {
loginForm.removeChild(failureMessage);
}, 2000);
}
});
main.js:
const { app, BrowserWindow, ipcMain } = require('electron');
var Connection = require('tedious').Connection;
var Request = require('tedious').Request
var TYPES = require('tedious').TYPES;
var dbFunctions = require('sql') //I know I'm missing a function here. The 'sql module' mentioned in the Stack Overflow
const path = require('path');
let mainWindow;
var config = {
server: 'your_server.database.windows.net', //update me
authentication: {
type: 'default',
options: {
userName: 'your_username', //update me
password: 'your_password' //update me
}
},
options: {
// If modifying to work with Microsoft Azure (which I'm not), you need encryption:
encrypt: true,
database: 'your_database' //update me
}
};
var connection = new Connection(config);
connection.on('connect', function (err) {
// If no error, then good to proceed.
console.log("Connected");
executeStatementLogin();
});
function executeStatementLogin() {
// This needs updating!
var request = new Request("SELECT username, password FROM table_name WHERE username = " + , function (err) {
if (err) {
console.log(err);
}
});
// This needs updating!
request.addParameter('Name', TYPES.NVarChar, 'SQL Server Express 2019');
request.addParameter('Number', TYPES.NVarChar, 'SQLEXPRESS2019');
request.addParameter('Cost', TYPES.Int, 11);
request.addParameter('Price', TYPES.Int, 11);
request.on('row', function (columns) {
columns.forEach(function (column) {
if (column.value === null) {
console.log('NULL');
} else {
console.log("Product id of inserted item is " + column.value);
}
});
});
// Close the connection after the final event emitted by the request, after the callback passes
request.on("requestCompleted", function (rowCount, more) {
connection.close();
});
}
function createWindow() {
mainWindow = new BrowserWindow({
width: 800,
height: 600,
webPreferences: {
nodeIntegration: true,
contextIsolation: false,
enableWebSQL: true,
preload: path.join(__dirname, 'preload.js')
},
});
mainWindow.loadFile(path.join(__dirname, 'index.html'));
mainWindow.on('closed', () => {
mainWindow = null;
});
}
app.on('ready', () => {
createWindow();
});
app.on('window-all-closed', () => {
if (process.platform !== 'darwin') {
app.quit();
}
});
// This is old code that I made. This runs when the user clicks submit and has username and password. This could be modified to do the SQL query, too
ipcMain.on('login', (event, loginData) => {
const { username, password } = loginData;
// Here, you can perform the necessary login verification logic
// For demonstration purposes, we'll just log the username and password
console.log(`Received login request. Username: ${username}, Password: ${password}`);
let data = dbFunctions.get(path);
window.webContents.send(
path,
data
);
if (username == 'exampletest' && password == 'testexample') { //Hardcoded solution to test if ifSuccess works and the result gets sent back (which it does)
isSuccess = true
} else {
isSuccess = false
}
setTimeout(() => {
event.reply('login-status', isSuccess);
}, 2000);
});
ipcMain.on('login', (event, loginData) => is old code that I made. It has both the username and the password the user inputted already. This could potentially be modified to perform the SQL, by creating the SQL query there and executing this.
Alternatively, modifying the solution I found here which is all of preload.js and config, connection, types, dbFunctions, request and executeStatementLogin in main.js.
When I run the program, it loads, however on submit a runtime error is given. Both solutions run at the same time. My solution works fine (but does not implement SQL), and the alternative solution throws an error that dbFunctions does not have a 'get' function. This is because I do not know what the SQL module used to actually GET dbFunctions is, as it is seemingly never specified. Once I have dbFunctions, I can start to modify the code that needs to be updated (//update me), though I am not sure how I am going to modify the username and password in the config file based on user input.
Any help at all would be appreciated. Thank you! (assume I know nothing)
