?
Solved

VBS script to write to an Oracle DB

Posted on 2013-01-16
12
Medium Priority
?
2,265 Views
Last Modified: 2013-02-09
Hello experts,

I'm trying to write a logon(off) script that will record some data to an orcale database.

This script is to be run on winXP and Win7.

Herse what i have so far;

Set oShell = CreateObject( "WScript.Shell" )
userName=oShell.ExpandEnvironmentStrings("%USERNAME%")
computerName=oShell.ExpandEnvironmentStrings("%COMPUTERNAME%")
sessionName=oShell.ExpandEnvironmentStrings("%SESSIONNAME%")
clientName=oShell.ExpandEnvironmentStrings("%CLIENTNAME%")

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

const cnstCommand = 1 'Command type - 1 is for standard query
commandString = "INSERT INTO LogonEvent (UserName, ComputerName, AccessMethod, ScourceComputer, Date) VALUES ('userName', 'computerName', 'sessionName', 'clientName', " & Now()& ")" );"
thecommand.CommandText = commandString
thecommand.CommandType = cnstCommand
thecommand.ActiveConnection = connection
thecommand.Execute

wscript.quit

any help would be appreciated
0
Comment
Question by:jackstark
  • 7
  • 2
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38786063
what is the problem/error?
I would replace the "now()" by using the oracle function sysdate, otherwise you get into date issues. ..

Set oShell = CreateObject( "WScript.Shell" )
userName=oShell.ExpandEnvironmentStrings("%USERNAME%")
computerName=oShell.ExpandEnvironmentStrings("%COMPUTERNAME%")
sessionName=oShell.ExpandEnvironmentStrings("%SESSIONNAME%")
clientName=oShell.ExpandEnvironmentStrings("%CLIENTNAME%")

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

const cnstCommand = 1 'Command type - 1 is for standard query
commandString = "INSERT INTO LogonEvent (UserName, ComputerName, AccessMethod, ScourceComputer, Date) VALUES ('userName', 'computerName', 'sessionName', 'clientName', sysdate)" );"
thecommand.CommandText = commandString
thecommand.CommandType = cnstCommand
set thecommand.ActiveConnection = connection
thecommand.Execute

wscript.quit 

Open in new window

0
 
LVL 35

Expert Comment

by:YZlat
ID: 38787645
Are you getting any errors?

Are you using tnsnames.ora file?
0
 
LVL 35

Expert Comment

by:YZlat
ID: 38787696
Another thing is, looks like you have an extra ) in your INSERT statement
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 35

Expert Comment

by:YZlat
ID: 38787702
and I am not sure you are handling date correctly. Try

Set oShell = CreateObject( "WScript.Shell" )
userName=oShell.ExpandEnvironmentStrings("%USERNAME%")
computerName=oShell.ExpandEnvironmentStrings("%COMPUTERNAME%")
sessionName=oShell.ExpandEnvironmentStrings("%SESSIONNAME%")
clientName=oShell.ExpandEnvironmentStrings("%CLIENTNAME%")

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

const cnstCommand = 1 'Command type - 1 is for standard query
commandString = "INSERT INTO LogonEvent (UserName, ComputerName, AccessMethod, ScourceComputer, Date) VALUES ('userName', 'computerName', 'sessionName', 'clientName', to_date('" & Now() & "','MM-DD-YYY'));"
thecommand.CommandText = commandString
thecommand.CommandType = cnstCommand
thecommand.ActiveConnection = connection
thecommand.Execute

wscript.quit 

Open in new window

0
 
LVL 35

Expert Comment

by:YZlat
ID: 38787717
commandString = "INSERT INTO LogonEvent (UserName, ComputerName, AccessMethod, ScourceComputer, Date) VALUES ('userName', 'computerName', 'sessionName', 'clientName', " & Now()& "-->)<--" );"

extra one here
0
 
LVL 35

Accepted Solution

by:
YZlat earned 1000 total points
ID: 38787743
sorry, missed one Y:

commandString = "INSERT INTO LogonEvent (UserName, ComputerName, AccessMethod, ScourceComputer, Date) VALUES ('userName', 'computerName', 'sessionName', 'clientName', to_date('" & Now() & "','MM-DD-YYYY'));"

Open in new window


or try

commandString = "INSERT INTO LogonEvent (UserName, ComputerName, AccessMethod, ScourceComputer, Date) VALUES ('userName', 'computerName', 'sessionName', 'clientName', to_date('" & Now() & "','MM/DD/YYYY'));"

Open in new window

0
 
LVL 59

Expert Comment

by:LeeTutor
ID: 38871117
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
LVL 35

Expert Comment

by:YZlat
ID: 38871118
I think the issue is pretty clear -  OP had an extra parenthesis
0
 
LVL 35

Expert Comment

by:YZlat
ID: 38871126
and date issues can be corrected with to_date function
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38871143
I second that. Points for yzlat
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

While working, an annoying popup showing below will come and we cannot cancel or close it form the screen. The error message will come again and again.
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This Micro Tutorial will give you a basic overview of Windows Live Photo Gallery and show you various editing filters and touches to photos you can apply. This will be demonstrated using Windows Live Photo Gallery on Windows 7 operating system.

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question