Connecting to DB through QTP

Methods Used: ADO Connection object

What is ADO connection?

Microsoft’s ActiveX Data Objects (ADO) is a set of Component Object Model (COM) objects for accessing data sources. It provides a layer between programming languages and OLE DB (a means of accessing data stores, whether they be databases or otherwise, in a uniform manner). ADO allows a developer to write programs that access data without knowing how the database is implemented. You must be aware of your database for connection only. No knowledge of SQL is required to access a database when using ADO, although one can use ADO to execute arbitrary SQL commands. The disadvantage of this (i.e. using SQL directly) is that it introduces a dependency upon the type of database used.

To learn more about ADO visit http://www.w3schools.com/ADO/ado_ref_connection.asp

Let us look how to connect to database(DB) in QTP, the steps involved are

  1. Create an object of type ADODB.Connection.
  2. Using the connection string establish a connection to the DB.
  3. Execute the query and store the query result to Recordset collection.

Let us see code snippet how to connect to the flights MS Access DB

STEP 1:

Set connect = CreateObject (“ADODB.Connection”)

STEP 2:

This can be done by many ways, if there is a system DSN created then you can use that directly to establish a connection.

connect.open “QT_Flight32” ‘here this is the QT_Flight32 is the system DSN name.

if there is no system DSN, then you can give the DB details directly as

Connect.open “Driver={Microsoft ODBC for Oracle}:Server=DBserver,UID=Sa,Password=sa”

Here you have to give the Driver type like oracle,sybase then server name, user id and password.

STEP 3 :

Set RS = connect.execute(“select flight_number from flights where flight_number = 2004” )

Now the RS object has the query result ,using loop we can get those data.

Count1 = RS.fields.count

Do

For  i = 0 To count1-1

MsgBox(RS.fields(i).name)

MsgBox(RS.fields(i).value)

Next

RS.MoveNext

Loop until RS.EOF

9 thoughts on “Connecting to DB through QTP

  1. Hi Poornima,
    I am not sure what you are asking,if you want to save the file do a File–>save option.Can you please elobrate if you mean some thing else?

  2. Hi Vikas,

    Please find the below code it might help

    sConstr=”Provider=sqloledb;Data Source={Give the IP of DB);Initial Catalog=Database Name;User ID=UserID;Password=password;”
    Set Db_Connection = CreateObject(“ADODB.Connection”)
    Db_Connection.open sConstr
    msgbox Db_Connection.State

    Regards,
    Deepak.Bhandarkar

  3. how to execute large queries and sub queries..?A single line query works fyn but if it contains sub queries it fails…?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s