Put the resutl of an SQL query in an array

Using ADO objects, a select query returns an RecordSet object if more than one result is returned.  So, if you want to go through the records in that RecordSet, you would iterate through a "while" loop while the RecordSet is not EOF.

See the example at https://support.smartbear.com/testcomplete/docs/reference/program-objects/ado/createadoquery.html

If you want specific help with your code, please post your code here and we'll see what we can do to help.

I suggest storing it as JSON.  OR use the example i provide below

For example here is some code that will grab the results from a stored procedure using the SQLUtilities Script provided by tristaanogre

var _testData = [];
var queryObject = SQLUtilities.NewQueryObject(
"EXEC [dbo].[Get_TestData] " +
"@TestID = :TESTDATA", {TESTDATA: "4"});
var query = SQLUtilities.NewSQLQuery(queryObject);
query.open();
var record = query.execute();
while (!record.EOF) {
  _testData.push([
    record.Fields.Item('Row').Value,//0
    record.Fields.Item('OrderNum').Value,//1
    record.Fields.Item('NumOfParams').Value,//2
    record.Fields.Item('FunctionPath').Value,//3
    record.Fields.Item('Function').Value,//4
    record.Fields.Item('Parameter').Value,//5
    record.Fields.Item('ParameterType').Value,//6
    record.Fields.Item('Data').Value//7
  ]);
  record.MoveNext();
}
query.closeAndNull;

I take the fields and push them into an array.  I hope this helps.

You can make this also more dynamic and use this function. Just pass the SQLObject

function ExtractObjectFromSQLResult(obj_SQLObject,bool_DoNotSchowResults){
  
  var obj_Result = []
  //Get first item
  obj_SQLObject.First()
  Log.Message("Recors found: " +obj_SQLObject.RecordCount)
  
  var j = 0
  //Itterate through result and put into array
  while (!obj_SQLObject.EOF)
  {
    var obj_OneSet = {}
    if(bool_DoNotSchowResults === undefined || bool_DoNotSchowResults != true){
      Log.Message("Fields found: " +obj_SQLObject.FieldCount)
    }
    for(var i = 0; i<obj_SQLObject.FieldCount;i++){
      var str_FullName = obj_SQLObject.Field(i).fullname
      var str_Value
      //If the datatype is a bigint, take the DisplayText
      if(obj_SQLObject.Field(i).DataType === 25){
      	str_Value = obj_SQLObject.Field(i).DisplayText
      }
      else{
      	str_Value = obj_SQLObject.Field(i).value
      }
      
      if(bool_DoNotSchowResults === undefined || bool_DoNotSchowResults != true){
        Log.Message("Get line " +i+ ": " +str_FullName +";" + str_Value)
      }
      //AddToAobject
      obj_OneSet[str_FullName] = str_Value
      
    }
    //add to object array
    obj_Result[j] = obj_OneSet
    j++
    obj_SQLObject.Next();  
  }  
  return obj_Result
}

Hi,

Thank for your reply,

Yes but still have to specify the fields if you want to get them.

What I really would like to do is ti get the whole query result in an array or a map or a tuple or something else

thanks

Phil

Hey Guys,

Well thank you for the storing loop :p

I'll make good use of it.

Have a great day

Philippe