Visit: [Link].
com for QTP Information
[Link]
Database Operations in QTP
ADO (Active Data Objects), this technology allows users to access data easily from
many existing databases (such as Access or Paradox) or from ODBC compliant
databases like Oracle or MS SQL Server. Using ADO is quite simple and allows
programmers to provide flexible database front ends to users that are reliable and
include many features.
Following are some of the key objects found in the ADO object model and some of
their key methods and properties.
Connection Object
This object represents an open connection to the data source. This connection can be
a local connection (say [Link]) or can be across a network in a client server
application. Some of the methods and properties of this object are not available
depending on the type of data source connected to.
Command Object
A command object specifies a specific method we intend to execute on or against the
data source accessed by an open connection.
RecordSet Object
The RecordSet object represents a complete set of records from an executed
command or from an underlying base table in the database. A key thing to note is
that a RecordSet object references only one record at a time as the current record.
Database Scripts Examples:
1) Get Test Data from a Database and use in Data Driven Testing (through
Scripting)
1) Dim con,rs
2) Set con=createobject(“[Link]”)
1
Visit: [Link] for QTP Information
3) Set rs=createobject(“[Link]”)
4) [Link]=(“[Link].4.0″)
5) [Link] “C:Documents and SettingsAdministratorMy [Link]”
6) [Link] “Select * From Login”,con
7) While [Link] <>True
8) [Link] “C:Program FilesMercury InteractiveQuickTest
[Link]”
9) Dialog(“Login”).Activate
10) Dialog(“Login”).WinEdit(“Agent Name:”).Set [Link] (“Agent”)
11) Dialog(“Login”).WinEdit(“Password:”).Set [Link] (“Password”)
12) Dialog(“Login”).WinButton(“OK”).Click
13) Window(“Flight Reservation”).Close
14) [Link]
15) Wend
2) Exporting Data from a Database to an Excel Sheet
1) Dim con,rs
2) Set con=createobject(“[Link]”)
3) Set rs=createobject(“[Link]”)
4) [Link]=”[Link].4.0″
5) [Link]”C:Documents and SettingsadminMy [Link]”
6) [Link]”select*from Login”,con
7) Set ex=createobject(“[Link]”)
Set a=[Link](“C:Documents and SettingsadminMy
[Link]”)
9) Set b=[Link](“sheet1″)
10) i=1
2
Visit: [Link] for QTP Information
11) Do While Not [Link]
12) [Link] (i,1).value=[Link](“agent”)
13) [Link](i,2).value=[Link](“password”)
14) [Link]
15) i=i+1
16) Loop
17) [Link]
18) [Link]
3) Exporting Data from a Database to a Text file
Dim objCon,objRs,ObjFso,myFile,myData,rc,r
Set objCon=createobject(“[Link]”)
Set objRs=createobject(“[Link]”)
set objFso=createobject(“[Link]”)
Set myFile=[Link](“C:Documents and SettingsgcrMy
[Link]”,8)
[Link]=(“[Link].4.0″)
[Link]”C:Documents and SettingsgcrMy [Link]”
[Link] “select * from login”,objCon
r=1
Do until [Link]
a=[Link] (“Agent”)
b=[Link] (“Pwd”)
[Link] a &”,”& b
r=r+1
[Link]
Loop
[Link]
[Link]
4) Connecting to a SQL Sever database
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject(“[Link]”)
Set objRecordSet = CreateObject(“[Link]”)
[Link] _
3
Visit: [Link] for QTP Information
“Provider=SQLOLEDB;Data Source=atl-sql-01;” & _
“Trusted_Connection=Yes;Initial Catalog=Northwind;” & _
“User ID=fabrikamkenmyer;Password=34DE6t4G!;”
[Link] “SELECT * FROM Customers”, _
objConnection, adOpenStatic, adLockOptimistic
[Link]
[Link] [Link]
5) Open a Database Using a DSN
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject(“[Link]”)
Set objRecordSet = CreateObject(“[Link]”)
[Link] _
“Northwind;fabrikamkenmyer;34ghfn&!j”
[Link] “SELECT * FROM Customers”, _
objConnection, adOpenStatic, adLockOptimistic
[Link]
[Link] [Link]
6) Open Two Record sets
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject(“[Link]”)
Set objRecordSet = CreateObject(“[Link]”)
Set objRecordSet2 = CreateObject(“[Link]”)
[Link] _
4
Visit: [Link] for QTP Information
“Provider= [Link].4.0; ” & _
“Data Source=[Link]”
[Link] “SELECT * FROM GeneralProperties Where ComputerName =
‘Computer1′”, _
objConnection, adOpenStatic, adLockOptimistic
[Link]
[Link] “SELECT * FROM Storage Where ComputerName =
‘Computer1′”, _
objConnection, adOpenStatic, adLockOptimistic
[Link]
Do Until [Link]
[Link] [Link](“ComputerName”)
[Link] [Link](“OSName”)
[Link]
Loop
Do Until [Link]
[Link] [Link](“DriveName”), _
[Link](“DriveDescription”)
[Link]
Loop
[Link]
[Link]
[Link]
7) Searching a Database Using String Criteria
Const adOpenStatic = 3
Const adLockOptimistic = 3
5
Visit: [Link] for QTP Information
Set objConnection = CreateObject(“[Link]”)
Set objRecordSet = CreateObject(“[Link]”)
[Link] _
“Provider = [Link].4.0; ” & _
“Data Source = [Link]”
[Link] “SELECT * FROM EventTable ” & _
“WHERE Type = ‘Error’”, objConnection, adOpenStatic, _
adLockOptimistic
[Link]
[Link] “Number of records: ” & [Link]
[Link]
[Link]
8) Insert Data into a database table using Database Command Object
Dim objCon,objCom
Set objCon=Createobject(“[Link]”)
[Link]”Provider=[Link].4.0;Data Source=C:[Link];”
Set objCom=Createobject(“[Link]”)
[Link]=objCon
[Link]=”insert into Emp values(‘G C Reddy’,88233,30000)”
[Link]
[Link]
Set objCom=Nothing
Set objCon=Nothing
9) Insert multiple sets of Data (using Excel sheet) into a database table
using Database Command Object
6
Visit: [Link] for QTP Information
Dim objCon,objCom,strEmpName,intEmpNo,intEmpSal,intRowcount,i
Set objCon=Createobject(“[Link]”)
[Link]”Provider=[Link].4.0;Data Source=C:[Link];”
Set objCom=Createobject(“[Link]”)
[Link]=objCon
[Link](“input”)
[Link] “C:[Link]”,1,”input”
intRowcount=[Link](“input”).GetRowCount
Msgbox intRowcount
For i=1 to intRowcount step 1
[Link](i)
strEmpName= [Link](1,”input”)
intEmpNo= [Link](2,”input”)
intEmpSal= [Link](3,”input”)
[Link]=”insert into Emp values( ‘”&strEmpName&”
‘,”&intEmpNo&”,”&intEmpSal&”)”
[Link]
Next
[Link]
Set objCom=Nothing
Set objCon=Nothing
[Link]
