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)
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'");
}
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
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'");
}