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

10 comments:

  1. Kindly share the code for two Db table compare

    ReplyDelete
    Replies
    1. Machine Learning Projects for Final Year machine learning projects for final year

      Deep Learning Projects assist final year students with improving your applied Deep Learning skills rapidly while allowing you to investigate an intriguing point. Furthermore, you can include Deep Learning projects for final year into your portfolio, making it simpler to get a vocation, discover cool profession openings, and Deep Learning Projects for Final Year even arrange a more significant compensation.

      Python Training in Chennai Project Centers in Chennai

      Delete
  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. 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

    ReplyDelete