Database Scripts

 Database Scripting using VBScript

1) Data Driven Testing by fetching test data (All Records) from a database table

Dim objConnection, objRecordset
‘Create Database Connection Object, It is used to connect to databases
Set objConnection = CreateObject(“Adodb.Connection”)
‘Create Database Recordset Object, It is used to perform operations on database Tables
Set objRecordset = CreateObject(“Adodb.Recordset”)
‘Create Connection String for MS Access Database
objConnection.Provider = (“Microsoft.Jet.OLEDB.4.0”)
objConnection.Open “C:Documents and SettingsgcreddyDesktoptestdb.mdb”

objRecordset.Open “Select * from Login”,objConnection

Do While objRecordset.EOF = False
SystemUtil.Run “C:Program FilesHPUnified Functional Testingsamplesflightappflight4a.exe”,””,”C:Program FilesHPUnified Functional Testingsamplesflightapp”,”open”
Dialog(“Login”).Activate @@ hightlight id_;_591710_;_script infofile_;_ZIP::ssf1.xml_;_
Dialog(“Login”).WinEdit(“Agent Name:”).Set objRecordset.Fields(“Agent”) @@ hightlight id_;_1181948_;_script infofile_;_ZIP::ssf2.xml_;_
Dialog(“Login”).WinEdit(“Password:”).Set objRecordset.Fields(“Password”) @@ hightlight id_;_1050832_;_script infofile_;_ZIP::ssf3.xml_;_
wait 2
Dialog(“Login”).WinButton(“OK”).Click @@ hightlight id_;_264494_;_script infofile_;_ZIP::ssf4.xml_;_
Window(“Flight Reservation”).Close
objRecordset.MoveNext
Loop
objRecordset.Close
objConnection.Close

Set objRecordset = Nothing
Set objConnection = Nothing
———————————————————–
2) Data Driven Testing by fetching test data (Range of Records) from a database table

Dim objConnection, objRecordset, RecordCount
Set objConnection = CreateObject(“Adodb.Connection”)
Set objRecordset = CreateObject(“Adodb.Recordset”)
objConnection.Provider = (“Microsoft.Jet.OLEDB.4.0”)
objConnection.Open “C:Documents and SettingsgcreddyDesktoptestdb.mdb”

objRecordset.Open “Select * from Login”,objConnection

RecordCount = 0
Do While objRecordset.EOF = False
RecordCount = RecordCount + 1

If RecordCount > 2 And RecordCount < 5 Then
SystemUtil.Run “C:Program FilesHPUnified Functional Testingsamplesflightappflight4a.exe”,””,”C:Program FilesHPUnified Functional Testingsamplesflightapp”,”open”
Dialog(“Login”).Activate @@ hightlight id_;_591710_;_script infofile_;_ZIP::ssf1.xml_;_
Dialog(“Login”).WinEdit(“Agent Name:”).Set objRecordset.Fields(“Agent”) @@ hightlight id_;_1181948_;_script infofile_;_ZIP::ssf2.xml_;_
Dialog(“Login”).WinEdit(“Password:”).Set objRecordset.Fields(“Password”) @@ hightlight id_;_1050832_;_script infofile_;_ZIP::ssf3.xml_;_
wait 2
Dialog(“Login”).WinButton(“OK”).Click @@ hightlight id_;_264494_;_script infofile_;_ZIP::ssf4.xml_;_
Window(“Flight Reservation”).Close
End If
objRecordset.MoveNext
Loop
objRecordset.Close
objConnection.Close

Set objRecordset = Nothing
Set objConnection = Nothing
——————————————————————–
3) Read specific Record from a database table and execute Login Test

Dim objConnection, objRecordset, RecordCount
Set objConnection = CreateObject(“Adodb.Connection”)
Set objRecordset = CreateObject(“Adodb.Recordset”)
objConnection.Provider = (“Microsoft.Jet.OLEDB.4.0”)
objConnection.Open “C:Documents and SettingsgcreddyDesktoptestdb.mdb”

objRecordset.Open “Select * from Login”,objConnection

RecordCount = 0
Do While objRecordset.EOF = False
RecordCount = RecordCount + 1

If RecordCount = 2 Then
SystemUtil.Run “C:Program FilesHPUnified Functional Testingsamplesflightappflight4a.exe”,””,”C:Program FilesHPUnified Functional Testingsamplesflightapp”,”open”
Dialog(“Login”).Activate @@ hightlight id_;_591710_;_script infofile_;_ZIP::ssf1.xml_;_
Dialog(“Login”).WinEdit(“Agent Name:”).Set objRecordset.Fields(“Agent”) @@ hightlight id_;_1181948_;_script infofile_;_ZIP::ssf2.xml_;_
Dialog(“Login”).WinEdit(“Password:”).Set objRecordset.Fields(“Password”) @@ hightlight id_;_1050832_;_script infofile_;_ZIP::ssf3.xml_;_
wait 2
Dialog(“Login”).WinButton(“OK”).Click @@ hightlight id_;_264494_;_script infofile_;_ZIP::ssf4.xml_;_
Window(“Flight Reservation”).Close
End If
objRecordset.MoveNext
Loop
objRecordset.Close
objConnection.Close

Set objRecordset = Nothing
Set objConnection = Nothing
——————————————————————–
4) Export data from a Database to Excel
Dim objConnection, objRecordset, objExcel, objWorkbook, objWorksheet
Set objConnection = CreateObject(“Adodb.Connection”)
Set objRecordset = CreateObject(“Adodb.Recordset”)

Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:Documents and SettingsgcreddyDesktopabcd.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)

objConnection.Provider = (“Microsoft.Jet.OLEDB.4.0”)
objConnection.Open “C:Documents and SettingsgcreddyDesktoptestdb.mdb”
objRecordset.Open “Select * from Login”, objconnection

objWorksheet.Cells(1, 1) =”Agent”
objWorksheet.Cells(1, 2) =”Password”

i = 2
Do Until objRecordset.EOF
objWorksheet.Cells(i, 1) = objRecordset.Fields(“Agent”)
objWorksheet.Cells(i, 2) = objRecordset.Fields(“Password”)
objRecordset.MoveNext
i = i +1
Loop
objWorkbook.Save
objExcel.Quit

objRecordset.Close
objConnection.Close

Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

Set objRecordset = Nothing
Set objConnection = Nothing
—————————————————————
‘5) Compare database table data with excel data
Dim objConnection, objRecordset, objExcel, objWorkbook, objWorksheet
Set objConnection = CreateObject(“Adodb.Connection”)
Set objRecordset = CreateObject(“Adodb.Recordset”)

Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:Documents and SettingsgcreddyDesktopabcd.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)

objConnection.Provider = (“Microsoft.Jet.OLEDB.4.0”)
objConnection.Open “C:Documents and SettingsgcreddyDesktoptestdb.mdb”
objRecordset.Open “Select * from Login”, objconnection

RowCount = objWorksheet.UsedRange.Rows.count
objWorksheet.Cells(1, 3) = “Result”
For i = 2 To RowCount Step 1
ExcelData1 = objWorksheet.Cells(i, 1)
ExcelData2 = objWorksheet.Cells(i, 2)
DBdata1 = objRecordset.Fields(“Agent”)
DBdata2 =objRecordset.Fields(“Password”)

If ExcelData1 = DBdata1 And ExcelData2 = DBdata2  Then
objWorksheet.Cells(i, 3) = “Passed”
Else
objWorksheet.Cells(i, 3) = “Failed”
End If
objRecordset.MoveNext
Next
objWorkbook.Save
objExcel.Quit

objRecordset.Close
objConnection.Close

Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

Set objRecordset = Nothing
Set objConnection = Nothing
————————————————————-
‘6) Export data from a Database to Text File

Dim objConnection, objRecordset, objFso, objTextstream
Set objConnection = CreateObject(“Adodb.Connection”)
Set objRecordset = CreateObject(“Adodb.Recordset”)

Set objFso = CreateObject(“Scripting.FileSystemObject”)
Set objTextstream = objFso.OpenTextFile(“C:Documents and SettingsgcreddyDesktopxyz.txt”, 2)

objConnection.Provider = (“Microsoft.Jet.OLEDB.4.0”)
objConnection.Open “C:Documents and SettingsgcreddyDesktoptestdb.mdb”
objRecordset.Open “Select * from Login”, objconnection

objTextstream.WriteLine “Agent Password”
objTextstream.WriteLine “————–”

Do Until objRecordset.EOF
objTextstream.WriteLine objRecordset.Fields(“Agent”) &”, “&objRecordset.Fields(“Password”)
objRecordset.MoveNext
Loop
objTextstream.Close

objRecordset.Close
objConnection.Close

Set objTextstream = Nothing
Set objfso = Nothing

Set objRecordset = Nothing
Set objConnection = Nothing
—————————————————————
7′) Export data from an Excel to Text File

Dim objFso, objTextstream, objExcel, objWorkbook, objWorksheet, RowCount

Set objFso = CreateObject(“Scripting.FileSystemObject”)
Set objTextstream = objFso.OpenTextFile(“C:Documents and SettingsgcreddyDesktopxyz.txt”, 2)

Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:Documents and SettingsgcreddyDesktopabcd.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)

RowCount = objWorksheet.UsedRange.Rows.Count
objTextstream.WriteLine “Agent Password Result”
objTextstream.WriteLine “—————–”
For i = 2 To RowCount Step 1
objTextstream.WriteLine objWorksheet.Cells(i, 1) &”, “&objWorksheet.Cells(i, 2) &”, “&objWorksheet.Cells(i, 3)
Next

objTextstream.Close
objExcel.Quit

Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

Set objTextstream = Nothing
Set objfso = Nothing

‘8) Export data from a Text file to Excel

Dim objFso, objTextstream, objExcel, objWorkbook, objWorksheet, myLine, myField

Set objFso = CreateObject(“Scripting.FileSystemObject”)
Set objTextstream = objFso.OpenTextFile(“C:Documents and SettingsgcreddyDesktopxyz.txt”)

Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:Documents and SettingsgcreddyDesktopabcd.xls”)
Set objWorksheet = objWorkbook.Worksheets(3)

objWorksheet.Cells(1, 1) = “Agent”
objWorksheet.Cells(1, 2) = “Password”
objWorksheet.Cells(1, 3) = “Result”

LineCount =0
i= 2
Do Until objTextstream.AtEndOfStream
myLine = objTextstream.ReadLine
LineCount = LineCount + 1
If LineCount > 2 Then
myField = Split(myLine, “, “)
objWorksheet.Cells(i, 1) = myField(0)
objWorksheet.Cells(i, 2) = myField(1)
objWorksheet.Cells(i, 3) = myField(2)
i = i+1
End If
Loop
objWorkbook.Save
objExcel.Quit

Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

objTextstream.Close
Set objTextstream = Nothing
Set objfso = Nothing
——————————————–
9) Export data from Excel to Database

10) Export data from Text file Database

 

Comments

This site uses Akismet to reduce spam. Learn how your comment data is processed.