Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2187
  • Last Modified:

VBS script to write to an Oracle DB

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
jackstark
Asked:
jackstark
  • 7
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
YZlatCommented:
Are you getting any errors?

Are you using tnsnames.ora file?
0
 
YZlatCommented:
Another thing is, looks like you have an extra ) in your INSERT statement
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
YZlatCommented:
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
 
YZlatCommented:
commandString = "INSERT INTO LogonEvent (UserName, ComputerName, AccessMethod, ScourceComputer, Date) VALUES ('userName', 'computerName', 'sessionName', 'clientName', " & Now()& "-->)<--" );"

extra one here
0
 
YZlatCommented:
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
 
LeeTutorretiredCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
YZlatCommented:
I think the issue is pretty clear -  OP had an extra parenthesis
0
 
YZlatCommented:
and date issues can be corrected with to_date function
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I second that. Points for yzlat
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now