Database Scripts

 Database Scripts-I

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\Gcreddy.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\Gcreddy.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 Settings\gcr\My Documents\gcreddy.txt",8)
objcon.provider=("Microsoft.jet.oledb.4.0")
objcon.open"C:\Documents and Settings\gcr\My Documents\gcreddy.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

For More Scripts visit:

0 comments:

Post a Comment