Solved

VBS script to write to an Oracle DB

Posted on 2013-01-16
12
2,081 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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.
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…
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.
Suggested Courses

628 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