Database Scripts 1

 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