Simple VBS Script to Retrieve Data from Oracle

December 12, 2009

All recent releases of the Windows operating system include the Windows Scripting Host, which allows executing program commands that look much like the macro language used in Excel, Access, and Word, as well as the original line of Microsoft’s Visual Basic (before the introduction of .Net).  Scripts intended to be excuted by the Windows Scripting Host have an extension of .VBS and are executed either with cscript that outputs to a command line window, or the default wscript that outputs to Windows popup messages.

A simple script that connects to an Oracle database (without using ODBC), queries a table, and then performs a comparison on the values retrieved from the table follows:

'Save as ConnectDB.vbs
Dim strSQL
Dim strUsername
Dim strPassword
Dim snpData
Dim dbMyDBConnection
Dim ORDER_ID         'Note that this variable was omitted originally as it was automatically supplied by an ERP package

Set snpData = CreateObject("ADODB.Recordset")
Set dbMyDBConnection = CreateObject("ADODB.Connection")
strUsername = "MyUser"
strPassword = "MySecret"

dbMyDBConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=MyDB;User ID=" & strUsername & ";Password=" & strPassword & ";"
dbMyDBConnection.Open

ORDER_ID = "MYORDERID123"  'Note that this variable value was omitted originally as it was automatically supplied by an ERP package
strSQL = "SELECT C1, C2 FROM MY_TABLE WHERE PURC_ORDER_ID='" & ORDER_ID & "'"

snpData.Open strSQL, dbMyDBConnection

If Not(snpData.EOF) Then
    If cInt(snpData("c1")) < cInt(snpData("c2")) Then
        MsgBox "C1 is Less than C2"
    End If
    If cInt(snpData("c1")) > cInt(snpData("c2")) Then
        MsgBox "C1 is Greater than C2"
    End If
    If cInt(snpData("c1")) = cInt(snpData("c2")) Then
        MsgBox "C1 is Equal to C2"
    End If

    MsgBox cInt(snpData("c1")) - cInt(snpData("c2"))
Else
    MsgBox "Ut oh, No Matching Records"
End If

snpData.Close
dbMyDBConnection.Close

Set snpData = Nothing
Set dbMyDBConnection = Nothing

Once the script is saved, double-clicking the script should automatically execute it using wscript.  Alternatively, open a Windows command prompt and type the following (assuming that the script is named ConnectDB.vbs and is saved in the root of the C:\ drive):

cscript c:\ConnectDB.vbs