Justkez

A while ago I attempted to document what was needed to get VBScript to interact with Oracle. That piece covered how to insert rows into an Oracle table, and how to execute a stored procedure. Now I'd like to tackle the task (illiteration, awesome) of getting data OUT of an Oracle table and into a VBScript process.

I set about researching how this could be done for a small system I was tasked to work on, and found it to be surprisingly well documented. You should have some good results from Googling around for examples. I've put together my findings and implementation here in order to serve as a working example.

Connecting to Oracle

Before we can look at the code involved in retrieving rows from an Oracle table, we need to establish a connection to an Oracle instace - we do this with an ADODB.Connection object, and pass a connection string to its Open function. Have a look at the code below to see an example of this:

    Dim connectionString: connectionString = "DRIVER={Microsoft ODBC for Oracle};SERVER=oracle_server;User Id=oracle_user;Password=oracle_password;"
Dim connection: Set connection = CreateObject("ADODB.Connection")
  

Once this code is in place, and you have updated the connectionString variable to account for your server & account details you can initiate the connection to Oracle using the following line:

    connection.Open connectionString
' Stuff will happen here...
connection.Close
  

In between those lines, we'll start developing some magic which will enable you to get your data out of Oracle. Please note that it's good practice to use the connection.Close line to disconnect from Oracle - especially so in a testing situation, as you'll end up with many concurrent connections to Oracle.

Getting our records

Once you've established a connection to an Oracle instance the next step is to create and populate an ADODB.Recordset object - something which stores all the data returned from an SQL query you specify.

Ideally, you need to add the following line to where you initialise the rest of your object variables:

    Dim rows: Set rows = CreateObject("ADODB.Recordset")
  

The next step is where we execute an SQL query to populate rows - our new Recordset object. If you're just playing around with this code at the moment, I'd reccommend using a query with a limited set of results - otherwise you could be waiting around for a while for your results to come back, and potentially be innundated with message boxes!

    Set rows = connection.Execute("SELECT name,age FROM people WHERE age >= 18";)
  

This is telling rows to be populated by the results of our query - selecting two columns (name & age) of data from a made up table (people) who are 18 or older.

Using our records

By this point we have got a set of records/rows stored in our ADODB.Recordset object - named rows. Obviously they're pretty useless just sitting there - chances are you'll want to loop through and something with them...

The process for doing this is to perform a simple while loop on the rows object - saying "do something with each record then move on to the next one, until we're at the end of our record set". In code - this looks like the following:

    While Not rows.EOF
MsgBox rows.Fields("name").Value
rows.MoveNext
Wend
  

This will show a series of message boxes, depending on how many records there are, with the persons name - simple. There is another way to access the fields; you can pass a number to the Fields() method, and it will bring back a column value according to your query. For example:

    Set rows = connection.Execute("SELECT name,age FROM people");
While Not rows.EOF
  MsgBox rows.Fields(1).Value
  ' ... is the same as ...
  MsgBox rows.Fields("age").Value
Wend
  

These two are the same because age is the second column requested in our query (counting starts at 0, so second value has an index of 1). Using the column name is generally going to make more sense, especially when you have to go back and edit the code after a period of time.

That's it for the time being! It's pretty simple stuff, but it's nice to have a really basic example explained (I think). Full code is below, and a zip file is there if you want to download the code. Don't forget the housekeeping at the end!

    Option Explicit

Dim connectionString: connectionString = "DRIVER={Microsoft ODBC for Oracle};SERVER=;User Id=;Password=;"
Dim connection: Set connection = CreateObject("ADODB.Connection")
Dim rows: Set rows = CreateObject("ADODB.Recordset")

connection.Open connectionString
Set rows = connection.Execute("SELECT name,age FROM people")

While Not rows.EOF
  MsgBox rows.Fields("name").Value
  rows.MoveNext
Wend

connection.Close
Set rows = Nothing
Set connection = Nothing
  

Fairly recently I was approached to create a system which parses data out of a CSV file and dumps said data into an Oracle table so that it could be reported on. The standard procedure for this is to send the source files to a Unix system and run them through with SQL Loader, but I didn't fancy going down that route...

There's a fair amount of information around The Internet on getting VBScript to communicate with an Oracle database and to perform various operations, but it's all a little fragmented. I've tried to consolidate what I've learnt from my experience, covering:

1. Connecting to an Oracle database with VBScript
2. Sending INSERT statements to the Oracle database
3. Executing a stored procedure on the Oracle database to manipulate the inserted data

Connecting to an Oracle database

Getting VBScript to connect to Oracle is a pretty easy step - it's just a bit tricky to get your head around the various constants involved, and the order in which to setup various aspects of the connection:

    Dim connection, connectionString, theCommand, commandString
connectionString = "DRIVER={Microsoft ODBC for Oracle};SERVER=oracle_server;User Id=user;Password=password;"
Set connection = CreateObject("ADODB.Connection")
Set theCommand = CreateObject("ADODB.Command")
connection.Open connectionString
  

Depending on your ODBC setup, the driver name might be slightly different from "Microsoft ODBC for Oracle", so you may need to change it. The above script should connect your script to your given database, provided all the connection parameters are correct.

Sending an INSERT statement

Having an active connection to Oracle is pretty pointless unless you're actually going to send a query to it. We'll just work with a basic INSERT statement here, to get the point accross - obviously you'd need to adapt this to your situation:

    const cnstCommand = 1 'Command type - 1 is for standard query
commandString = "INSERT INTO people (name, email) VALUES ('John Doe', 'john@doe.com');"
thecommand.CommandText = commandString
thecommand.CommandType = cnstCommand
thecommand.ActiveConnection = connection
thecommand.Execute
  

As an example, this would create a new row in the people table with the given values - easy.

Executing a stored procedure

The database driver also allows you to execute stored procedures on your Oracle database. In my scenario, I sent data to a staging table, where it was then manipulated by a stored procedure which enforced some rules and output to a final table, which is used for the reports.

    const cnstStoredProcedure = 4 'Command type - 4 is for stored procedure
commandString = "STORED_PROCEDURE_NAME"
thecommand.CommandText = commandString
thecommand.CommandType = cnstStoredProcedure
thecommand.ActiveConnection = connection
thecommand.Execute
  

Voila, stored procedure executed.

I'm happy to go into slightly more detail in another post or two, if anyone needs further elaboration or to see a full program structure for running this properly. You may also want to implement some error trapping - you never know when someone else might lock out your table or user account!