Simple VBS Script to Retrieve Data from Oracle

12 12 2009

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

Actions

Information

2 responses

30 03 2010
joel garry

I don’t quite understand how ORDER_ID is set.

30 03 2010
Charles Hooper

Joel,

The above VBS script was adapted from a sample macro script that I created for an ERP package. The ERP package automatically supplied a number of different variables with pre-filled values, and one of those was the ORDER_ID variable. When I created the plain VBS version of the script for this blog I neglected to define the ORDER_ID variable. Based on your feedback I have adjusted the script to define that variable and set a value for the variable.

Thanks for the feedback.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 148 other followers

%d bloggers like this: