How to retrieve data from an Oracle database with VBscript

(This post was originally published September 21, 2006)

This article will give you a brief example of how to create a VBscript process that can retrieve data from an Oracle database. The techniques presented here can more than likely be adapted to connect to other databases as well. I’ll walk through a script that I wrote to solve a small problem with a web-based application that I support. The finished script will also touch upon techniques for logging and setting the date/time from within a VBscript process.

The Problem
One of the applications that I support is a web-based timekeeping system that is comprised of nineteen web servers running against an Oracle database backend. All of the servers in this system are isolated from our normal network, located behind firewalls in what is referred to as a DMZ. Since these servers can not communicate with any other servers to synchronize their time settings, their individual date/time on each server can vary widely. This generally isn’t too big of an issue, but with this particular application, date/times that appear on reports or in log files are based on the web server’s date/time stamp, and this can be confusing if these date/time stamps are too far out of sync. Suffice it to say that it would be optimal if the database server and all of the web servers had the same date/time settings.

The Solution

I decided to tackle this problem by writing a little VBscript that would run on each of the web servers, query the Oracle database server for the current date/time, and subsequently reset the web server’s date/time to match the database server. VBscript makes this fairly easy. You’ll need to have Windows Script and the Oracle ODBC driver (included with the Oracle client, I believe) installed.

Here is the complete script: DBSetDateTime.zip

Now let’s take a detailed look at the a few of the key steps in the script.
Note: Some of the lines have been artificially word wrapped for formatting purposes in this blog. Be sure to refer to the complete script in the above link for the actual source code.

Section 1

This section defines the variables that will contain the PL/SQL command to retrieve the current date/time from the database and the connection string that will allow the VBscript to communicate with the database. This connection string may vary a bit depending on what version of Oracle you are trying to access, but it should be fairly similar overall. The value that you supply for SERVER is the name of the instance (or SID) referenced in your tnsnames.ora file for the database you wish to connect to.

The CodeProject site has an article that describes an alternative connection string method that does not rely on an entry in the tnsnames.ora file (read the article here).

Section 2

This section creates the connection object as well as the rows object which will be the container recordset for the data we retrieve from the database.

Section 3

This set of statements creates the Windows Script and file handling objects. We’ll use the Windows Script object for executing the date and time commands, and the file handling object for writing to our log file.

Section 4

This section shows just how easy it is to create a log file for your scripts. By default it will create the log file in the same directory as the script (you can change this if you wish). The log file will be over written each time the script is executed.

Section 5

Here’s the meat of the script. First we open the connection to the database using the connection string we setup earlier and then execute our PL/SQL statement, returning the results into the rows object. The loop then steps through each row returned and processes it. In this case only a single row containing the date and time is returned, which is then parsed into separate variables. The script also writes this information to the log file.

As you can see, it would be fairly easy to process a result set that had many rows, using the rows object. I may explore more complicated queries in a future article.

Section 6

This section of the script takes the values retrieved from the database and executes the date and time commands using the Windows Script object. If an error occurs for one of these commands, a line is written to the log file.

Section 7

The remainder of the script finishes up the log file and then destroys all of the objects created within the script.

With the the script completed, all that was left to do was place it on each of the web servers and schedule it to run nightly via the Windows Scheduled Tasks application. Now, all of the web servers will have the same date/time setting as the database server. I sure hope that the database server has the correct date/time!

Hopefully, this little script will prove useful as a starting point for developing VBscripts that can make use of data stored within your Oracle databases.


Comments copied from the original blog entry:

Hector
Thursday, March 05, 2009 8:17 AM

Simply, clear, well explained, perfect.


Mads Bjerre
Tuesday, August 11, 2009 5:13 AM

Well maybe you should point out that this solution is dependent on regional settings!!

If your system for instance is configured for scandinavian date/time, you will get day and month switched 🙂

A workaround could be to read the registry keys sShortDate and sTimeFormat from HKCUControl PanelInternational and from those create the query for Oracle so it matches what you should parse to date/time.

Of course it would get a bit more complicated, but it would also make it universal and able to handle for instance 24/12 hour time.

Besides that I found it very useful and well documented!!


Atul
Monday, August 31, 2009 9:30 PM

Hi,
Grt article..Thanks alot for the effort….helped me a lot

thx


alex
Thursday, February 04, 2010 7:34 PM

Thank you Michael! It was just what I need to start learning Vb scripting. I will need to write a monitoring script for Oracle db . Great help !


Ryan
Friday, October 01, 2010 9:02 PM

Cheers Michael, Vb has never been high on my list of priorities, but this script is easy enough to get a grip on you’ve got me inspired to learn more. Thanks!


FMR
Thursday, March 03, 2011 2:04 PM

I really like your coding style, very clean and organized. Wish everyone wrote code like this. Thanks!


Seeni
Tuesday, July 19, 2011 4:32 AM

Hi,

When I’m trying to connect to a oracle Db, I’m getting below error.

[Microsoft][ODBC driver for Oracle][Oracle]Error while trying to retrieve text for error ORA-01019

Line (9): “FSO.open “Driver={Microsoft ODBC for Oracle}; Server=172.25.92.102; Uid=INT01_BEE; Pwd=INT01_BEE;””.

Please kindly help me on this.

Cheers,
Seeni.


Menno
Tuesday, September 06, 2011 5:46 AM

Works great! Thanks.


 

Leave a Reply

Your email address will not be published. Required fields are marked *