A series of articles on Database with QTP by your ever helpful moderator Saket.
Today almost all the software applications use relational database management systems (RDBMS) to provide persistency to the program. An RDBMS used in application could
be the Oracle, SQL, Access, MySQL etc which depends on the requirement of the software program. Testing the functionality of a database is one of the most challenging tasks for software tester.
In this part of series we will discuss how we can use database checkpoint in QTP and in the later parts we will understand the key concepts and how to connect to a database using QTP scripting and how we can retrieve data from the database and other important facts. As a good tester you must have basic knowledge of writing query and verify its correctness based on the parameters. For this you will need to first understand the database of the application under test, which includes understanding the referential integrity, security, and data formats.
The most important Referential Integrity is basically the schema of the database- a visual diagram of all the tables in your database, which you can get from your development team. This is very important as it provides you all the basic information about your database like how the integrity has been maintained, table information, risks etc. Once you have knowledge on all the intricacies of the database you can begin with your testing.
While automating, the very first thing you may face is to connect to the database. Let us first understand the database Checkpoint. Database checkpoint is one of the checkpoints in QTP which is used to test the contents of the database accessed by application under test. It stores the expected data and compares this with the data stored in the database. When you use a database checkpoint in your script, it connects to the database and sends the query to the database to retrieve the current data into the record set. QTP now compares the current data with the expected data stored in the checkpoint and gives you the result as pass or fail. You can create a database checkpoint to confirm that the data being stored in the database does not introduce any error and you can do this by verifying
- The data is saved to the correct tables and in proper field
- The data on different operation in database like insert, delete, update
- The correctness of data etc.
To understand this lets take a very simple example for Flight Application. Suppose you are updating an order by changing the name, as in the snapshot below. You need to change the customer name to Learnqtp now and update the record. So if you want to verify whether the record is properly updated with the changed name or not, you use the database checkpoint. 
To create a database checkpoint in QTP following steps should be followed – Insert statements to update the record in your Script.
- Go to Insert > Checkpoint > Database Checkpoint. You will see a database query wizard.
- Select either of the two option there
- Create query using Microsoft query – Select this if you want to use Microsoft query.
- Specify SQL statement manually – Select this to manually provide the sql query to the database. We will use and go with this option now. Click ‘Next’ once you selected the query definition.
- Click ‘Create’ button, which will open the data source window, Select ‘Machine Data Source’ and click new. ( To Connect to flight application database there is already a data source as QT_Flight32, we can directly select this press ‘OK’ and jump to step 7)
- Create New Data Source window opens, Select the type of data source and click Next
- Select the Driver depending on the database type from the list. For example if your database is SQL – select ‘SQL Server’, for Oracle – select ‘Microsoft ODBC for Oracle’ and follow the onscreen wizard with the details of your database like server name database name etc. Finally Test the connection and press ‘OK’
- You will see the data source name just created in the list at Machine Data source. Select and Click ‘OK’
- Specify your sql query e.g. for above mentioned example – ‘Select Customer_Name from Orders’. Click Finish
- It will Open the Database Checkpoint Properties, modify your checkpoint settings, enter the expected data and Click ‘OK’
It will add a line in the expert view as:
DbTable("DbTable").Check CheckPoint("DbTable")
When you will run the script QTP will check the database whether the record is updated with the customer name or not and will give you the result as pass or fail. DbTable is the database table object, which has following properties and methods associated with it.
- Exist Property –checks whether the database table exists
- GetToProperty method – is the same method used for test objects to get the specified identification property.
- SetToProperty method – is to set the specified identification property.
There could be three identification properties of a database table object – Source, dbuniqueid and connectionstring. So we can parameterize the database checkpoint using these properties and methods, for example if you want to use the same checkpoint to run for more query use the ‘SetToProperty’ method to set the source of the DBTable. Following example illustrates above methods
If DbTable("DbTable").Exist (0) Then
Print " Current query : " & DbTable("DbTable").GetTOProperty ("Source")
DbTable("DbTable").SetTOProperty "Source", "Select * from Orders"
Print " Modified query : " & DbTable("DbTable").GetTOProperty ("Source")
End If
Result
Have you downloaded the FREE Optimizing QTP eBook yet? Get It Now!
If you want to keep track of further articles on QTP. I recommend you to subscribe via RSS feed. You can also subscribe by Email and have new QTP articles sent directly to your inbox.
Related posts:
20 comments ↓
Hi Ankur,
How are you?
I have one query. I would be grateful to you if you will help me in solving my query:-
I am using licensed version of QTP 10.0. My application is based on flex and QTP is not able to recognise the flex objects. I did some r&d on it and found that there is flex add in available but I did not find the trial version.I would request you to please share your thoughts on automating flex objects as well so that I could proceed further with my target.
Many regards,
Ana
.
Hi Ankur,
this is good article….
i have one doubt, can we able to compare sql query result data with data in external .xls file
by using database check point.
if not, please provide solution how we can do it….
Thanks,
nice post , but the thing which is my time killer is the code snippet for connection to DB . i always copy paste it to make a succesfull connection but i was looking for some simple and well defined code to make a connection.
i always used this :
set con=Createobject(“ADODB.Connection”)
Srvname=”Driver={PostgreSQL ANSI};DATABASE=XYZ;SERVER=000.000.000.000;PORT=5432;UID=postgres;PWD=;CA=a;A6=;A7=100;A8=4096;B0=255;B1=8190;BI=0;C2=dd_;;CX=1b503bb;A1=7.4″
con.open Srvname
Set Rec = CreateObject(“ADODB.Recordset”)
SQL=”update tablename set xyz= ’900′ where id=250316″
Rec.open SQL ,con
all is fine with above code but i always get confused while writing the value for DATABASE ie :
Srvname=”Driver={PostgreSQL ANSI};DATABASE=XYZ;SERVER=000.000.000.000;PORT=5432;UID=postgres;PWD=;CA=a;A6=;A7=100;A8=4096;B0=255;B1=8190;BI=0;C2=dd_;;CX=1b503bb;A1=7.4″
Can you make life easy
Good coverage of ‘DB CheckPoints’. Nice article. Awaiting for the Part2.
Nice article Saket, will definately try to use it in our scripts.
Thanks
Hi Varun,
In the next part of this series you will get some good example and ways to connect to DB. all the required params will be explained in the next part.
[...] the earlier part of this series we have learnt how to use database checkpoint in database testing. If you don’t want to use the database checkpoint in your database testing, you will have to [...]
HI
Iam using QTP 10 and using DB checkpoints we have to run the scripts on multiple databases we are having trouble with DB checkpoints for Numaric and string values. Because QTP did not allow to put numaric and string checks in one single db checkpoint so we have to put 2 checkpoints on the same table one for string values and one for numaric values .
if the numaric value do not have decimal places its fine under the string checkpoinjt but the problem arises when there are decimal places .
here is the sample data
lets say we have a table books which have 2 columns
DB 1
Book Name , Price
ABC 100
ZAV 100.0
DB 2
Book Name , Price
ABC 100
ZAV 100.00
if we put a single checkpoint on DB 1 it will fail on the second db untill unless you put the price columns as numaric check .
but the trouble is you can not put the numaric and string checks togather in one db checkpoint.
in our case we have to put 2 db check points for the tables where we have both types of data in the table. Anybody know the solution so that we do not have to put the 2 checkpoints every time Thanks for you help .
Hi ,
I have IE8 and want to Run QTP 9.2 on it .But its not working .I know that if i uncheck the activeX controles it will run but there are some additional dialogs that are comming in IE 8 so Can any one tell what else setting i need to chnage in IE 8 so that it becomes Equvalent to IE 6.
Thanks in Advance.
Manpreet
Hi Manpreet,
QTP 9.2 does not support IE8, you will have to upgrade to QTP 10.
Hi Ankur,
we can install QTP 9.5 or 10.00 version on Windows 7 OS ?
Pl reply me urgent.
Hi Hitesh,
refer this Post
http://www.learnqtp.com/qtp10-supports-windows7-windows-server-2008-r2/
[...] Part1 we saw the wizard method to use Database checkpoint. In Part2 we learned how to connect to a database using scripts in QTP. In this part we will see [...]
Saket,
Can we reuse the connection information (connection string) between multiple database checkpoint Or every time we add database checkpoint we need to specify the data source?
Certainly it’s fact that all checkpoints in QTP are part of their beginner friendly marketing.
For database check, automators should use programmers approach… create data access function(s) using ADO and maintain connection string information in Environment file (similar to global.asax in .Net and property file in J2EE). All you need to do is call getRecordSet (or any name you give) function and provide the sql variable and database name as input and get the result set returned back. than check record-set against the value being displayed on the application. It will increase the readability and re-usability of your code.
Though checkpoints are useful they are not the solution in all scenarios.
happy programming!
Hi Ram,
Thanks for Pointing this, I have tried to explain the same in later parts of this series. Please continue reading Part 2 and 3.
hi while creating DB check point, it is asking for connection string.. can anyone suggest me wat is connection string.
Hi Sateesh,
This has been discussed in Part 2 of this series.
http://www.learnqtp.com/qtp-database-part2-how-to-create-connection-strings/
HI,
I need a query to get the enterprise name from the database …..
con.open “Server=192.168.10.35;Port=-1;Database=efare_qa_2_1;Uid=efare_qa_2_1;Pwd=yana123;”
when i use above con, QTP shows below error
“[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified”
Can any one help me to connect MYSQL to QTP using vb script.
HI,
There is any options for writing descriptive program using checkpoints manually with out recording….If there is any option tell with some examples…
Tnks in advance…
Leave a Comment