QTP-Database connection example

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