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.
Can we retrieve timestamp data from Database using QTP?
Awesome saket,real time and very useful one.thanks for Ankur too.
can you please provide connection string for informix?
Best Solution for database testing..Thank you so much for sharing!!It resolved my complex issue!
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
Having trouble connecting to DB. Would you be able to assist me please?
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 ?
Hii
Thanks for the explation given
I need to know creation of database checkpoint where datasource is located in remotehost
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
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.
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
Sorry Saket, this was a typo. I am fine with the connection state.
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
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.
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.
Very good explanation about database connection.
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.
Explanation provided regarding the DB connectivity of QTP was splendid.Its very useful learining!! Thanks!
Nice article Saket, The explanation is simple and clear.
Thanks Vijayendra,
feel free to ask any related query at QTP Forums.
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.