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):