I am struggling with similar issue as well.
My scenario:
As a POC, I have created some simple Keyword Driven tests, which do the following:
1. start application;
2. add a Customer using the UI;
3. Close application;
I want to extend this test a step 4, which is a validation on the database. This validation should check whether the added customer has actually been written to the database. In this validation a simple SQL query would be enough for the moment. The SQL Query looks (simplified) as follows:
Select CustomerName, ID, Address
from Table.Customers
where CustomerName is "ENTERED_CUSTOMER_NAME".
The value "ENTERED_CUSTOMER_NAME" in the where clausule of the SQL statement, should match the value entered in the Keywordtest in the UI (step 2). And these values should be dynamically (read test data from Excel file).
The validation at step 4 should pass if the result of the query is > 0 (a record is found), and it should fail if no record is found (which would indicate the entered customer data does not exist in the database).
Question: is this possible with TestComplete? If so, could this be handled by Database Checkpoints or something?
This sounds like a DBTable checkpoint with the exception that you need a dynamic "WHERE" clause. So... I don't think this is available out of the box. However, you can do what you're asking using ADO objects such as ADO.CreateCommand. Build a common function where you pass in the fields you want, the table name, and the parameter for the "WHERE" clause and run the SQL query that way.
I'm currently in the middle of re-factoring my SQLUtilities Script Extension to be a bit more object oriented but, built into it, is a query for precisely that in the current version. Feel free to download and extract the code if you want to see how it's done. The link I posted above also gives an example of writing such code.
tristaanogrefirst of all, thanks a lot for your feedback. I appreciate it.
I have to say I am a "QA- type of person", I approach my work based on how to assure quality, rather then focusing on development of scripts. It's a different approach.
Nevertheless, I will take a look athe ADO.CreateCommand info you provided and I will give it a try - If you don't mind I keep you posted on the progress.
Having said that, I would like like to see TestComplete improved by implementation of way to perform this kind of validation (this meaning easy Database validations) without the need of coding/scripting etc. (except the SQL statements if you like to see this as coding/scripting).
Next to that, I don't see the real benefit of the DBTable checkpoint, if, as in it's current implementation, there is no (dynamic) support for Where clausules. To be honest I consider that a shortcoming of DBTable checkpoint. As far as I can see now I can only implement SQL statements without dynamic Where clausules, right? I mean it's pretty basic feature that you can create a dynamic relationship the "where part" of the SQL statement to the data entered in the UI.
Thanks.
mgroen2 wrote:
tristaanogrefirst of all, thanks a lot for your feedback. I appreciate it.
I have to say I am a "QA- type of person", I approach my work based on how to assure quality, rather then focusing on development of scripts. It's a different approach.
Well, I have a pretty deep background in manual testing and software QA as well so I do approach my work with how to assure quality of the product under test. This means that I use tools like TestComplete to build good, well formed, robust test automation to supplement the manual processes in the organization. Tools like TestComplete will always have "holes" in their feature set that could be useful if filled. One of the things I appreciate about the TestComplete tool is that it provides a variety of ways of filling those "holes" in order to achieve the desired results without having to wait for the tool to be updated.
One such way is to build script code to supplement the Keyword tests in order to perform specific desired functions, like SQL record checks with a where clause. One step further, TestComplete's open architecture allows the creation of coded plugins and script extensions where you can create your OWN checkpoints (e.g., the tutorial for creating a Script Extension does exactly that) to perform the desired task.
The SQLUtilities extension I wrote that uses the ADO.CreateCommand method doesn't resolve necessarily as a checkpoint but it can be used with the "Call object method" keyword operation to execute an SQL query. So, while it's not a "built in" feature of TestComplete, the end result is the same, ensuring quality of the application under test, by providing a customized functionality.
I do like the idea of adding to my extension to do the kind of check you are looking for so I've put that on my personal backlog.
mgroen2 wrote:
To be honest I consider that a shortcoming of DBTable checkpoint. As far as I can see now I can only implement SQL statements without dynamic Where clausules, right? I mean it's pretty basic feature that you can create a dynamic relationship the "where part" of the SQL statement to the data entered in the UI.
I have voted up your feature request on this line as I do agree that it would be nice to have access to the SQL query of the DBTable checkpoint. It would make creating dynamic scripts easier "out of the box". However, because I have a mindset of ensuring quality of my application under test, I cannot wait for it to be included "out of the box" so I've written my own methods to achieve the same result.
tristaanogre Thanks again for your reply.
In short I totally agree with you, in the way that waiting for TestComplete to support this testing "out of the box", is not really desired in current situation.
Therefore, I am working on implementing ADO querie. However, I run in an issue with that:
For the ADO querie to be succesfull, I need to set the Connection String: ("Qry.ConnectionString = ").
But, I get stuck already at where to find the connection string for the environment I need to test.
I am using MS SQL Server Management Studio to connect to the database, but nowhere the Connection String is displayed (viewed properties, etc).
Sub TestProc
' Create a query
Set Qry = ADO.CreateADOQuery
' Specify the connection string
Qry.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
"Data Source=C:\Microsoft Visual Studio\VB98\biblio.mdb"
' Specify the SQL expression
Qry.SQL = "Select * FROM Authors WHERE Authors.[Year Born] >= :Param_Year"
' Specify the parameter value
Qry.Parameters.ParamByName("Param_Year").Value = 1950
' Execute the query
Qry.Open
' Process results and insert data into the test log
Qry.First
While Not Qry.EOF
Log.Message Qry.FieldByName("Author").Value, Qry.FieldByName("Year Born").Value
Qry.Next
Wend
' Closes the query
Qry.Close
End SubAlso, I would like to emphasize the potential for Feature Requests. While feature requests (like this one) have been created, SmartBear can work on implementing them, in the mean time TestComplete users can use workarounds (coding, scripting, write extensions whatsoever) to deal with current validation challenges until TestComplete provides robust, enhanced, and high quality support for (this kind of) situations.
