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 Settings\gcreddy\Desktop\testdb.mdb"

objRecordset.Open "Select * from Login",objConnection

Do While objRecordset.EOF = False
SystemUtil.Run "C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe","","C:\Program Files\HP\Unified Functional Testing\samples\flight\app\","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 Settings\gcreddy\Desktop\testdb.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 Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe","","C:\Program Files\HP\Unified Functional Testing\samples\flight\app\","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 Settings\gcreddy\Desktop\testdb.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 Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe","","C:\Program Files\HP\Unified Functional Testing\samples\flight\app\","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 Settings\gcreddy\Desktop\abcd.xls")
Set objWorksheet = objWorkbook.Worksheets(1)

objConnection.Provider = ("Microsoft.Jet.OLEDB.4.0")
objConnection.Open "C:\Documents and Settings\gcreddy\Desktop\testdb.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 Settings\gcreddy\Desktop\abcd.xls")
Set objWorksheet = objWorkbook.Worksheets(1)

objConnection.Provider = ("Microsoft.Jet.OLEDB.4.0")
objConnection.Open "C:\Documents and Settings\gcreddy\Desktop\testdb.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 Settings\gcreddy\Desktop\xyz.txt", 2)

objConnection.Provider = ("Microsoft.Jet.OLEDB.4.0")
objConnection.Open "C:\Documents and Settings\gcreddy\Desktop\testdb.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 Settings\gcreddy\Desktop\xyz.txt", 2)

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and Settings\gcreddy\Desktop\abcd.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 Settings\gcreddy\Desktop\xyz.txt")

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and Settings\gcreddy\Desktop\abcd.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

0 comments:

Post a Comment