Page 1 of 1
SQL Stored Procedure with Parameters
Posted: Fri Oct 10, 2014 10:01 pm
by 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!
Re: SQL Stored Procedure with Parameters
Posted: Sat Oct 11, 2014 1:25 am
by 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.
Re: SQL Stored Procedure with Parameters
Posted: Thu Oct 16, 2014 9:49 pm
by 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!
Re: SQL Stored Procedure with Parameters
Posted: Thu Oct 16, 2014 9:59 pm
by 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
Re: SQL Stored Procedure with Parameters
Posted: Thu Oct 16, 2014 10:21 pm
by 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.
Re: SQL Stored Procedure with Parameters
Posted: Thu Oct 16, 2014 10:31 pm
by 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
Re: SQL Stored Procedure with Parameters
Posted: Fri Oct 17, 2014 9:03 pm
by 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.
Re: SQL Stored Procedure with Parameters
Posted: Fri Oct 17, 2014 11:20 pm
by Simon
Ahh... so there is a number in dataBaseConnectionID?
Simon
Re: SQL Stored Procedure with Parameters
Posted: Mon Oct 20, 2014 6:33 pm
by LiveCodeBeginner
Hello,
Yes there is an id and everything. It connects without issue.
Thank you.