Pages

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

9 comments:

  1. Kindly share the code for two Db table compare

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

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

    ReplyDelete
  4. it's good explanation

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

    Con =
    ??
    What should we replace with?

    Thanks,
    Roshan.

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

    ReplyDelete
  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

    ReplyDelete
  8. It has been simply incredibly generous with you to provide openly what exactly many individuals would’ve marketed for an eBook to end up making some cash for their end, primarily given that you could have tried it in the event you wanted oracle training in bangalore.

    ReplyDelete