i developed a MS Access desktop database app a decade ago that im attempting to rebuild using node, electron and mysql now that im a recovering windows user.
ive started by recreating the simplest form in the app after importing the necessary parts of the db: https://i.sstatic.net/PbmaF.jpg.
the problem is in the datacontrol's operation (bottom left), specifically when using the new record button (">+"). while it sometimes properly increments the record number ("336" in the screen shot), other times it does not. when it sticks, continuing to add records eventually makes it suddenly catch up before it eventually sticks again. debugging indicates the cause being that rows.length in newRecord() is not being reported correctly.
it seems safe to assume this is related to my frustratingly inexplicable inability to fully comprehend asynchronous execution, callbacks, promises and async/await, despite being moderately intelligent and banging my head against countless articles on the subject over the past four days. i guess i just need to see an example of how to do it as it applies to my situation before im able to better grasp the concept. so feel free to ignore the great many flaws in this noobie's code below, some of which he is aware, and im sure a great many of which he is not, and just focus on how i might get an accurate rows.length.
<script>
var new_record = false;
var position = 0;
var last_record = 0;
var sql_columns = [];
var sql_values = [];
var mysql = require('mysql2/promise');
var bluebird = require('bluebird');
var pool = mysql.createPool({
host : 'localhost',
user : 'user',
password : 'password',
database : 'db',
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0,
Promise: bluebird
});
async function getData() {
new_record = false;
const [rows, fields] = await pool.query('SELECT * FROM qryStudents');
last_record = rows.length - 1;
if (position > last_record) { position = last_record; }
refreshDataControl();
document.getElementById("student-id").value = rows[position].idsStudentID;
document.getElementById("first-name").value = rows[position].chrFName;
document.getElementById("last-name").value = rows[position].chrLName;
document.getElementById("ssn").value = rows[position].chrSSN;
if (rows[position].ysnActive == 1 || rows[position].ysnActive == true) {
document.getElementById("active").checked = true;
} else if (rows[position].ysnActive == 0 || rows[position].ysnActive == false) {
document.getElementById("active").checked = false;
}
document.getElementById("enrollment-address1").value = rows[position].chrAddress1;
document.getElementById("enrollment-address2").value = rows[position].chrAddress2;
document.getElementById("enrollment-city").value = rows[position].chrCity;
document.getElementById("enrollment-state").value = rows[position].chrState;
document.getElementById("enrollment-zip").value = rows[position].chrZIP;
document.getElementById("enrollment-phone").value = rows[position].chrPhone;
document.getElementById("lda-address1").value = rows[position].chrLDAAddress1;
document.getElementById("lda-address2").value = rows[position].chrLDAAddress2;
document.getElementById("lda-city").value = rows[position].chrLDACity;
document.getElementById("lda-state").value = rows[position].chrLDAState;
document.getElementById("lda-zip").value = rows[position].chrLDAZIP;
document.getElementById("lda-phone").value = rows[position].chrLDAPhone;
}
async function saveRecord() {
var sql;
var i;
if (sql_columns.length == 0) { return; }
if (new_record == true) {
sql = "INSERT INTO tblStudents SET ";
if (sql_columns.indexOf("ysnActive") == -1) {
sql_columns.push("ysnActive");
sql_values.push(document.getElementById("active").checked);
}
} else { sql = "UPDATE tblStudents SET "; }
for (i = 0; i < sql_columns.length; i++) {
if (i > 0) { sql = sql + ", "; }
sql = sql + sql_columns[i] + " = ?";
}
if (new_record == false) { sql = sql + " WHERE idsStudentID = ?;"; } else { sql = sql + ";"; }
await pool.query(sql, sql_values);
sql_columns = [];
sql_values = [];
}
function buildSQL(type, name, id) {
var index = sql_columns.indexOf(name);
if (index == -1) { sql_columns.push(name); }
if (type == "text") {
if (index == -1) { sql_values.push(document.getElementById(id).value); } else { sql_values[index] = document.getElementById(id).value; }
} else if (type == "checkbox") {
if (index == -1) { sql_values.push(document.getElementById(id).checked); } else { sql_values[index] = document.getElementById(id).checked; }
}
if (new_record == false) { sql_values.push(document.getElementById("student-id").value); }
}
async function deleteRecord() {
const { dialog } = require('electron').remote;
const options = {
type: 'question',
buttons: ['Yes', 'No'],
defaultId: 1,
title: 'Question',
message: 'Do you want to do this?',
}
var choice = dialog.showMessageBoxSync(null, options);
if (choice == 0) {
var sql = "DELETE FROM tblStudents WHERE idsStudentID = " + document.getElementById("student-id").value;
await pool.query(sql);
getData();
}
}
function firstRecord() {
saveRecord();
position = 0;
getData();
}
function previousRecord() {
saveRecord();
position--;
getData();
}
function nextRecord() {
saveRecord();
position++;
getData();
}
function lastRecord() {
saveRecord();
position = last_record;
getData();
}
async function newRecord() {
saveRecord();
const [rows, fields] = await pool.query('SELECT * FROM qryStudents');
new_record = true;
last_record = rows.length - 1;
position = last_record + 1;
console.log("NR: rows.length = " + rows.length + ", position = " + position + ", last_record = " + last_record);
refreshDataControl();
}
function refreshDataControl() {
console.log("RDC: last_record = " + last_record + ", position + 1 = " + (position + 1));
console.log("------------------------------------------------");
document.getElementById("record-number").value = position + 1;
if (position == 0) {
document.getElementById("first-record").disabled = true;
document.getElementById("previous-record").disabled = true;
document.getElementById("next-record").disabled = false;
document.getElementById("last-record").disabled = false;
} else if (position >= last_record) {
document.getElementById("first-record").disabled = false;
document.getElementById("previous-record").disabled = false;
document.getElementById("next-record").disabled = true;
document.getElementById("last-record").disabled = true;
} else {
document.getElementById("first-record").disabled = false;
document.getElementById("previous-record").disabled = false;
document.getElementById("next-record").disabled = false;
document.getElementById("last-record").disabled = false;
}
}
window.resizeTo(550, 700);
window.onload = getData();
</script>
debug output showing the first two newRecord() runs functioning corectly, followed by five instances rows.length being incorrectly reported, before catching up on the final run.
[4151:0215/134309.258750:INFO:CONSOLE(300)] "RDC: last_record = 331, position + 1 = 333", source: file:///home/antioch/enrollmentdb/students-enrollments.html (300)
[4151:0215/134309.258909:INFO:CONSOLE(301)] "------------------------------------------------", source: file:///home/antioch/enrollmentdb/students-enrollments.html (301)
[4151:0215/134315.820950:INFO:CONSOLE(294)] "NR: rows.length = 333, position = 333, last_record = 332", source: file:///home/antioch/enrollmentdb/students-enrollments.html (294)
[4151:0215/134315.821515:INFO:CONSOLE(300)] "RDC: last_record = 332, position + 1 = 334", source: file:///home/antioch/enrollmentdb/students-enrollments.html (300)
[4151:0215/134315.822020:INFO:CONSOLE(301)] "------------------------------------------------", source: file:///home/antioch/enrollmentdb/students-enrollments.html (301)
[4151:0215/134321.729763:INFO:CONSOLE(294)] "NR: rows.length = 333, position = 333, last_record = 332", source: file:///home/antioch/enrollmentdb/students-enrollments.html (294)
[4151:0215/134321.729865:INFO:CONSOLE(300)] "RDC: last_record = 332, position + 1 = 334", source: file:///home/antioch/enrollmentdb/students-enrollments.html (300)
[4151:0215/134321.729974:INFO:CONSOLE(301)] "------------------------------------------------", source: file:///home/antioch/enrollmentdb/students-enrollments.html (301)
[4151:0215/134333.178796:INFO:CONSOLE(294)] "NR: rows.length = 334, position = 334, last_record = 333", source: file:///home/antioch/enrollmentdb/students-enrollments.html (294)
[4151:0215/134333.178873:INFO:CONSOLE(300)] "RDC: last_record = 333, position + 1 = 335", source: file:///home/antioch/enrollmentdb/students-enrollments.html (300)
[4151:0215/134333.178944:INFO:CONSOLE(301)] "------------------------------------------------", source: file:///home/antioch/enrollmentdb/students-enrollments.html (301)
[4151:0215/134339.487472:INFO:CONSOLE(294)] "NR: rows.length = 335, position = 335, last_record = 334", source: file:///home/antioch/enrollmentdb/students-enrollments.html (294)
[4151:0215/134339.488114:INFO:CONSOLE(300)] "RDC: last_record = 334, position + 1 = 336", source: file:///home/antioch/enrollmentdb/students-enrollments.html (300)
[4151:0215/134339.488276:INFO:CONSOLE(301)] "------------------------------------------------", source: file:///home/antioch/enrollmentdb/students-enrollments.html (301)
[4151:0215/134344.938782:INFO:CONSOLE(294)] "NR: rows.length = 336, position = 336, last_record = 335", source: file:///home/antioch/enrollmentdb/students-enrollments.html (294)
[4151:0215/134344.939238:INFO:CONSOLE(300)] "RDC: last_record = 335, position + 1 = 337", source: file:///home/antioch/enrollmentdb/students-enrollments.html (300)
[4151:0215/134344.939395:INFO:CONSOLE(301)] "------------------------------------------------", source: file:///home/antioch/enrollmentdb/students-enrollments.html (301)
[4151:0215/134350.297774:INFO:CONSOLE(294)] "NR: rows.length = 337, position = 337, last_record = 336", source: file:///home/antioch/enrollmentdb/students-enrollments.html (294)
[4151:0215/134350.297856:INFO:CONSOLE(300)] "RDC: last_record = 336, position + 1 = 338", source: file:///home/antioch/enrollmentdb/students-enrollments.html (300)
[4151:0215/134350.297931:INFO:CONSOLE(301)] "------------------------------------------------", source: file:///home/antioch/enrollmentdb/students-enrollments.html (301)
[4151:0215/134355.734959:INFO:CONSOLE(294)] "NR: rows.length = 339, position = 339, last_record = 338", source: file:///home/antioch/enrollmentdb/students-enrollments.html (294)
[4151:0215/134355.735069:INFO:CONSOLE(300)] "RDC: last_record = 338, position + 1 = 340", source: file:///home/antioch/enrollmentdb/students-enrollments.html (300)
[4151:0215/134355.735468:INFO:CONSOLE(301)] "------------------------------------------------", source: file:///home/antioch/enrollmentdb/students-enrollments.html (301)```
the answer appears to have been as simple as prepending await to saveRecord() in newRecord().
the new and improved newRecord():
async function newRecord() {
await saveRecord();
const [rows, fields] = await pool.query('SELECT * FROM qryStudents');
new_record = true;
last_record = rows.length - 1;
position = last_record + 1;
refreshDataControl();
}