Hey folks,
I would like to give example of how database connection works in QTP. Following example takes DNS name as parameter and open ADODB connection with database. It fetch value from tables and compare it with UI elements. Hope this will helpful to you.
Following are the steps for the same with below example.
1. Either through parameter/varaible give DSN name.
ConnectionString="filedsn="&systemdnsnm
2. Create adodb.connection object
3. create execonnection object
Execonn.Connectionstring=Cstr(Connectionstring)
.connectiontimeout=10
.open
4. create recordset adobd.recordset
5. check connection state
if execonn.state=1 then
6. write query
sqlquery=select * from table
7. execute query by set dbresultset=execonn.execute(sqlquery)
8. read data from UI and store in variable or take data in variable.
if webtable used then use getcelldata(1,2) like method
9. Read dbresult till EOF n store value in DBtemp variable.
using DBResult.Fields.Item(query col name)
10. compare variable and dbvar and verify result.
Example
'parameter name for database connection
Parameter("DNS_Name")="DNSFilename.dsn"
'code for connecting database
Systemdatasourcenm=Parameter("DNS_Name") 'give DSN name
ConnStr="filedsn="&Systemdatasourcenm
Set ExtConn=CreateObject("ADODB.Connection") 'create connection object for database
With ExtConn
.ConnectionString=ConnStr
.ConnectionTimeout=10
.OPEN
End with
'create recordset object
Set DBResult=CreateObject("ADODB.Recordset")
'check for the connection state
If ExtConn.State=1 Then
reporter.ReportEvent micPass,"DBConn","DB connect successfully"
Else
reporter.ReportEvent micFail,"DBConn","DB Connection fail"
End If
'write query for fetching record and store the query in variable
SQLQuery="SELECT col1, col2, col3, convert(varchar(10),col4,101) As Submitted FROM table1 WHERE col1='04162BM00001'"
'set result set value by executing query with using connection object
Set DBResult=ExtConn.Execute(SQLQuery)
'go to first record in database
DBResult.MoveFirst
'do looping till the end of file to get results
While Not DBResult.EOF
DB_ClaimID=Trim(DBResult.Fields.Item("col1")) ' col1
msgbox DB_col1
DB_ClaimStatus=DBResult.Fields.Item("col3") ' col3
msgbox DB_col2
DB_Submitted=DBResult.Fields.Item("Submitted") ' col4
msgbox DB_col3
DB_ClaimType=Trim(DBResult.Fields.Item("col2")) ' col2
msgbox DB_col4
DBResult.MoveNext
Wend
Dim date1,id,STS,cltype
date1="06/10/2004"
id="04162BM00001"
STS="04"
cltype="F"
'compare two values of records
If ucase(id)= ucase(DB_ClaimID) Then
reporter.ReportEvent micPass,"id ","id same"
else
reporter.ReportEvent micFail,"id ","id not same"
End If
If STS= DB_ClaimStatus Then
reporter.ReportEvent micPass,"STS "," STS same"
else
reporter.ReportEvent micFail,"STS","STS not same"
End If
If cltype= DB_ClaimType Then
reporter.ReportEvent micPass,"cltype ","cltype same"
else
reporter.ReportEvent micFail,"cltype","cltype not same"
End If
If date1= DB_Submitted Then
reporter.ReportEvent micPass," date1"," date1 same"
else
reporter.ReportEvent micFail,"date1",date1 not same"
End If
'close database connection
ExtConn.Close
Set DbResult2 = Nothing
Set ExtConn = Nothing
