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:
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