Visit: www.gcreddy.
com for QTP Information
www.gcreddy.com
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 App.Path) 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(“Adodb.connection”)
1
Visit: www.gcreddy.com for QTP Information
3) Set rs=createobject(“Adodb.recordset”)
4) con.provider=(“microsoft.jet.oledb.4.0″)
5) con.open “C:Documents and SettingsAdministratorMy DocumentsGcreddy.mdb”
6) rs.open “Select * From Login”,con
7) While rs.eof <>True
8) SystemUtil.Run “C:Program FilesMercury InteractiveQuickTest
Professionalsamplesflightappflight4a.exe”
9) Dialog(“Login”).Activate
10) Dialog(“Login”).WinEdit(“Agent Name:”).Set rs.fields (“Agent”)
11) Dialog(“Login”).WinEdit(“Password:”).Set rs.fields (“Password”)
12) Dialog(“Login”).WinButton(“OK”).Click
13) Window(“Flight Reservation”).Close
14) rs.movenext
15) Wend
2) Exporting Data from a Database to an Excel Sheet
1) Dim con,rs
2) Set con=createobject(“adodb.connection”)
3) Set rs=createobject(“adodb.recordset”)
4) con.provider=”microsoft.jet.oledb.4.0″
5) con.open”C:Documents and SettingsadminMy DocumentsGcreddy.mdb”
6) rs.open”select*from Login”,con
7) Set ex=createobject(“Excel.Application”)
Set a=ex.workbooks.open(“C:Documents and SettingsadminMy
DocumentsGcreddy.xls”)
9) Set b=a.worksheets(“sheet1″)
10) i=1
2
Visit: www.gcreddy.com for QTP Information
11) Do While Not rs.EOF
12) b.cells (i,1).value=rs.fields(“agent”)
13) b.cells(i,2).value=rs.fields(“password”)
14) rs.movenext
15) i=i+1
16) Loop
17) a.save
18) a.close
3) Exporting Data from a Database to a Text file
Dim objCon,objRs,ObjFso,myFile,myData,rc,r
Set objCon=createobject(“Adodb.connection”)
Set objRs=createobject(“Adodb.Recordset”)
set objFso=createobject(“Scripting.Filesystemobject”)
Set myFile=objFso.OpenTextFile(“C:Documents and SettingsgcrMy
Documentsgcreddy.txt”,8)
objcon.provider=(“Microsoft.jet.oledb.4.0″)
objcon.open”C:Documents and SettingsgcrMy Documentsgcreddy.mdb”
objrs.open “select * from login”,objCon
r=1
Do until objRs.EOF
a=objRs.Fields (“Agent”)
b=objRs.Fields (“Pwd”)
myFile.Writeline a &”,”& b
r=r+1
objRs.MoveNext
Loop
myFile.Close
objCon.Close
4) Connecting to a SQL Sever database
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)
objConnection.Open _
3
Visit: www.gcreddy.com for QTP Information
“Provider=SQLOLEDB;Data Source=atl-sql-01;” & _
“Trusted_Connection=Yes;Initial Catalog=Northwind;” & _
“User ID=fabrikamkenmyer;Password=34DE6t4G!;”
objRecordSet.Open “SELECT * FROM Customers”, _
objConnection, adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
Wscript.Echo objRecordSet.RecordCount
5) Open a Database Using a DSN
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)
objConnection.Open _
“Northwind;fabrikamkenmyer;34ghfn&!j”
objRecordSet.Open “SELECT * FROM Customers”, _
objConnection, adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
Wscript.Echo objRecordSet.RecordCount
6) Open Two Record sets
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)
Set objRecordSet2 = CreateObject(“ADODB.Recordset”)
objConnection.Open _
4
Visit: www.gcreddy.com for QTP Information
“Provider= Microsoft.Jet.OLEDB.4.0; ” & _
“Data Source=inventory.mdb”
objRecordSet.Open “SELECT * FROM GeneralProperties Where ComputerName =
‘Computer1′”, _
objConnection, adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
objRecordSet2.Open “SELECT * FROM Storage Where ComputerName =
‘Computer1′”, _
objConnection, adOpenStatic, adLockOptimistic
objRecordSet2.MoveFirst
Do Until objRecordset.EOF
Wscript.Echo objRecordset.Fields.Item(“ComputerName”)
Wscript.Echo objRecordset.Fields.Item(“OSName”)
objRecordSet.MoveNext
Loop
Do Until objRecordset2.EOF
Wscript.Echo objRecordset2.Fields.Item(“DriveName”), _
objRecordset2.Fields.Item(“DriveDescription”)
objRecordSet2.MoveNext
Loop
objRecordSet.Close
objRecordSet2.Close
objConnection.Close
7) Searching a Database Using String Criteria
Const adOpenStatic = 3
Const adLockOptimistic = 3
5
Visit: www.gcreddy.com for QTP Information
Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)
objConnection.Open _
“Provider = Microsoft.Jet.OLEDB.4.0; ” & _
“Data Source = eventlogs.mdb”
objRecordSet.Open “SELECT * FROM EventTable ” & _
“WHERE Type = ‘Error’”, objConnection, adOpenStatic, _
adLockOptimistic
objRecordSet.MoveFirst
Wscript.Echo “Number of records: ” & objRecordset.RecordCount
objRecordSet.Close
objConnection.Close
8) Insert Data into a database table using Database Command Object
Dim objCon,objCom
Set objCon=Createobject(“ADODB.connection”)
objCon.open”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:gcreddy.mdb;”
Set objCom=Createobject(“ADODB.Command”)
objCom.ActiveConnection=objCon
objCom.CommandText=”insert into Emp values(‘G C Reddy’,88233,30000)”
objCom.Execute
objCon.Close
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: www.gcreddy.com for QTP Information
Dim objCon,objCom,strEmpName,intEmpNo,intEmpSal,intRowcount,i
Set objCon=Createobject(“ADODB.connection”)
objCon.open”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:gcreddy.mdb;”
Set objCom=Createobject(“ADODB.Command”)
objCom.ActiveConnection=objCon
Datatable.AddSheet(“input”)
Datatable.ImportSheet “C:gcreddy.xls”,1,”input”
intRowcount=Datatable.GetSheet(“input”).GetRowCount
Msgbox intRowcount
For i=1 to intRowcount step 1
DataTable.SetCurrentRow(i)
strEmpName= DataTable.Value(1,”input”)
intEmpNo= DataTable.Value(2,”input”)
intEmpSal= DataTable.Value(3,”input”)
objCom.CommandText=”insert into Emp values( ‘”&strEmpName&”
‘,”&intEmpNo&”,”&intEmpSal&”)”
objCom.Execute
Next
objCon.Close
Set objCom=Nothing
Set objCon=Nothing
www.gcreddy.com
