Solved

VBS script to write to an Oracle DB

Posted on 2013-01-16
12
2,026 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
12 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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 250 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

By default the complete memory dump option is disabled in windows . If we want to enable the complete memory dump for a diagnostic purpose, we have a solution for it. here we are using the registry method to enable this.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This Micro Tutorial will teach you the basics of configuring your computer to improve its speed. It will also teach you how to disable programs that are running in the background simultaneously. This will be demonstrated using Windows 7 operating…

828 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