In 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 script it to connect to database and test the records. This gives you more flexibility and options to play around the records. Let us see how we can connect to a database using QTP through scripting.
- ActiveX Data Objects (ADO) is COM objects which can be used to gain access to a database through QTP. While using ADO you will need to understand three objects
- Command– Command object is like a container for a command which you send to the database to do some actions. Mostly a sql statement is used as a command to the database.
- Connection – Connection object is the link between QTP and the database. For any operation you will need to open the connection link initially and make sure you have closed the link once you are done. It requires a connection string to initialize the properties needed to connect to a db .
- RecordSet – RecordSet object is the container for the command results. The container is called as the cursor. A cursor is like a temporary table in the memory which contains all the characteristics of a table like rows, columns, record pointers etc. This is the object on which plays significant role in your test.
To connect to the database, you will need to use CreateObject method to get the object
Set MyConnection = CreateObject(“ADODB.Connection”)
Next you will need to pass the connection string to the connection object. The connection string is created using certain keywords and values in it depending on the database you need to connect. The keyword Provider (identifies the OLE DB provider to be used.) is used in almost all type of connection and other keywords depends on the database you are connecting. [Note From Ankur: You can use the site ConnectionStrings to build strings corresponding to your database.]
A typical connecting string for database connection will be like –
Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\mydatabase.mdb;User Id=admin;Password=;
Access 2007 –
Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;
Provider=sqloledb;Data Source=myServerAddress; Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
Provider=msdaora;Data Source=MyOracleDB; User Id=myUsername;Password=myPassword;
Once you have the connection string you can now open the connection using Open method of Connection object
MyConnection.Open “Provider=sqloledb;Data Source=myServerAddress; Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;”
That is all and now QTP is linked with the database. Now we need to pass the required command and verify the data.
Below is a generalized function to connect to DB
Function getConnection () On Error Resume Next Dim oConnection,ConnectionString ConnectionString ="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Program Files\HP\QuickTest Professional\samples\flight\app\flight32.mdb; User Id=admin;Password=;" Set oConnection = CreateObject("ADODB.Connection") oConnection.Open ConnectionString Set getConnection = oConnection End Function
The function creates a new connection session to a database using the connection string passed. Connecting can be set as per the requirement.
Let us see how we can connect to the database of flight application. The database for sample application is an Access Database located at “C:\Program Files\HP\QuickTest Professional\samples\flight\app\flight32.mdb”. To connect to this database we will need to create a connection string for Access which is used in the above function.
Example 1 illustrates the usage of function mentioned above.
Dim MyConnString, MyConnection Set MyConnection = getConnection If MyConnection.Errors.Count = 0 then msgbox "Database Connected" Else msgbox Err.Description End If
If you are having difficulties using connection string, don’t worry you can create the connection using Data Source Name as well.
To create the DSN Click Start, Control Panel, Administrative Tools, and then double-click Data Sources(ODBC). On the user DSN tab, click Add. And follow rest of the wizard. Now in place of the connection string you need to have the DSN that you provide while creating.
Connect to flight application database using already created DSN
ConnectionString = "QT_Flight32” Set oConnection = CreateObject("ADODB.Connection") oConnection.Open ConnectionString
In the same you can now connect to any database using the connection. Note that for connecting to Oracle database you need have the TNS Setup located in your tnsnames.ora. the file can be found at “<Oracle Client Installation folder> \network\admin”. And use the TNSname as the Datasource in your connection string.
A typical tnsnames.ora content will be like
YourTNSName = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <YourServerNameOrIP>)(PORT = <YourPortOrDefault1521>)) ) (CONNECT_DATA = (SID = <YourDBSID>) (SERVER = DEDICATED) ) )
Use ‘Close’ method to close the connection.