Forum Discussion
Kitt
2 years agoRegular 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'");
}
Related Content
- 3 years agoRaj48
- 11 years agovwanaskar
- 5 years agobach_ls
- 6 months agosohailalam2696
Recent Discussions
- 8 hours agovladd1