Blog

21

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

1
2
3
4
5
6
7
8
9
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

1
2
3
'* 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

1
2
3
4
5
6
'* 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

1
2
3
4
5
6
7
8
9
'* 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
'* 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
'* 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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.

 

Posted in: Development

Post Rating

Comments

Hector
# Hector
Thursday, March 05, 2009 11:17 AM
Simply, clear, well explained, perfect.
Mads Bjerre
# Mads Bjerre
Tuesday, August 11, 2009 8: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 HKCU\Control Panel\International 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
# Atul
Tuesday, September 01, 2009 12:30 AM
Hi,
Grt article..Thanks alot for the effort....helped me a lot

thx
alex
# alex
Thursday, February 04, 2010 10: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
Saturday, October 02, 2010 12:02 AM
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
# FMR
Thursday, March 03, 2011 5:04 PM
I really like your coding style, very clean and organized. Wish everyone wrote code like this. Thanks!
Seeni
# Seeni
Tuesday, July 19, 2011 7: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
# Menno
Tuesday, September 06, 2011 8:46 AM
Works great! Thanks.
Anonymous
# Anonymous
Thursday, September 22, 2011 5:47 PM
http://selimcivelek.shikshik.org/2011/09/22/drywall-stime/

Post Comment

Name (required)

Email (required)

Website

CAPTCHA image
Enter the code shown above:

  
christian louboutin outlet christian louboutin outlet cheap canada goose