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
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
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 :)
Kindly share the code for two Db table compare
ReplyDeleteHow do you execute a stored procedure in QTP?
ReplyDeletehow can we read excel file using vb script without using datatable
ReplyDeleteit's good explanation
ReplyDeletehi
ReplyDeleteGood example. Could you please tell what would be in the line in your script when:
ReplyDeleteCon =
??
What should we replace with?
Thanks,
Roshan.
Hi,can you pls let me know how to find SID,host and port
ReplyDeleteThere 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
ReplyDeleteDatabase 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