VBScript Database Objects
1) Database Connection Object
It is used to connect to Databases.
(* Database Connection String only varies from One Database to another)
Create Database Connection Object
Set variable = CreateObject(“Adodb.Connection”)
2) Database Recordset Object
It is used to perform operations on Database tables (records)
Create Database Recordset Object
Set variable = CreateObject(“Adodb.Recordset”)
Database Scripting Examples
1) Read test data from a database and perform Data driven Testing for Login Functionality.
Dim objConnection, objRecordset
‘Creating Database Connection object, it is used to connect to databases
Set objConnection = CreateObject(“Adodb.Connection”)
‘Creating 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.ACE.OLEDB.12.0”)
objConnection.Open “C:\Users\G C REDDY\Desktop\testdb.accdb”
‘Fetch ttest data using SQL statements
objRecordset.Open “Select Agent, Password from Login”, objConnection
Do Until objRecordset.EOF = True
SystemUtil.Run “C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe”
Dialog(“Login”).Activate
Dialog(“Login”).WinEdit(“Agent Name:”).Set objRecordset.Fields(“Agent”)
Dialog(“Login”).WinEdit(“Password:”).Set objRecordset.Fields(“Password”)
Wait 2
Dialog(“Login”).WinButton(“OK”).Click
Window(“Flight Reservation”).Close
objRecordset.MoveNext
Loop
objRecordset.Close
objConnection.Close
Set objRecordset = Nothing
Set objConnection = Nothing
2) Read test data(Range of Records) from a database and perform Data driven Testing for Login Functionality.
Dim objConnection, objRecordset, RecordCount
‘Creating Database Connection object, it is used to connect to databases
Set objConnection = CreateObject(“Adodb.Connection”)
‘Creating 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.ACE.OLEDB.12.0”)
objConnection.Open “C:\Users\G C REDDY\Desktop\testdb.accdb”
‘Fetch ttest data using SQL statements
objRecordset.Open “Select Agent, Password from Login”, objConnection
RecordCount = 1
Do Until objRecordset.EOF = True
If RecordCount > 1 And RecordCount < 5 Then
SystemUtil.Run “C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe”
Dialog(“Login”).Activate
Dialog(“Login”).WinEdit(“Agent Name:”).Set objRecordset.Fields(“Agent”)
Dialog(“Login”).WinEdit(“Password:”).Set objRecordset.Fields(“Password”)
Wait 2
Dialog(“Login”).WinButton(“OK”).Click
Window(“Flight Reservation”).Close
End If
objRecordset.MoveNext
RecordCount = RecordCount + 1
Loop
objRecordset.Close
objConnection.Close
Set objRecordset = Nothing
Set objConnection = Nothing
3) Export data from a Database to Excel file.
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:\Users\G C REDDY\Desktop\xyz.xlsx”)
Set objWorksheet = objWorkbook.Worksheets(1)
objConnection.Provider =(“Microsoft.ACE.OLEDB.12.0”)
objConnection.Open “C:\Users\G C REDDY\Desktop\testdb.accdb”
objRecordset.Open “Select Agent, Password from Login”, objConnection
objWorksheet.cells(1, 1) = “Agent”
objWorksheet.cells(1, 2) = “Password”
row = 2
While objRecordset.EOF = False
objWorksheet.cells(row, 1) = objRecordset.Fields(“Agent”)
objWorksheet.cells(row, 2) = objRecordset.Fields(“Password”)
objRecordset.MoveNext
row = row+1
Wend
objworkbook.Save
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
objRecordset.Close
objConnection.Close
Set objRecordset = Nothing
Set objConnection = Nothing
4) 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:\Users\G C REDDY\Desktop\abcd.txt”, 2)
objConnection.Provider =(“Microsoft.ACE.OLEDB.12.0”)
objConnection.Open “C:\Users\G C REDDY\Desktop\testdb.accdb”
objRecordset.Open “Select Agent, Password from Login”, objConnection
objTextstream.WriteLine “Agent Password”
objTextstream.WriteLine “———”
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
5) Export data from an Excel File to a Text file
Dim objExcel, objWorkbook, objWorksheet, objFso, objTextstream, RowsCount
Set objExcel = CreateObject(“Excel.Application”)
Set objworkbook = objExcel.Workbooks.Open (“C:\Users\G C REDDY\Desktop\xyz.xlsx”)
Set objWorksheet = objWorkbook.Worksheets(1)
Set objFso = CreateObject(“Scripting.FileSystemObject”)
Set objTextstream = objFso.OpenTextFile(“C:\Users\G C REDDY\Desktop\abcd.txt”, 2)
RowsCount = objWorksheet.UsedRange.Rows.count
objTextstream.WriteLine “Agent Password”
objTextstream.WriteLine “———”
For i = 2 To RowsCount Step 1
objTextstream.WriteLine objWorksheet.Cells(i, 1) &”, “& objWorksheet.Cells(i, 2)
Next
objTextstream.Close
Set objTextstream = Nothing
Set objFso = Nothing
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
6) Export data from a Text File to an Excel file.
Dim objExcel, objWorkbook, objWorksheet, objFso, objTextstream, LineCount, i
Dim myLine, myField
Set objExcel = CreateObject(“Excel.Application”)
Set objworkbook = objExcel.Workbooks.Open (“C:\Users\G C REDDY\Desktop\abc.xlsx”)
Set objWorksheet = objWorkbook.Worksheets(3)
Set objFso = CreateObject(“Scripting.FileSystemObject”)
Set objTextstream = objFso.OpenTextFile(“C:\Users\G C REDDY\Desktop\abcd.txt”, 1)
RowsCount = objWorksheet.UsedRange.Rows.count
objWorksheet.cells(1, 1) = “Agent”
objWorksheet.cells(1, 2) = “Password”
LineCount=1
i=2
While objTextstream.AtEndOfStream = False
myLine = objTextstream.ReadLine
If LineCount > 2 Then
myField = Split(myLine, “, “)
objWorksheet.cells(i, 1) = myField(0)
objWorksheet.cells(i, 2) = myField(1)
i=i+1
End If
LineCount=LineCount + 1
Wend
objTextstream.Close
Set objTextstream = Nothing
Set objFso = Nothing
objWorkbook.Save
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing