Ask a Question

How to get a SQL query result into a (log) file (and excel file) if the result hast 2 or more rows

rhu
Occasional Contributor

How to get a SQL query result into a (log) file (and excel file) if the result hast 2 or more rows

I want to do a sql query where i get a table with 2 or more lines and want to get the output as log file within TestComplete and optional also written to a excel fiile

SQL query could be like:
Select NN as lastname from  database  test
where lastname = 'test'
Order by lastname DESC

Executing the sql query I get e.g. 5 rows containing the "lastname"

Steps in Testcomplete:
I create a variable named "result" with Type DB Table where I enter the query
Pressing view results in the Edit DB Table Variable Value "Designer" gets me the correct results (e.g. 5 rows)

Now I create a manual Teststep
Item Log
Details Mode Table Date
As Value I select the variable "result".

If I run the test I always get the result from the first line but not the total table.
How can i get all results from the SQL query (e.g. 5 rows)

 

3 REPLIES 3
rraghvani
Valued Contributor

Have a look at ADO.CreateRecordset Method, which allow you to work with all recordsets (tables, query results, etc.) from you scripts.

Kitt
Regular Contributor

EDIT: **1st RESPONSE**

If you wanted to pass a query to the database instead of referencing an .mdb file, it would look something like this:

 

 

 

// executes sql statement capturing results in a record set
function sqlRecordSet(query)
{
  // set log and variables
  Log.Message("SQL Query: " + query);
  var AConnection, result;
  
  // create a connection object
  AConnection = ADO.CreateConnection();
  
  // specify the connection string
  AConnection.ConnectionString = "Provider=SQLNCLI11;"
    + "Server=tcp:" + ProjectSuite.Variables.dboDatabaseServer + ".database.windows.net;"
    + "Database=" + ProjectSuite.Variables.dboDatabase + ";"
    + "Uid=" + ProjectSuite.Variables.dboDatabaseUser + ";" 
    + "Pwd=" + ProjectSuite.Variables.dboDatabasePass.DecryptedValue + ";";
  
  // open the connection
  AConnection.Open();
  
  // Create a new Recordset object
  var Rs = ADO.CreateRecordset();
  Rs.Open(AConnection.Execute(query));
  
  // Read data from the recordset and post them to the test log
  Log.AppendFolder("SQL Record Set");
  Rs.MoveFirst();
  while(! Rs.EOF)
  {
    Log.Message(Rs.Fields.Item(0).Value);
    result = Rs.Fields.Item(0);
    Rs.MoveNext();
  }
  return result;
  
  // Close the recordset and connection
  Rs.Close();
  AConnection.Close();
}

 

 

 

 

Similarly, you could use an array to accomplish the same thing, counting the results then looping through to post the values to the log: 

 

 

 

// executes sql statement capturing results in array
function sqlQueryArray(query) {
  // set log, variables, & array
  Log.Message("SQL Query: " + query);
  var AConnection, recSet, results;
  var dataSet = [];
  
  // create a connection object
  AConnection = ADO.CreateConnection();
  
  // specify the connection string
    AConnection.ConnectionString = "Provider=SQLNCLI11;"
    + "Server=tcp:" + ProjectSuite.Variables.dboDatabaseServer + ".database.windows.net;"
    + "Database=" + ProjectSuite.Variables.dboDatabase + ";"
    + "Uid=" + ProjectSuite.Variables.dboDatabaseUser + ";" 
    + "Pwd=" + ProjectSuite.Variables.dboDatabasePass.DecryptedValue + ";";
  
  // open the connection
  AConnection.Open();
  
  // execute a simple query
  recSet = AConnection.Execute(query);
   
  // iterate through query results and insert data into the test log
  for (i = 0; i <= recSet.RecordCount - 1; i++) {
    dataSet.push(" " + recSet.Fields.Item(0).Value);
    recSet.MoveNext();
  }  
  ProjectSuite.Variables.DynamicVarInt = recSet.RecordCount; // track number of records in array
  return dataSet;
  
  // close the connection
  AConnection.Close();
}

 

 

 

 

You would then just pass your query in from another function (I am also calling my envt handler function, which just updates the user/pass base on the envt I'm testing in - you can ignore this line if you handle in a different way):

 

 

 

 

function testRecordSetAndArray() {
  // handle envt db user/pass
  CommonSQL.sqlDatabaseHandler("sqldb-" + 
 qString.ToLower(ProjectSuite.Variables.Environment));
  
  // return array
  var arrayData = CommonSQL.sqlQueryArray("SELECT [column] FROM [table] WHERE [column] = 'Test Complete'");
  Log.Message(arrayData);
  
  // return record set
  CommonSQL.sqlRecordSet("SELECT [column] FROM [table] WHERE [column] = 'Test Complete'");
}

 

 

 

 

The log results look similar, but the array needs to be set as a variable before you call and log it. The array function also prints the values to the same line so it is better used as a reference (or if you have have a large dataset and don't want to write a ton of new lines to the log). The record set is better served for working with the data, as @rraghvani referenced 

8Kit_0-1659704771543.png

 

FULL SCOPE: using the CreateRecordSet method to also save the records to an excel file

 

// create a records set, write values to the log, and save results to excel
function sqlRecordSet(query)
{
  // set log and variables
  Log.Message("SQL Query: " + query);
  var AConnection, result;
  
  // Get the sheet of the Excel file
  var excelFile = Excel.Open("C:\\yourFilePath\\testRecordSet.xlsx");
  var excelSheet = excelFile.SheetByTitle("Sheet1");
  
  // create a connection object
  AConnection = ADO.CreateConnection();
  
  // specify the connection string
  AConnection.ConnectionString = "Provider=SQLNCLI11;"
    + "Server=tcp:" + ProjectSuite.Variables.dboDatabaseServer + ".database.windows.net;"
    + "Database=" + ProjectSuite.Variables.dboDatabase + ";"
    + "Uid=" + ProjectSuite.Variables.dboDatabaseUser + ";" 
    + "Pwd=" + ProjectSuite.Variables.dboDatabasePass.DecryptedValue + ";";
  
  // open the connection
  AConnection.Open();
  
  // Create a new Recordset object
  var Rs = ADO.CreateRecordset();
  Rs.Open(AConnection.Execute(query));
  
  // Read data from the recordset and post them to the test log
  Log.AppendFolder("SQL Record Set");
  Rs.MoveFirst();
  while(! Rs.EOF)
  {
    Log.Message(Rs.Fields.Item(0).Value);
    result = Rs.Fields.Item(0);
    
    // Write the obtained data into a new row of the file
    var rowIndex = excelSheet.RowCount + 1;
    excelSheet.Cell("A", rowIndex).Value = result;
    excelFile.Save();

    Rs.MoveNext();
  }
  return result;
  
  // Close the recordset and connection
  Rs.Close();
  AConnection.Close();
}

function test() {
  // handle envt db user/pass
  CommonSQL.sqlDatabaseHandler("sqldb-" + aqString.ToLower(ProjectSuite.Variables.Environment));
  
  // return record set and save to excel
  CommonSQL.sqlRecordSet("SELECT [column] FROM [table] WHERE [column] = 'Test Complete'");
}

 

[[ kITt: keep IT testing ]]
Kitt
Regular Contributor

EDIT: **2nd RESPONSE**

For some reason my first response was blocked, so I guess I'll try again with the short answer:

 

 

function sqlRecordSet(query)
{
  // set log and variables
  Log.Message("SQL Query: " + query);
  var AConnection, result;
  
  // Get the sheet of the Excel file
  var excelFile = Excel.Open("C:\\yourFilePath\\testRecordSet.xlsx");
  var excelSheet = excelFile.SheetByTitle("Sheet1");
  
  // create a connection object
  AConnection = ADO.CreateConnection();
  
  // specify the connection string
  AConnection.ConnectionString = "Provider=SQLNCLI11;"
    + "Server=tcp:" + ProjectSuite.Variables.dboDatabaseServer + ".database.windows.net;"
    + "Database=" + ProjectSuite.Variables.dboDatabase + ";"
    + "Uid=" + ProjectSuite.Variables.dboDatabaseUser + ";" 
    + "Pwd=" + ProjectSuite.Variables.dboDatabasePass.DecryptedValue + ";";
  
  // open the connection
  AConnection.Open();
  
  // Create a new Recordset object
  var Rs = ADO.CreateRecordset();
  Rs.Open(AConnection.Execute(query));
  
  // Read data from the recordset and post them to the test log
  Log.AppendFolder("SQL Record Set");
  Rs.MoveFirst();
  while(! Rs.EOF)
  {
    Log.Message(Rs.Fields.Item(0).Value);
    result = Rs.Fields.Item(0);
    
    // Write the obtained data into a new row of the file
    var rowIndex = excelSheet.RowCount + 1;
    excelSheet.Cell("A", rowIndex).Value = result;
    excelFile.Save();

    Rs.MoveNext();
  }
  return result;
  
  // Close the recordset and connection
  Rs.Close();
  AConnection.Close();
}

function test() {
  // handle envt db user/pass
  CommonSQL.sqlDatabaseHandler("sqldb-entitlements-" + aqString.ToLower(ProjectSuite.Variables.Environment));
  
  // return record set and save to excel
  CommonSQL.sqlRecordSet("SELECT [column] FROM [table] WHERE [column] = 'Test Complete'");
}

 

8Kit_0-1659706584027.png

 

 

[[ kITt: keep IT testing ]]
cancel
Showing results for 
Search instead for 
Did you mean: