Database Connection Using QTP

We can connect to Database using QTP in Two Ways.

·         DSN approach

·         DSN less approach

DSN approach:

What is DSN?

DSN stands for Data Source Name.DSN allows you to connect to a database using ODBC driver. ODBC stands for Open Database connectivity which provides a standard interface that allows one application to access many different data sources.

DSN creation for Oracle database:

Step 1:

Go to your Control Panel\Administrative Tools\Data Sources (ODBC)

Step 2:

Go to User DSN and click on Add. The Create New Data Source dialog opens.

Step 3:

Select a driver for which you want to set up a data source and click finish button. The Oracle ODBC Driver Configuration window opens.

Step 4:

Specify the following information in the Oracle ODBC Driver Configuration window:

·         In the Data Source Name field, enter a name of your choice to identify the data source. If your DBA has supplied a common DSN for use across the enterprise, enter it here.

·         In the Description field, enter an optional description for the data source.

·         In the TNS Service Name drop-down, select the TNS Service Name for the database your workspace repositories will be stored in. If no choices are shown, or if you are unsure which name to select, contact your DBA.

·         In the User ID field, enter the database user ID supplied by your DBA

Step 5:

Click Test Connection. The Oracle ODBC Driver Connect window opens.

Step 6:

·         In the Oracle ODBC Driver Connect window the Service Name and User ID fields are prefilled with the information you supplied in the Oracle ODBC Driver Configuration window. Enter the password for your user ID and click OK. You are notified that the connection was created successfully. Click OK.

Note: If you receive an error, verify the driver configuration settings and retest the connection. See your DBA if the problem persists.

·         Click OK to exit the Driver Configuration window. Then click OK again to exit the ODBC Data Source Administrator window.

We are done with DSN creation with this step.

What is ADO?

ADO stands for ActiveX Data Object and allows you to access a database from inside QTP using VBScript.

Now use the following VB script code to connect to database using DSN,

Dim dbCon, dbRecordset, ConnectionString
Set dbCon = Createobject("ADODB.Connection")
Set dbRecordset = CreateObject("ADODB.Recordset")

'Please replace 'Name fo the DSN' with actual name of DSN which you craeted,
'ServerName with server ip address or server name and password with the value of the password to connect to database

ConnectionString = "DSN=Name fo the DSN;Server=ServerName;Pwd=Password"

'Open Connection
dbObj.Open ConnectionString

DSN less approach:

What is DSN-less approach?
Here you have to specify all the connection related info directly in the QuickTest pro.

Note:
We have to make sure that the machine has the correct drivers installed.

VB Script Code to Connect to different databases:

MS Access:

Dim dbCon, dbRecordset, ConnectionString 

Set dbCon = Createobject("ADODB.Connection")
Set dbRecordset = CreateObject("ADODB.Recordset")

ConnectionString ="Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;"

con.open ConnectionString