Database Checkpoint and QTP Part2 – Using Scripts

Updated On Jul 7, 2014 by

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.

Welcome new readers: Download the Free eBook!

Ankur JainI am Ankur Jain, the owner of LearnQTP and founder of a software organization. This site is run, maintained and updated by me and my team. Feel free to take a look around, you may want to start with What is QTP? 

You can connect with me on my Google+ page. 

Please enter your first name and email address below to instantly download the eBook on Optimizing QTP Scripts. In future, we will make sure you get new tips & tricks on QTP delivered direct to your email box.



Share This:

20 Comments

  1. Banesh

    Awesome saket,real time and very useful one.thanks for Ankur too.

  2. ksk

    can you please provide connection string for informix?

  3. MJ

    Best Solution for database testing..Thank you so much for sharing!!It resolved my complex issue!

  4. vishal

    Hello, this is vishal just now i am downloading qtp 11. and please provide me document material where i can understand qtp from starting (0).

    Thank you,
    9662206758

  5. Emad

    Having trouble connecting to DB. Would you be able to assist me please?

  6. i have come across using several more fields while enabling database connection such as adopenstatic..
    But frankly I myself donto know the relevance on the same. Wish you to oultine some insights from your usdage of the same on this ?

  7. ravi

    Hii
    Thanks for the explation given

    I need to know creation of database checkpoint where datasource is located in remotehost

  8. Arun

    Hi Ankur,

    Your explonation all are Excellent .

    Here I have one clarification while iam developing the MYSQL database connection script.

    Dim dbexample
    Set dbexample =CreateObject(“ADODB.Connection”)
    dbexample.ConnectionString=”driver=mysql;server=localhost;uid=root;pwd=root;database=dbempinfo”
    dbexample.open
    Set recordset=dbexample.Execute(“Select * from tblemp”)
    msgbox recordset.getstring.

    but I got the error such as

    The test run cannot continue due to an unrecoverable error.

    [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

    so can you point our where i did the mistake from here and how can i correct it here..

    my email id:sarulvenki@rediffmail.com

    Thanks and Regards,
    S.Arun

  9. Saket, I used this getConnection() function and Example 1 to use this function.

    When I checked whether object was created using IsObject(MyConnection), it returned FALSE. The reason I did this was when I was trying to close the connection, I got “Object Required” error message.

  10. mahesh

    Hi,
    Thanks a lot for nice explanation ,

    I am a Java Developer and currently working in automation where I need to automate the interactions between 3 systems (some UI is also there ) for that I need to pass an xml using QTP into a JMS queue created at server.
    Is there any way by which I can place an xml in JMS(JAVA based ) queue through QTp (VB code).

    Your help will be geratly apriciated.

    waiting for your response..

    thanks
    Mahesh Chaudhari

  11. Haris

    Sorry Saket, this was a typo. I am fine with the connection state.

  12. Haris

    Sorry here is the updated function:

    Function getConnection ()

    On Error Resume Next
    Dim conn, connectString
    connectionString = “Driver={Microsoft ODBC for Oracle};ConnectString= dev11i ;UID={uid};PWD={pswd};”

    ‘ Create the connection
    Set conn = CreateObject(“ADODB.Connection”)

    ‘ Open the connection
    conn.Open connectString
    Set getConnection = conn
    MsgBox conn.State ‘ returns 0!
    End Function

  13. Haris

    Thanks Saket, it showing object as True now. But the object state is 0 (closed) instead of open. I have to use this statement again in the script after calling the function:

    Dim conn
    Set conn = getConnection()
    MsgBox conn.State ‘ Is 0

    conn.Open ConnectionString ‘ I have to do this again
    MsgBox conn.State ‘ now it’s 1

    Inside the function its 0 as well:

    Function getConnection ()

    On Error Resume Next

    Dim conn, connectString

    connectionString = “Driver={Microsoft ODBC for Oracle};ConnectString= dev11i ;UID={uid};PWD={pswd};”

    ‘ Create the connection
    Set conn = CreateObject(“ADODB.Connection”)

    ‘ Open the connection
    conn.Open connectString
    Set connectDb = conn
    MsgBox conn.State ‘ returns 0!
    End Function

    Maybe I am doing something wrong.

  14. Saket

    Haris, when I am trying, it it is working fine.

    Dim MyConnString, MyConnection
    Set MyConnection = getConnection
    msgbox IsObject(MyConnection)

    returns true.
    Can you check if your getconnection() function is working properly, check the connectionstring.
    I will suggest you to remove the error handling statement from the function and see if it throws any error there.

  15. suresh

    Very good explanation about database connection.

  16. Haris

    Saket, I used this getConnection() function and Example 1 to use this function.

    When I checked whether object was created using IsObject(MyConnection), it returned FALSE. The reason I did this was when I was trying to close the connection, I got “Object Required” error message.

  17. Kiss

    Explanation provided regarding the DB connectivity of QTP was splendid.Its very useful learining!! Thanks!

  18. Ram

    Nice article Saket, The explanation is simple and clear.

  19. Saket

    Thanks Vijayendra,
    feel free to ask any related query at QTP Forums.

  20. Vijayendra

    Very beautiful explanation of DB connection through QTP. Till now I could not understand how to connect to DB and had mugged up the command lines read from various sources. Now I have got the proper understanding of the concept. Please continue with such useful articles.

Trackbacks/Pingbacks

  1. Database Checkpoint and QTP Part 3 – Retrieving Data | Learn QTP - [...] Part1 we saw the wizard method to use Database checkpoint.  In Part2 we learned how to connect to a …

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>