Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Oracle Macro Fun!

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

Tags (3)
5 Replies
Luminary
Luminary

Oracle Macro Fun!

Hi Dominic,

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

Regards,

Stephen

Not applicable

Oracle Macro Fun!

Hi Stephen

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

Thanks

Luminary
Luminary

Oracle Macro Fun!

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

Regards,

Stephen

Not applicable

Oracle Macro Fun!

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

Luminary
Luminary

Oracle Macro Fun!

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