1

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!

Mariane
  • 25
  • 3
  • Looks like there are several issues with your code that lead to the unexpected result. Assuming that you are using the https://www.npmjs.com/package/ibm_db package, a call to the query method returns an array of rows, such as [ { '1': 1 } ]. If your goal is to collect all rows you need to make several changes in your queryAll method: (1) initialize variable allData as an empty array and (2) concatenate the arrays using [concat](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/concat) instead of +. – ptitzler Mar 27 '17 at 07:12

0 Answers0