I am trying to append tables together in my database but for some reason I am getting excess rows when I check the data object I am appending each table to.
So here are the tables I have in my database (dashDB):
ILI_NAMES = table of table names
S0100 = a data set with lat and longs and other data (1864 rows)
S1234 = another data set with lat and longs and other data (1866 rows)
Here is the code to grab the table names from ILI_NAMES to pass into a function. This works fine!
app.get('/select_full_map', function(req, res) {
//get ILITable Names
ibmdb.open(dbConnString, function(err, conn) {
if (err) {
console.error("Error: ", err);
return;
}
console.log("**********CONNECTING TO DATABASE**********");
var query4names = "SELECT * FROM ILI_NAMES";
conn.query(query4names, function(err, rows) {
if (err) {
console.log("Error: ", err);
return;
}
var iliTableName = rows;
//console.log(iliTableName[1]);
var numTables = rows.length;
queryAll(iliTableName, numTables);
conn.close(function() {
console.log("**********Connection closed successfully.**********");
});
});
Appending to the code above, the queryAll function makes a query string using the table names and is put inside a while loop in order to callback data from each table in the database and append into allData. The problem I am having now is that I am getting more rows than expected when I check for the number of rows in allData. I have added the console output and what I expect the console output to be below.
function queryAll(tableName, numTables){
var i = 0;
var query = "";
var allData = "";
while (i < numTables){
query = "SELECT FEATURE_NUMBER, LAT___DEG_DEC_NAD_83_, LONG___DEG_DEC_NAD_83___UTM_ZONE_11_, GFLAG, COMMENTS FROM " + tableName[i].ILI_SECTION_NAME + " WHERE FEATURE_NUMBER IS NOT NULL ORDER BY LAT___DEG_DEC_NAD_83_";
fetchRows(query, i, function(err,data){
if (err) {
// error handling code goes here
console.log("ERROR : ",err);
}
console.log ('NUMBER OF ROWS FROM fetchRows: ' + data.length);
allData += data;
console.log ('NUMBER OF ROWS FROM allData: ' + allData.length);
//outputAll(data);
return;
});
i++;
}
// made this to test, outputs same result
function outputAll(data){
allData += data;
console.log ('NUMBER OF ROWS FROM allData: ' + allData.length);
//res.end(JSON.stringify(allData));
}
}
function fetchRows (query, i, callback){
conn.query(query, function(err, rows) {
if (err) {
callback(err, null);
} else
callback(null, rows);
console.log('READING SINGLE TABLE DATA FROM QUERY: ' + query);
console.log ('NUMBER OF ROWS FROM SINGLE TABLE QUERY: ' + rows.length);
});
}
});
});
Actual Output Console:
**********CONNECTING TO DATABASE**********
NUMBER OF ROWS FROM fetchRows: 1864
NUMBER OF ROWS FROM allData: 29823
READING SINGLE TABLE DATA FROM QUERY: SELECT FEATURE_NUMBER, LAT___DEG_DEC_NAD_83_, LONG___DEG_DEC_NAD_83___UTM_ZONE_11_, GFLAG, COMMENTS FROM S1234 WHERE FEATURE_NUMBER IS NOT NULL ORDER BY LAT___DEG_DEC_NAD_83_
NUMBER OF ROWS FROM SINGLE TABLE QUERY: 1864
NUMBER OF ROWS FROM allData: 59678
READING SINGLE TABLE DATA FROM QUERY: SELECT FEATURE_NUMBER, LAT___DEG_DEC_NAD_83_, LONG___DEG_DEC_NAD_83___UTM_ZONE_11_, GFLAG, COMMENTS FROM S0100 WHERE FEATURE_NUMBER IS NOT NULL ORDER BY LAT___DEG_DEC_NAD_83_
NUMBER OF ROWS FROM SINGLE TABLE QUERY: 1866
**********Connection closed successfully.**********
My expected Output Console:
**********CONNECTING TO DATABASE**********
NUMBER OF ROWS FROM fetchRows: 1864
NUMBER OF ROWS FROM allData: 1864
READING SINGLE TABLE DATA FROM QUERY: SELECT FEATURE_NUMBER, LAT___DEG_DEC_NAD_83_, LONG___DEG_DEC_NAD_83___UTM_ZONE_11_, GFLAG, COMMENTS FROM S1234 WHERE FEATURE_NUMBER IS NOT NULL ORDER BY LAT___DEG_DEC_NAD_83_
NUMBER OF ROWS FROM SINGLE TABLE QUERY: 1864
NUMBER OF ROWS FROM allData: 3730
READING SINGLE TABLE DATA FROM QUERY: SELECT FEATURE_NUMBER, LAT___DEG_DEC_NAD_83_, LONG___DEG_DEC_NAD_83___UTM_ZONE_11_, GFLAG, COMMENTS FROM S0100 WHERE FEATURE_NUMBER IS NOT NULL ORDER BY LAT___DEG_DEC_NAD_83_
NUMBER OF ROWS FROM SINGLE TABLE QUERY: 1866
**********Connection closed successfully.**********
My suspicions of what might be causing the bug is the asynchronous nature or a reiterating while loop. I am terribly new and I admit, I have no clue what I am doing. But I did try best and scavenged every source I could find. Any help would be greatly appreciated. Thanks in advance!