SQL Stored Procedure with Parameters

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
LiveCodeBeginner
Posts: 26
Joined: Fri Oct 10, 2014 9:40 pm

SQL Stored Procedure with Parameters

Post by LiveCodeBeginner » Fri Oct 10, 2014 10:01 pm

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
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 3901
Joined: Sat Mar 24, 2007 2:54 am

Re: SQL Stored Procedure with Parameters

Post by Simon » Sat Oct 11, 2014 1:25 am

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.
I used to be a newbie but then I learned how to spell teh correctly and now I'm a noob!

LiveCodeBeginner
Posts: 26
Joined: Fri Oct 10, 2014 9:40 pm

Re: SQL Stored Procedure with Parameters

Post by LiveCodeBeginner » Thu Oct 16, 2014 9:49 pm

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! :evil: 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
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 3901
Joined: Sat Mar 24, 2007 2:54 am

Re: SQL Stored Procedure with Parameters

Post by Simon » Thu Oct 16, 2014 9:59 pm

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
I used to be a newbie but then I learned how to spell teh correctly and now I'm a noob!

LiveCodeBeginner
Posts: 26
Joined: Fri Oct 10, 2014 9:40 pm

Re: SQL Stored Procedure with Parameters

Post by LiveCodeBeginner » Thu Oct 16, 2014 10:21 pm

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
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 3901
Joined: Sat Mar 24, 2007 2:54 am

Re: SQL Stored Procedure with Parameters

Post by Simon » Thu Oct 16, 2014 10:31 pm

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
I used to be a newbie but then I learned how to spell teh correctly and now I'm a noob!

LiveCodeBeginner
Posts: 26
Joined: Fri Oct 10, 2014 9:40 pm

Re: SQL Stored Procedure with Parameters

Post by LiveCodeBeginner » Fri Oct 17, 2014 9:03 pm

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
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 3901
Joined: Sat Mar 24, 2007 2:54 am

Re: SQL Stored Procedure with Parameters

Post by Simon » Fri Oct 17, 2014 11:20 pm

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

Simon
I used to be a newbie but then I learned how to spell teh correctly and now I'm a noob!

LiveCodeBeginner
Posts: 26
Joined: Fri Oct 10, 2014 9:40 pm

Re: SQL Stored Procedure with Parameters

Post by LiveCodeBeginner » Mon Oct 20, 2014 6:33 pm

Hello,

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

Thank you.

Post Reply