10:00 pm
Lets take one example - "database.mdb" is one database in MS Access and "EMP" is table in db.
EMP Table has 3 columns(empnumber,empname,empsalary).
empnumber empname empsalary
1 em1 1000
2 em2 2000
3 em3 3000
Execute the following script in QTP:
Dim con,rs
Set con=createobject("adodb.connection")
Set rs=createobject("adodb.recordset")
con.provider="microsoft.jet.oledb.4.0"
con.open"d:\database.mdb"
rs.open"select*from EMP",con
datatable.GetSheet(1).addparameter"EMP_No",""
datatable.GetSheet(1).addparameter"EMP_Name",""
datatable.GetSheet(1).addparameter"EMP_Sal",""
row=1
Do While Not rs.EOF
datatable.SetCurrentRow(row)
datatable.Value (1,1)=rs.fields("empnumber")
datatable.Value(2,1)=rs.fields("empname")
datatable.Value(3,1)=rs.fields("empsalary")
row=row+1
rs.movenext
Loop
After running the script u can see EMP table (from msaccess database) in runtime datatable of QTP.
Output:
EMP_No EMP_Name EMP_Sal
1 em1 1000
2 em2 2000
3 em3 3000
--------------------------------------------------------------------------------------------------
'Data Driven testing for Login Operation by fetching test data directly from a database
Dim objConnection, objRecordset
'Create Database connection Object, it used to connect to databases
Set objConnection = CreateObject("Adodb.Connection")
'Create Database Recordset Object, it used to perform operations on Database tables (Records)
Set objRecordset = CreateObject("Adodb.Recordset")
'Generate Connection String for MS Access database
objConnection.Provider = ("Microsoft.ACE.OLEDB.12.0")
objConnection.Open "C:\Documents and Settings\Administrator\Desktop\testdata.mdb"
objRecordset.Open "Select Agent, Password from Login",objConnection
Do Until objRecordset.EOF
SystemUtil.Run "C:\Program Files\HP\QuickTest Professional\samples\flight\app\flight4a.exe"
Dialog("text:=Login").Activate
Dialog("text:=Login").WinEdit("attached text:=Agent Name:").Set objRecordset.Fields("Agent")
Dialog("text:=Login").WinEdit("attached text:=Password:").Set objRecordset.Fields("Password")
Wait 1
Dialog("text:=Login").WinButton("text:=OK").Click
Window("text:=Flight Reservation").Close
objRecordset.MoveNext
Loop
objRecordset.Close
objConnection.Close
Set objRecordset = Nothing
Set objConnection = Nothing
--------------------------------------------------------------------
'Count Records for Login Table
Dim objConnection, objRecordset
Set objConnection = CreateObject("Adodb.Connection")
Set objRecordset = CreateObject("Adodb.Recordset")
objConnection.Provider = ("Microsoft.ACE.OLEDB.12.0")
objConnection.Open "C:\Documents and Settings\Administrator\Desktop\testdata.mdb"
objRecordset.Open "Select Agent, Password from Login",objConnection
RecCount=0
Do Until objRecordset.EOF
RecCount=RecCount+1
objRecordset.MoveNext
Loop
Msgbox RecCount
objRecordset.Close
objConnection.Close
Set objRecordset = Nothing
Set objConnection = Nothing
-----------------------------------------------------------------------------------------------------------
'Export data from a Database Table to Excel sheet
'Export data from a Database Table to Excel sheet
Dim objExcl, objWorkBook, objWorksheet
Dim objConnection, objRecordset
Set objConnection=Createobject("ADODB.connection")
Set objRecordset=Createobject("ADODB.recordset")
Set objExcl = Createobject("Excel.Application")
Set objWorkBook=objExcl.Workbooks.Open ("C:\Documents and Settings\Administrator\Desktop\dec1.xls")
Set objWorksheet=objWorkBook.Worksheets(1)
objConnection.Provider = ("Microsoft.ACE.OLEDB.12.0")
objConnection.Open ("C:\Documents and Settings\Administrator\Desktop\testdata.mdb")
objRecordset.Open "Select Agent,Password from Login",objConnection
objWorksheet.Cells(1,1)="Agent"
objWorksheet.Cells(1,2)="Password"
i=2
While objRecordset.EOF = False
objWorksheet.Cells(i,1)=objRecordset.Fields("Agent")
objWorksheet.Cells(i,2)=objRecordset.Fields("Password")
objRecordset.MoveNext
i=i+1
Wend
objWorkBook.Save
objWorkBook.Close
objExcl.Quit
Set objWorksheet=Nothing
Set objWorkBook=Nothing
Set objExcl=Nothing
objRecordset.Close
objConnection.Close
Set objRecordset=Nothing
Set objConnection=Nothing
------------------------------------------------------------------------------------------------------------
''Export data from a Database Table to a Text file
Dim objConnection, objRecordset
Dim objFso, objTextStream
Set objConnection=Createobject("ADODB.connection")
Set objRecordset=Createobject("ADODB.recordset")
Set objFso= CreateObject("Scripting.FileSystemObject")
Set objTextStream = objFso.OpenTextFile("C:\Documents and Settings\Administrator\Desktop\abcd.txt", 2)
objTextStream.WriteLine "Agent Password"
objTextStream.WriteLine ".................."
objConnection.Provider = ("Microsoft.ACE.OLEDB.12.0")
objConnection.Open ("C:\Documents and Settings\Administrator\Desktop\testdata.mdb")
objRecordset.Open "Select Agent,Password from Login",objConnection
While objRecordset.EOF = False
objTextStream.WriteLine objRecordset.Fields("Agent")&", "&objRecordset.Fields("Password")
objRecordset.MoveNext
Wend
objTextStream.Close
Set objTextStream = Nothing
Set objfso = Nothing
objRecordset.Close
objConnection.Close
Set objRecordset=Nothing
Set objConnection=Nothing
Working with DataBase in QTP - Understanding Connection and Recordset object
QTP Interacts with database creating an instance of ADODB object using ADO. ADO is a Microsoft technology and stands for ActiveX Data Objects and is a programming interface to interact and access data from a database.
Connection Object
Before a database can be accessed by QTP, An object for database connection has to be established.
Set adocon = createobject(“Adodb.connection”)
Once the object is created, we need to set connection string to connect to the database. We can define a connection string with details of database including database provider, database, and user credentials for access.
Some useful methods and properties of connection object are as follows:
Properties:
adocon.connectionstring – This property sets or returns the details used to create a connection to a data source. Includes details of driver, database,username and password.
Strconnection = "Driver=… "Server=svrnme;uid=username;pwd=password;"
For e.g to connect to a database for excel the strConnection would be like:
strcon = "Driver={Microsoft Excel Driver (*.xls)};Dbqls="+xfilename +";ReadOnly=0;"
For details of connection strings, see www.connectionstrings.com
adocon.ConnectionTimeout – this defines the time to wait for a connection to be established.
adocon.provider – This sets or gets connection provider name.
Methods
adocon.open – opens a database connection based on the connection string provided. adocon.Open connectionstring,userID,password,options
adocon.Execute – execute the sql statement provided
adocon.execute “Select * from table”
adocon.close – This closes the adodb connection.
RecordSet Object:
Once a connection has been established, we can create recordset object to hold a set of record from database. A recorset consists of records and column
Set rs = createobject(“”Adodb.recordset”)
Some useful methods and properties of RecordSet Objects are as follows:
Properties:
BOF property returns True if the current record position is before the first record in the Recordset,
EOF property returns True if the current record position is after the last record in the Recordset, otherwise it returns False. For a empty recordset,i.e no records in the recordset or empty recordset, value of BOF and EOF is false. So the property can be used in a loop to validate RecordSet does not return any records.
MaxCount Property returns the maximum value of records to be returned in a recordset.
rs.MaxCount = 20 will return 20 rows of data in recordset.
Methods:
rs.cancel – cancels an existing execution.
rs.clone – returns a clone of existing recorset and assigns to an object
set rsclone = rs.clone
rs.Close - closes instance of recordset
rs.open – opens a recordset based on query specified.
rs.open sqlquery, adocon
where sqlquery is query executed and adocon is connection object.
rs.move – moves the pointer in a recordset by specified count as defined in numrec
rs.move numrec, start.
Also movenext,moveprevious, movefirst, movelast can be used to move to specified location in recordset.
rs.fields.count gives number of items in the fields collection.
rs.field.item(i) returns specified item from the collection.
No comments
Post a Comment