Forum Discussion

Adagio's avatar
Adagio
Frequent Contributor
8 years ago

how to get DB table into a 2D array

Hi, I'm trying to fetch the contnts of a DB table into an array then iterating over the array to read the elements.

 

the DB table has 10 rows and 4 columns, but in the code below:

elementList[0,0] and elementList[1,0] are fetching the same value. 
  conn.Open();
  var set;
  //execute query for a payment type
        set = conn.Execute_(
        "select  XXXXXXX "); 
    
  var elementList = new Array();
  set.MoveFirst();
  while(! set.EOF)
  {
  
  elementList.push(
     set.Fields.Item("Col1").Value 
    ,set.Fields.Item("Col2").Value
    ,set.Fields.Item("Col3").Value 
    ,set.Fields.Item("Col4").Value 
    );
    set.MoveNext();
  }
Log.Message(elementList[0,0]);
Log.Message(elementList[1,0]);
Log.Message(elementList);

In short all the value appear in a string separated by ','. I need to parse it to a table structure. How can I get these values in an array so that I can fetch each value using row column indexes? 

 

any help would be greatly appreciated!

Thank you

Abhi

 

 

  • cunderw's avatar
    cunderw
    Community Hero
    8 years ago

    Here is a little generic function that will take a record and convert it to an object. 

     

    /**
 * @function
 * @param {object} rec - the result of an ADO query
 * @return {object} retVal - the record converted to a javascript object.
 */
function recordToObject(rec) {
  var retVal = {};
  try {
    if(!rec.EOF) {
      for(var i = 0; i < rec.Fields.Count; i++ ) {
        retVal[rec.Fields.Item(i).Name] =  rec.Fields.Item(i).Value == null ? "" : aqString.Trim(rec.Fields.Item(i).Value);
      }
    }
    else {
      Log.Warning("There is nothing in the record set"); 
    }
  }
  catch(err) {
    Log.Warning("There was an error building object. See additional information",err.message + "\n" + err.stack);
  }
  finally {
    return retVal;
  }
}


    //you can then push the results to an array to call by index and column name.
    function test() {
      //code to get record
      var elementList = [];
      set.MoveFirst();
      while(!set.EOF) {
        elementList.push(recordToObject(set));
        set.MoveNext();
      }
      Log.Message(elementList[0].columnName);
    }

     

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    8 years ago

    What if you change your code to this?

      conn.Open();
  var set;
  //execute query for a payment type
        set = conn.Execute_(
        "select  XXXXXXX "); 
    
  var elementList = new Array();
  set.MoveFirst();
  while(! set.EOF)
  {
  
  elementList.push([
     set.Fields.Item("Col1").Value 
    ,set.Fields.Item("Col2").Value
    ,set.Fields.Item("Col3").Value 
    ,set.Fields.Item("Col4").Value] 
    );
    set.MoveNext();
  }
Log.Message(elementList[0][0]);
Log.Message(elementList[1][0]);
Log.Message(elementList);

    Basically, you end up with an array of arrays which is what a 2D array is, effectively.

    • Adagio's avatar
      Adagio
      Frequent Contributor
      8 years ago

      Thank you Robert! It helps but problem is still not solved.

      This time, I have one whole row of the table going in each element of the array i.e.

       

      elementList[0][0] = FirstRow
      elementList[1][0] = SecondRow
      elementList[1][0] = ThirdRow

       and each cell of that row is separated by ',' . Still trying to get a 2d array like structure in the result.

       

      Thank you

      Abhi

      • tristaanogre's avatar
        tristaanogre
        Esteemed Contributor
        8 years ago

        Something is wrong in your implementation.  Please make sure that you have all the code changes I included.  In my tests, what I posted works as desired.  

         

        The important piece is here

         

          elementList.push([
     set.Fields.Item("Col1").Value 
    ,set.Fields.Item("Col2").Value
    ,set.Fields.Item("Col3").Value 
    ,set.Fields.Item("Col4").Value] 
    );
    set.MoveNext();
  }

        Note the square brackets within the "push" call.  If you don't have those, you won't get the desired nested array.  Please double check this.