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
        Err.clear
        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
        err.clear
        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

    myrs.MoveFirst
    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
    Else
        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"
con_string="DSN=SQLServer_Source;UID=SA;PWD=xyz123"

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

Con.close
Set Con = Nothing 'Disconnect database

'Below is the example connection string for Oracle database
' strDBDesc ="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dev.uat.application.opp.devenv.domain.net)(PORT=1521)))(CONNECT_DATA=(SID=DBS)))"

 '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 :)