Tuesday, May 10

Database Connection using QTP

Hello Friends,

In this post, we'll see how to connect to database using QTP. We'll connect with database and retrieve values from the table/recordsets.

DataBase_Connection() creates a new connection to a database.

There are two arguments passed to this function -

1. sessionName - the name of the session (string)
2. connection_string - a connection string, for example the connection_string can be "DSN=SQLServer_Source;UID=SA;PWD=xyz123". Please note that the connection string will vary as per your database details.

Function DataBase_Connection(sessionName,connection_string)
    Dim oConnection
    on error Resume next
    ' Opening connection
    set oConnection = CreateObject("ADODB.Connection")
    If Err.Number <> 0 then
        DataBase_Connection= "Error :- " & CStr(Err.Number) & " " & Err.Description
        Exit Function
    End If
    oConnection.Open connection_string
oConnection.CommandTimeout = 120  'modify this value if needed. 
    If Err.Number <> 0 then
        DataBase_Connection= "Error := " & CStr(Err.Number) & " " & Err.Description
        Exit Function
    End If
    set sessionName = oConnection
    DataBase_Connection = 0
End Function

We need another function to retrieve data from record set.

Function db_get_field_value( myrs , rowNum, colNum )
    dim curRow

    count_fields = myrs.fields.count-1
    If ( TypeName(colNum)<> "String" ) and ( count_fields < colNum ) then
        db_get_field_value = -1 'requested field index more than exists in recordset
        myrs.Move rowNum
        db_get_field_value = myrs.fields(colNum).Value
    End If
End Function

Now, let's do the actual thing :)

Con = <name of the session>
SQL="SELECT * FROM Your_Table"

isConnected = DataBase_Connection (Con , con_string)

'Now check if connection is successful. Function will return zero if connection is successful.
If isConnected = 0 then
    'Execute your SQL statement
    set myrs = Con.Execute(SQL)

    'Retrieve values from the recordset
    print "val - row 0 col 0: " & db_get_field_value( myrs , 0 , 0 )
    print "val - row 0 col 1: " & db_get_field_value( myrs , 0 , 1 )
End If

Set Con = Nothing 'Disconnect database

'Below is the example connection string for Oracle database

 'strUserID = "user11"
 'strPassword = "pass11"
 'Conn_String="Provider=OraOLEDB.Oracle;Data Source=" & strDBDesc & ";User ID=" & strUserID & ";Password=" & strPassword & ";"
In case of any querirs, please leave your comments. Happy Automation :)


  1. Kindly share the code for two Db table compare

  2. How do you execute a stored procedure in QTP?

  3. how can we read excel file using vb script without using datatable

  4. it's good explanation

  5. Good example. Could you please tell what would be in the line in your script when:

    Con =
    What should we replace with?


  6. Hi,can you pls let me know how to find SID,host and port

  7. There are many different types of databases available such as Microsoft Access Databases, Oracle Databases, MySQL databases and so on but Microsoft Excel also has a form of a database known as a database list.create mysql dashboard

  8. Database security is done periodically by the administrator of the company's website for closely monitoring the activities of the users who have access to the database. Database Diagram Tool