topic Oracle Macro Fun! in QlikView

Oracle Macro Fun!

Fri, 04 Mar 2011 13:35:16 GMT

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

Oracle Macro Fun!

stephencredmond — Fri, 04 Mar 2011 14:37:40 GMT

Hi Dominic,

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

Regards,

Stephen

Oracle Macro Fun!

Fri, 04 Mar 2011 14:44:33 GMT

Hi Stephen

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

Thanks

Oracle Macro Fun!

stephencredmond — Fri, 04 Mar 2011 14:52:18 GMT

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

Regards,

Stephen

Oracle Macro Fun!

Fri, 04 Mar 2011 14:58:47 GMT

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

Oracle Macro Fun!

stephencredmond — Fri, 04 Mar 2011 15:06:42 GMT

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