Programming, CakePHP and commentary from Britain.

Popular

CakePHP Livesearch
CakePHP Sessions
VBScript & Excel
VBScript & Oracle

Search

E-mail

kdobson@gmail.com

Subscribe

RSS 2.0

VBScript and Oracle, Redux

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

Download Oracle - retrieve rows VBScript (zip)

8 comments

Contribute...