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
ReplyDeleteIt 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.
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
ReplyDeleteThank you for posting informative insights, I think we have got some more information to share with! Do check out
oracle training in chennai and let us know your thoughts. Let’s have great learning
Sharing the same interest, Infycle feels so happy to share our detailed information about all these courses with you all! Python Training In Chennai & get to know everything you want to about software trainings.
ReplyDelete