Called stored procedure causing "error in your SQL syntax..."

Scriptcase 9
#1

Greetings from a first time forum poster. I’ve created a stored procedure via the SC database builder interface. I’ve tested it there and it works without issue. However, when calling it via an sc_exec_sql macro it fails with an error message (debug mode):

1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘proc_insert_company(30)’ at line 1.

The procedure is as follows:

proc_insert_company | IN usrid int 11

BEGIN

INSERT INTO tbl_company(create_user_id) VALUES (usrid);

INSERT INTO tbl_user_company
(user_co_user_id, user_co_co_id, create_user_id)
SELECT create_user_id, id, create_user_id
FROM tbl_company
WHERE create_user_id = usrid;

END

The event I’m calling it from is the OnAfterInsert.

The code used is:

sc_exec_sql(“proc_insert_company($usrid)”);

$usrid is based on a lookup and as you can see from the above error, it’s passing the correct value of 30. Any suggestions would be great.

Cheers

#2

I typically first debug SP on the server,
there can be different reasons for errors - primary key violation, null values or other constrains violations
you query a just inserted record, is there a time lag on mysql server?

if your sp is tested multiple times with this user ID, you move on to testing SC side.
it could be a connection/rights issue, formating issue.
i have few tips. i usually enclose parameter in single quotes, even if is numeric. if a null value is passed it will brake without quotes . but you can extra check with PHP

sc_exec_sql does not return a value, you are better of using lookup, where str_sp is your
execute sp statement , on SQL server it will be like
“Exec proc_insert_company(@userid=$usrid)”
please check mysql for a proper command

you can return rows or information from your SP

sc_lookup(rs,$str_sp);

if(isset({rs[0][0]}))
{
echo {rs[0][0]};
}

}
else // No row found
{
echo 'Error. Could not fetch the import data
';
}

1 Like
#3

Thanks maximnl :slight_smile: I put the single quote around the variable and still no luck, however, after a bit more digging around I watched Nate’s ScriptCase tutorial on stored procedures and in one of the viewers questions about how to call a stored procedure, the answer was

sc_exec(“CALL sp(parameter1, parameter2, ect…)”);

Well that’s not quite correct it should be sc_exec_sql not just sc_exec but the clue was the “CALL” instruction which I was missing in mine

sc_exec_sql(“proc_insert_company($usrid)”);

So, after adding the CALL statement at the begining and retesting, it worked perfectly. So, the final code was

sc_exec_sql(“CALL proc_insert_company(’$usrid’)”);

Hope that assists anyone else that may encounter the same problem.

Cheers

2 Likes
#4

sc_exec is less practical as it does not show the SP output /results.
sc_lookup allows to get data out of your stored procedure and show it/log it.
you can use a variable in SP to concatenate whatever messages, and output it back, so the result comes into the SC recordset , as you would select from a table, it is just one row one column .