(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

Option Explicit

'* Define Constants / Variables
'SQL command to retrieve date and time from server
Const DateTimeSQL = "SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') _
   AS DateTime FROM DUAL" 
Dim connectionString: connectionString = "DRIVER={Microsoft ODBC for Oracle}; _ 
   SERVER=servername;User Id=userID;Password=password;"

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

'* Create the connection
Dim connection: Set connection = CreateObject("ADODB.Connection")
Dim rows: Set rows = CreateObject("ADODB.Recordset")

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

'* Create objects
Dim WshShell
Set WshShell = CreateObject("WScript.Shell")

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")

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

'* Create log file
Dim LogFile
Set LogFile = fso.CreateTextFile("DBSetDateTime.log", true)
LogFile.WriteLine("===================================")
LogFile.WriteLine("|   DBSetDateTime.vbs Log         |")
LogFile.WriteLine("===================================")
LogFile.WriteBlankLines 1
LogFile.WriteLine("Current computer date and time: " & Now)
LogFile.WriteBlankLines 1

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

'* Open connection
connection.Open connectionString

'* Execute SQL
Set rows = connection.Execute(DateTimeSQL)

While Not rows.EOF
  '* Populate variables with results from the query
  sDate = Left(rows.Fields("DateTime").Value, 10)
  sTime = Right(rows.Fields("DateTime").Value, 8)
  LogFile.WriteLine("Retrieved database server date: "  _
    & sDate & "  and time: " & sTime)
  LogFile.WriteBlankLines 1
 
  rows.MoveNext
Wend

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

'* Set the local computer's date and time
sCmd = "cmd.exe /c time " + sTime
iRC1 = WshShell.Run(sCmd, 0, TRUE)
if iRC1 <> 0 then
  LogFile.WriteLine("*** Error returned  _
    from time command: " & iRC1) End if  sCmd = "cmd.exe /c date " + sDate
iRC2 = WshShell.Run(sCmd, 0, TRUE)
if iRC2 <> 0 then   LogFile.WriteLine("*** Error returned  _
    from date command: " & iRC2) End if

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

If iRC1 = 0 And iRC2 = 0 Then
  LogFile.WriteLine("Computer date  _
    and time updated successfully.")

Else
  LogFile.WriteLine("Computer date  _
    and time update failed!")

End If

LogFile.WriteBlankLines 1
LogFile.WriteLine("====================================")

'* Close log file
LogFile.Close

'* Close connection and clean up objects
connection.Close
Set rows = Nothing
Set connection = Nothing
Set WshShell = Nothing
Set fso = Nothing
Set LogFile = Nothing

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: