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”)
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
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
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 _
“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 _
“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

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

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

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
—————————————————-