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
I don’t quite understand how ORDER_ID is set.
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.
Hi ,
Here the connection string is using only provider , data source and credentials.I am able to open a successful connection using these details. But Could you please explain how to fetch table values from a particular table in a specific database.I don’t see any line specifying the name of database in the above code.Can you please help me
Sharaz,
In the example provided in this article, the following line appears:
In this case, all recordsets and commands variables that use the dbMyDBConnection variable will work with the database named MyDB (using the example code, you will change this name to be the name of one of the databases that are defined in the tnsnames.ora file). What that means is that everything will be selected from the same database. If you need to select from multiple databases, then you either need to create multiple variables like dbMyDBConnection (ex: dbDatabase1, dbDatabase2, etc.) and connect to each database, or have the DBA set up a database link from the database that you connect to in your script to the other databases (see the documentation: https://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_concepts002.htm#ADMIN12083 ). With method 1, to send the SQL statement to a different database (once connected to the different databases), you would just specify the correct connection variable (dbMyDBConnection, dbDatabase1, dbDatabase2, etc.) in the snpData.Open line. Using the second method, you would only have a single database connection in the script, but after the table name in the SQL statement, you would add @ and then the database link name (with no spaces in between).