SQL Stored Procedure with Parameters

[LiveCodeBeginner]

Hello all,

Maybe this has been addressed or not, but I can't seem to get my stored procedure to execute via LiveCode.

I know select statements work, but I can't do a select. I need the stored procedure . The stored procedure also takes parameters.

I can establish a connection to the database so that is not the issue.

Here is my code example:

Code: Select all

  
Local sqlScript
put "EXEC databaseName.schemaName.spName" && 'parameter1' & "," & 'parameter2' into sqlScript

put revOpenDataBase("ODBC","SQLReport",,dbUserName,dbUserPassword,"static") into dataBaseConnectionID
   
put revDataFromQuery(tab,cr,dataBaseConnectionID,sqlScript) into myVariable

Nothing is being returned though? I do not understand why. Anyone got any ideas?

Thanks for all the help!

[Simon]

Hi LiveCodeBeginner,

Welcome to the forum

Hey what are you going to do when by the end of the year you'll be a liveCode expert?
The-user-formally-known-as-LiveCodeBeginner

Here I've put your code and the dictionary code side by side, can you see the error?

Code: Select all

put revOpenDataBase("ODBC","SQLReport",,dbUserName,dbUserPassword,"static") into dataBaseConnectionID
get revOpenDatabase("mysql", "localhost", "dbName", myUsr, myPass, false, "/var/mysql.sock", 1, true)

Simon
Edit: oh I see from the dictionary as well:
get revOpenDatabase("odbc", "BizFile", , "jenkins" ,the dbPassword of me, "emulated static")
That looks wrong to me.

[LiveCodeBeginner]

Haha I have no idea what I will do when I become a expert by the end of the year! that is a good question?

Sorry I could not respond earlier I have been out of town. I am still having no luck with this! but challenge accepted indeed.
I was going by the example given in the dictionary? Should I be doing it another way?

Thank you for all the help!

[Simon]

OK here:
revOpenDatabase("odbc",host[:port], databaseName, [userName],[password],[cursorType])
also from the dictionary.
It shows item 3 is databaseName and you have it empty in your script.

Simon

[LiveCodeBeginner]

Hey,

The reason my dataBase Parameter is empty is because my ODBC called "SQLReport" contains this already therefore I did not need to specify it.

Thank you.

[Simon]

Hi,
ummm... "SQLReport" is in quotes so it is read as a string... nothing in it but SQLReport.
If it's a variable it shouldn't be quoted and the empty item should be remove (remove 1 comma) and replaced with a comma in the variable.

As it's written now your host[:port] is SQLReport and the database name is empty, nada, nill, nothing.

Simon

[LiveCodeBeginner]

Hello,

If that is the case then nothing should happened correct? Nothing should be returned right?

But that's not the case as it is working for me? How can that be? I am totally confused now. I do understand what you are saying though.

Thanks.

[Simon]

Ahh... so there is a number in dataBaseConnectionID?

Simon

[LiveCodeBeginner]

Hello,

Yes there is an id and everything. It connects without issue.

Thank you.