Database Checkpoint and QTP Part2 – Using Scripts

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 –

Access

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;

SQL Server

Provider=sqloledb;Data Source=myServerAddress;
Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Oracle

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 1

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.

Example 1

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

Example 2

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.

MyConnection.Close

An article by Saket. You can follow the complete series here.