cancel
Showing results for 
Search instead for 
Did you mean: 

How to make TestComplete execute custom sql queries with a variable name in where condition

SOLVED
Contributor

How to make TestComplete execute custom sql queries with a variable name in where condition

I'm trying to add Database checkpoint by specifying a custom sql query like

Select * from Table1 where EmployeeID = id

In the previous step i fetch the value from the UI and store it in the id variable. But when i execute the test

DBTables.CheckNotification.RowCount

it is fetching the count of all the records in the table, rather than the specific record with the id. the previous line in the code to fetch the id from UI is working good

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Leader

Re: How to make TestComplete execute custom sql queries with a variable name in where condition

What method are you using to connect to SQL ? The syntax for a SQL query in ADO for example looks different than the SQL query you would use in a standard SQL and it may be that your query isn't sending the parameter correctly.  

 

So for example your ADO command would specify the query as follows:

 

var ADOConnection = ADO.CreateConnection();
//Configure the ADOConnection var ADOCommand = ADO.CreateCommand();
//Configure the ADOCommand

ADOCommand.CommandText='select * from Table1 where Employeeid=? and EmployeeName=?'; //Here's the important bit: ADOCommand.Parameters.Item(0) = employeeIDValue; ADOCommand.Parameters.Item(1) = emplayeeNameValue; var RecordSet = ADOCommand.Execute(); //etc...

 

 


-------------------------------------------------
Standard syntax disclaimers apply
Regards,
6 REPLIES 6
Highlighted
Community Leader

Re: How to make TestComplete execute custom sql queries with a variable name in where condition

What method are you using to connect to SQL ? The syntax for a SQL query in ADO for example looks different than the SQL query you would use in a standard SQL and it may be that your query isn't sending the parameter correctly.  

 

So for example your ADO command would specify the query as follows:

 

var ADOConnection = ADO.CreateConnection();
//Configure the ADOConnection var ADOCommand = ADO.CreateCommand();
//Configure the ADOCommand

ADOCommand.CommandText='select * from Table1 where Employeeid=? and EmployeeName=?'; //Here's the important bit: ADOCommand.Parameters.Item(0) = employeeIDValue; ADOCommand.Parameters.Item(1) = emplayeeNameValue; var RecordSet = ADOCommand.Execute(); //etc...

 

 


-------------------------------------------------
Standard syntax disclaimers apply
Regards,
Contributor

Re: How to make TestComplete execute custom sql queries with a variable name in where condition

@RUDOLF_BOTHMA i have used ADO.CreateConnection before and used the exact same piece of code to connect to db and fetch results. My question here is specific to the step where we can add custom sql queries while adding a DB checkpoint, how do i replace a variable in the where condition in this case. I'm able to fetch results and get recordcount, when i just mention a harcoded value instead of the variable. using the below code

DBTables.DBTable1.RowCount

DBCheckPoint_Wizard.PNG

Community Hero

Re: How to make TestComplete execute custom sql queries with a variable name in where condition

This is one of the limitations of DB Checkpoints.  They don't allow for passing in variables.  Hence why @RUDOLF_BOTHMA  suggested you use the ADO connections to build and create your own comparison code.


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
Contributor

Re: How to make TestComplete execute custom sql queries with a variable name in where condition

Thank you @tristaanogre and @RUDOLF_BOTHMA . Got it, I will use the ADO connections. But i'm also curious to know on what would be a good way to store these sql queries instead of having it in the code. 

Community Hero

Re: How to make TestComplete execute custom sql queries with a variable name in where condition

You can create the query as a .SQL text file.  You can then read the text file into a variable in TestComplete and pass the contents to your ADO connection as the SQL query string.


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
Contributor

Re: How to make TestComplete execute custom sql queries with a variable name in where condition

Thank you @tristaanogre 

New Here?
Join us and watch the welcome video:
API Testing Mistake #2
APITestingMistake#2