Ask a Question

How to get a Database table into an object (2D Array may be), and read the data

SOLVED
Al2
Occasional Contributor

How to get a Database table into an object (2D Array may be), and read the data

Hello,

 

I'm running a UI tests which fetches data continuously from a DB table. This table has 10000 rows and 20 columns.

Test continuously fetches the data, and uses if for modifications, and many column values get updated. It goes thru three iterations, and in each iteration column values get changed.

 

The problem I'm facing: The way I'm fetching these values in an object takes significant time(~ 2 hours) to get all these values into an object (10000 rows x 20 Columns  = 200,00 cells) because each value is being fetched individually. 

 

What I really need help with is, a way to fetch the whole data table into an object (may be an array), and then be able to read each cell(row, column) value buy using the combination of columnname and row Indicator e.g. Column_Name.row[0] and also much quicker(Within 5 minutes range).

 

The records fetched from this snippet are being pushed into another array to have a table like object. 

 

function dbRecordToObject(record) {
  var returnValue = {};
  try {
    if(!record.EOF) {
      for(var i = 0; i < rec.Fields.Count; i++ ) {
        returnValue[record.Fields.Item(i).Name] =  record.Fields.Item(i).Value == null ? "" : aqString.Trim(record.Fields.Item(i).Value);
      }
    }
    else {
      Log.Warning("Empty record set"); 
    }
  }
  catch(err) {
    Log.Warning("Error : " +err.message);
  }
  finally {
    return returnValue;
  }
}

 

 

Any help would be appreciated!

 

Thank you

Al2

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
BenoitB
Community Hero

Re: How to get a Database table into an object (2D Array may be), and read the data

Ok, i'd done two posts because you asked about two things if i understood correctly.

 

First post is about transforming linear data in 2D array.

Use google translate to translate the documentation because i made effort to document then make the effort to translate  😁

And getTokenBySeparator take a token from a string, using a separator to find tokens ... 😂

 

Second post is about, root cause of your problem, speed.

If you look the second post, the speed problem could be resolved simply, call the set.GetRows() instead of navigating into ADO result set, so it would be:

 

 

 

let set         = conn.Execute_(exeQuery); 
let elementList = [];        
if ((set != null) && (set.RecordCount != 0))
  elementList = set.GetRows().toArray();
return elementList;

 

 

What you do was to browse Ado result set one by one to rebuild an array instead of fetching all data directly, so it's very slow.

 

Some info on GetRows() is here:

https://www.w3schools.com/asp/met_rs_getrows.asp

 

BTW try to implement error management and connection established waiting as it is in my code. Your code will be robust and in case of errors you can know where and what.

 

And don't put comment on trivial things (e.g. // Declare an array) or // Closing connection), comments are for advanced technical or algorithm points.

 

I invite you to take a glance to this post about method writing:

https://community.smartbear.com/t5/TestComplete-General-Discussions/Share-your-writing-guidelines-on...

 

 

Un sourire et ça repart

View solution in original post

8 REPLIES 8
BenoitB
Community Hero

Re: How to get a Database table into an object (2D Array may be), and read the data

Perhaps my function can help you ..  You have to adapt it to your input  but after ou acces data as array named from column. Just look at the logic.

 

Please post your work in feedback if it works.

 

  /**
   * <a id="system.stringToNamedArray"></a>
   * Transformer une chaîne de couples clés/valeurs en tableau nommé de nom clé et avec les lignes de valeurs
   * @function
   * @author Biache Benoit
   * @memberOf qa.system
   * @Param {string} Donnees - Données à transformer
   * @Param {boolean} [Normalize = false] - <b>true</b> alors effectue un trim des données et sur le nom du tableau le passe en majuscule
   * @Param {string} [Separator = ";"] - Caractère séparateur des couples clé/Valeur
   * @Param {string} [ValueSeparator = "="] - Caractère séparateur de la clé et de la Valeur
   * @returns {object} Renvoie <b>result.error == ""</b> si la fonction s'est exécutée sans erreur<br> et <b>result.data</b> contient les données et <b>result.count</b> contient le nombre de lignes obtenues
   * Si la fonction a échoué alors la valeur de l'erreur est dans <b>result.error</b> et un log de warning est créé
   */
  qa.system.stringToNamedArray = function(Donnees = null, Normalize = false, Separator = ";", ValueSeparator = "=") {
    var FunctionResult = { data  : [],
                           count : 1,
                           error : "" };
    try {
      if (Donnees == null)
        throw Error("Le paramètre obligatoire Donnees n'est pas renseigné.")
      var fields;
      var temp;
      temp = qa.system.getTokenBySeparator(Donnees, FunctionResult.count, Separator);
      while (temp != "") {
        fields = temp.split(ValueSeparator)
        if (Normalize) {
          fields[0] = qa.system.trim(fields[0]);
          fields[0] = aqString.ToUpper(fields[0]);
          fields[1] = qa.system.trim(fields[1]);
        }
        FunctionResult.data[fields[0]] = fields[1];
        FunctionResult.count++;
        temp = qa.system.getTokenBySeparator(Donnees, FunctionResult.count);
      };  
    }    
    catch(e) {
      FunctionResult.error = qa.system.logExceptionByLevel(e, "qa.system.stringToNamedArray(" + Donnees + ", " + Normalize + ", " + Separator + "," + ValueSeparator + ")", false, "Warning");
      FunctionResult.count = 0;
    }
    finally {
      return FunctionResult;
    }
  }

 

If you don't succeed, i can write the proper one but learning is better by doing  ;^)

 

Un sourire et ça repart

BenoitB
Community Hero

Re: How to get a Database table into an object (2D Array may be), and read the data

And the easiest way to fetch data from DB is using ADO, something like this (here your Sql command will be your SELECT  * FROM table):

 

      Connection = ADO.CreateConnection();
      Connection.ConnectionString = ConnectionStr;
      Connection.Open();
      Command = ADO.CreateCommand();
      while (Connection.State == adStateConnecting) {
        aqUtils.Delay(qa.system.time.smaller);
        i = i + 1;
        if (i > 100) {
          throw Error("Connection not opened within 10s");
        }
      }
      Command.CommandText      = Sql; 
      Command.CommandTimeout   = CommandTimeout;
      Command.ActiveConnection = Connection;
      Results                  = Command.Execute();
      ErrCount = Connection.errors.count;
      if (ErrCount !== 0) {
        for (Err = 0; Err == ErrCount; Err++){
          Err   = Connection.errors.item;
          Error = Error + Err;
        }
        Connection.Errors.Clear;
        throw Error;
      }
      else {
        if ((Results != null) && (Results.RecordCount !== 0)) {
          FunctionResult.Lines = Results.GetRows().toArray();
        }
        FunctionResult.Count = Results.RecordCount;
      }

 

Un sourire et ça repart

Al2
Occasional Contributor

Re: How to get a Database table into an object (2D Array may be), and read the data

@BenoitB  Thanks much for providing some insights into this.

 

I see that you have a function as an array variable which has a JSON object 'functionResult'. That would store the 'data table' in 'data' array as a value. 'data' gets all the elements from 'temp' object which in turn contains row data separated by commas(,) ?

 

I have a few questions. 

  • Are you using the StringToNamedArray anywhere else to fetch the data from the array object after table is loaded into 'data' ?
  • What are these params : Donnes, Normalize referring to ?
  • I'm not so clear about    FunctionResult.Lines = Results.GetRows().toArray();  Lines is not defined in JSON
  • what's the getTokenBySeparator utility doing? 

I really appreciate your help and the direction you've given. I'm still trying to understand this piece.

 

On the other hand, I have another function which has an ADO connection and runs a query. After that it makes a call to the routine:

dbRecordToObject(record)

This fetches single record at a time and that gets pushed to a set like this: but this is really slow

var set = conn.Execute_(exeQuery); 
        
//declare an array 
  var elementList = [];  

  Log.Message("INFO:   set.EOF is: " +set.EOF);
  if(set.EOF == false){

    set.MoveFirst();
    while(!set.EOF)
    {
    //get the result set into the array
    elementList.push(dbRecordToObject(set));
      set.MoveNext();
    }
  //Closing the connection
  conn.Close();
  return elementList;

 

 

Thank you

Al2

 

  

BenoitB
Community Hero

Re: How to get a Database table into an object (2D Array may be), and read the data

Ok, i'd done two posts because you asked about two things if i understood correctly.

 

First post is about transforming linear data in 2D array.

Use google translate to translate the documentation because i made effort to document then make the effort to translate  😁

And getTokenBySeparator take a token from a string, using a separator to find tokens ... 😂

 

Second post is about, root cause of your problem, speed.

If you look the second post, the speed problem could be resolved simply, call the set.GetRows() instead of navigating into ADO result set, so it would be:

 

 

 

let set         = conn.Execute_(exeQuery); 
let elementList = [];        
if ((set != null) && (set.RecordCount != 0))
  elementList = set.GetRows().toArray();
return elementList;

 

 

What you do was to browse Ado result set one by one to rebuild an array instead of fetching all data directly, so it's very slow.

 

Some info on GetRows() is here:

https://www.w3schools.com/asp/met_rs_getrows.asp

 

BTW try to implement error management and connection established waiting as it is in my code. Your code will be robust and in case of errors you can know where and what.

 

And don't put comment on trivial things (e.g. // Declare an array) or // Closing connection), comments are for advanced technical or algorithm points.

 

I invite you to take a glance to this post about method writing:

https://community.smartbear.com/t5/TestComplete-General-Discussions/Share-your-writing-guidelines-on...

 

 

Un sourire et ça repart

View solution in original post

sonya_m
Community Manager

Re: How to get a Database table into an object (2D Array may be), and read the data

Great suggestion Benoit, thank you for the detailed reply.

 

@Al2 Did you have a chance to try this out?


Sonya Mihaljova
Community and Education Specialist

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
Al2
Occasional Contributor

Re: How to get a Database table into an object (2D Array may be), and read the data

@BenoitB  Thanks much for help. I tried adjusting my code based on your suggestions. Getrows() is definitely the best way to get the data into the record set quickly. I wasn't aware of this method. 

 

I could get all the data into the array, and all the data here is in the form of a liner array.

 

FunctionResult

 

 One thing that I struggled with was to convert this into 2D array and append the column names so that I can reference these values using the Array, Index and the column name. I had spent some time on it earlier when you provided these suggestions, but haven't had any tangible success. I tried to implement an idea similar to what you suggested  in this piece, but haven't got that working.

 

 var FunctionResult = { data  : [],
                           count : 1,
                           error : "" };
    try {
      if (Donnees == null)
        throw Error("Le paramètre obligatoire Donnees n'est pas renseigné.")
      var fields;
      var temp;
      temp = qa.system.getTokenBySeparator(Donnees, FunctionResult.count, Separator);
      while (temp != "") {
        fields = temp.split(ValueSeparator)
        if (Normalize) {
          fields[0] = qa.system.trim(fields[0]);
          fields[0] = aqString.ToUpper(fields[0]);
          fields[1] = qa.system.trim(fields[1]);
        }
        FunctionResult.data[fields[0]] = fields[1];
        FunctionResult.count++;
        temp = qa.system.getTokenBySeparator(Donnees, FunctionResult.count);
      };  

 

 

Thank you!

Al2

 

BenoitB
Community Hero

Re: How to get a Database table into an object (2D Array may be), and read the data

If data are not to be kept secret, could you attach a jfile of FuntionResult ?

 

let DataInString = JSON.stringify(FunctionResult);

aqFile.WriteToTextFile("C:\\MyData.txt", DataInString, aqFile.ctANSI, true);

 

Or explain more (a sample) the structure of obtained FunctionResult ?

Un sourire et ça repart

Al2
Occasional Contributor

Re: How to get a Database table into an object (2D Array may be), and read the data

Actually, writing to the external file is something I ruled out in the beginning. The best thing would be to keep this data in an object and read thru it. Yes there are data security concerns, too. 

 

Here's the sample of results that I get using the method  getRows() (R -Row and C- Column). Since this is in the linear form, it's not very helpful.

 

var rslt = [R0C0 , R1C0 R2C0, R3C0,    R0C1, R1C1, R2C1, R3C1,     R0C2, R1C2, R2C2, R3C2 ];

 

I appreciate your time and help!

 

Thank you

Al2

cancel
Showing results for 
Search instead for 
Did you mean: