1/4/11

QTP - Database Scripts

1) Get Test Data From a Database and use in Data Driven Testing (through Scripting)


Dim objCon, objRs
'Creating an automation object in database connection class, it is used for connecting to databases.
Set objCon=CreateObject("Adodb.Connection")
'Creating an automation object in database record set class, it is used for performing operations on database tables(records).
Set objRs= CreateObject("Adodb.Recordset")
'Establishing connection string for Ms-Access database.
objCon.Provider=("Microsoft.Jet.oledb.4.0")
objCon.Open "C:\Documents and Settings\gcr.GCRC-9A12FBD3D9\Desktop\Flights.mdb"
objRs.Open "Select * from Login", objCon
Do Until objRs.EOF=True
SystemUtil.Run "C:\Program Files\HP\QuickTest Professional\samples\flight\app\flight4a.exe","","C:\Program Files\HP\QuickTest Professional\samples\flight\app\","open"
Dialog("Login").Activate
Dialog("Login").WinEdit("Agent Name:").Set objRs.Fields("Agent")
Dialog("Login").WinEdit("Password:").Set objRs.Fields("Pwd")
Dialog("Login").WinButton("OK").Click
Window("Flight Reservation").Close
objRs.MoveNext
Loop
objRs.Close
objCon.Close
Set objCon=Nothing
Set objRs=Nothing

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 Settings\admin\My Documents\Pavan.mdb"
6) rs.open"select*from Login",con
7) Set ex=createobject("Excel.Application")
8) Set a=ex.workbooks.open("C:\Documents and Settings\admin\My Documents\Pavan.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

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 Settings\gcr\My Documents\Pavan.txt",8)
objcon.provider=("Microsoft.jet.oledb.4.0")
objcon.open"C:\Documents and Settings\gcr\My Documents\Pavan.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=fabrikam\kenmyer;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;fabrikam\kenmyer;34ghfn&!j"

objRecordSet.Open "SELECT * FROM Customers", _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst
Wscript.Echo objRecordSet.RecordCount

6) Open Two Recordsets

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

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:\Pavan.mdb;"
Set objCom=Createobject("ADODB.Command")
objCom.ActiveConnection=objCon
objCom.CommandText="insert into Emp values('Pavan',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:\Pavan.mdb;"
Set objCom=Createobject("ADODB.Command")
objCom.ActiveConnection=objCon
Datatable.AddSheet("input")
Datatable.ImportSheet "C:\Pavan.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

10) Fetch data from a database, and compare with expected data in Excel file.

Dim objCon, objRs, objExcel, myFile, mysheet
Set objCon=CreateObject("Adodb.Connection")
Set objRs= CreateObject("Adodb.Recordset")
Set objExcel=CreateObject("Excel.Application")

Set myFile=objExcel.Workbooks.Open ("C:\Documents and Settings\gcr.GCRC-9A12FBD3D9\Desktop\TestData2.xls")

Set mySheet=myFile.Worksheets("Sheet1")

Rc=mySheet.usedrange.rows.count

'Msgbox Rc

objCon.Provider=("Microsoft.Jet.oledb.4.0")

objCon.Open "C:\Documents and Settings\gcr.GCRC-9A12FBD3D9\Desktop\Comp.mdb"

objRs.Open "Select EMPName from Employee", objCon

Do Until objRs.EOF=True
x=objRs.Fields("EMPName")
For j= 2 to Rc
y=mySheet.cells(j,"A")
If x=y Then
Reporter.ReportEvent micPass,"Res","Name: "& y &" Available"
' Else
'Reporter.ReportEvent micFail,"Res","Name: "& y &" Not Available"
End If
Next
objRs.MoveNext
Loop

objExcel.Quit

Set objExcel=Nothing

objRs.Close

objCon.Close

Set objRs=Nothing

Set objCom=Nothing

6 comments:

  1. appreciable and very good content

    ReplyDelete
  2. Thanks for your informative article on UFT automation testing tool. Your post helped me to understand the features and functionality of QTP automation testing tool. QTP training institute in Chennai with placement

    ReplyDelete
  3. Thanks for your informative article on UFT automation testing tool. Your post helped me to understand the features and functionality of QTP automation testing tool. QTP training

    ReplyDelete
  4. Hello Admin, this article you have written here is very informative. Thank you for that. I’m into HTML5 training in Chennai, and articles like these help me to be up to date on the technology. If anyone is interested in HTML training in Chennai can contact us.

    ReplyDelete
  5. Really awesome blog. Your blog is really useful for me. Thanks for sharing this informative blog. Keep update your blog.
    Oracle Training In Chennai

    ReplyDelete
  6. I am reading ur post from the beginning, it was so interesting to read & i feel thanks to you for posting such a good blog, keep updates regularly.Best Hadoop Training Institute In Chennai

    ReplyDelete