Forum Discussion

S_Seydel's avatar
S_Seydel
Contributor
10 years ago

SQL insert with JScript arrays and adodb

Hello,

 

I have a problem with the following function:

 

function sqlInsert()
{ 
  // static name of the database table
  var table = "Names";
  // calls the function which opens the database connection 
  var connection = openDatabaseConnection();
  // calls the function which opens table "Names"
  var recordset = openRecordset(connection, table);
  // array object with table column names as strings
  var columns = new Array("forename", "surname");
  // array object with values to insert as strings
  var values = new Array("John", "Doe");
    
  // writes the message to the test log 
  // "forename = John and surname = Doe"
  Log.Message(
  columns[0] + " = " + values[0] + " and  " + 
  columns[1] + " = " + values[1]
  );
  // calls adodb AddNew function on the opened recordset 
  // to insert the values into the table
  recordset.AddNew(columns, values);
    
  // calls a function to update the recordset, 
  // close the recordset and close the database connection
  updateAndClose = updateAndClose(recordset, connection);
}

In this state the function works just fine, a new tupel is inserted into table "Names" with correct values in the correct columns.

However the whole log message only was for error searching purposes and I would like to remove it, but if I remove it and run the function I only get an empty new tupel in table "Names".

 

In debug mode the arrays for columns and values always look like shown in the following image, no matter if the code to create the log message is active or commented out.

 

arrays in debugger.jpg

Could anyone explain to me please, why I get an emtpy tupel, when the arrays are not used to create a log message before feeding them into the AddNew function?

  • TanyaYatskovska's avatar
    TanyaYatskovska
    10 years ago

    Hi,

    You need to pass either strings or the safeArray arrays. You code doesn’t work as you pass the JScript array the the AddNew method. You can use two approaches:

    1. Update your table in the following way:
    recordset.AddNew();
recordset("forename") = "John";
recordset("surname") = "Doe";
recordset.Update;

     

    1. Convert a JScript array to safeArray in the following way:
    function ArrayToSafeArray(array) {
    var dictionary = new ActiveXObject("Scripting.Dictionary");
    for (var i = 0; i < array.length; i++) {
      dictionary.add(i, array[i]);
    }
    return dictionary.Items();
  }

  var columns = new Array("forename", "surname");
  var values = new Array("John", "Doe");
  recordset.AddNew(ArrayToSafeArray(columns), ArrayToSafeArray(values));
  recordset.Update();

4 Replies

Sort By
  • TanyaYatskovska's avatar
    TanyaYatskovska
    SmartBear Alumni (Retired)
    10 years ago

    Hi S_Seydel,

     

    What TestComplete version are you using? Can you check if this behavior persists in TestComplete 10.6 (the latest product version)?

     

    • S_Seydel's avatar
      S_Seydel
      Contributor
      10 years ago

      Hi TanyaGorbunova,

       

      thank you for your reply.

       

      I use TestComplete version 10.60.3387.7.

      I started to use TestComplete with this version, so the script also was not written with an earlier version of TestComplete, which has been updated, if this information is of interest.

      • TanyaYatskovska's avatar
        TanyaYatskovska
        SmartBear Alumni (Retired)
        10 years ago

        Hi,

        You need to pass either strings or the safeArray arrays. You code doesn’t work as you pass the JScript array the the AddNew method. You can use two approaches:

        1. Update your table in the following way:
        recordset.AddNew();
recordset("forename") = "John";
recordset("surname") = "Doe";
recordset.Update;

         

        1. Convert a JScript array to safeArray in the following way:
        function ArrayToSafeArray(array) {
    var dictionary = new ActiveXObject("Scripting.Dictionary");
    for (var i = 0; i < array.length; i++) {
      dictionary.add(i, array[i]);
    }
    return dictionary.Items();
  }

  var columns = new Array("forename", "surname");
  var values = new Array("John", "Doe");
  recordset.AddNew(ArrayToSafeArray(columns), ArrayToSafeArray(values));
  recordset.Update();