Oracle Macro Fun!

[#1]

Hi All

I'm trying to update the value in a stored procedure using a macro, but it's failing to connect to Oracle; my code is:

sub test()

Dim connection, connectionString, theCommand, commandString
connectionString = "DSN=EFIN;Uid=user;Pwd=pword;"
Set connection = CreateObject("ADODB.Connection")

Set theCommand = CreateObject("ADODB.Command")

connection.Open connectionString

If connection.State = adStateOpen Then
MsgBox "Welcome to bnhft!"
Else
MsgBox "Sorry. No bnhft today."
End If

const cnstStoredProcedure = 4 'Command type - 4 is for stored procedure
commandString = "PKG_PARAM.SET_FLAG"
thecommand.CommandText = commandString
thecommand.CommandType = cnstStoredProcedure
thecommand.ActiveConnection = connection
thecommand.Parameters.Append thecommand.CreateParameter("FLAG",adChar,adParamInput,"1")
thecommand.Execute

end sub

I just get the message "Sorry. No bnhft today." indicating that the connection has failed. The DSN works fine in the reload script?

Any advice welcome!

Thanks

Dominic

[stephencredmond]

Hi Dominic,

Do you have System Access turned on in the security section of the module editor?

Regards,

Stephen

[#3]

Hi Stephen

Yes I have System access set on and local security Allow system access.

Thanks

[stephencredmond]

Does it work outside of QlikView if you just create a .vbs file and run that?

Regards,

Stephen

[#5]

you're right it doesn't work as a vbs either.....

Not a QV issue then but still I don't see what's wrong with my connection string?

Thanks

[stephencredmond]

ODBC connection string is different than an OLEDB one. If you want to use the DSN, it might look like this:

"Provider=MSDASQL.1;Password=password;Persist Security Info=True;User ID=userid;Data Source=DSNName"

Regards,

Stephen