I just setup a stored procedure with CF8, MySQL 5.0. Works fine running locally, but when I upload it to hostmysite.com, CF sends back the most helpful error message "null null the error occurred on line -1". I can also run the SP from a MySQL GUI connected to the live database, and it runs fine. Here's the CF code:
<cfstoredproc procedure="duplicate" datasource="#request.dsn#">
<cfprocparam cfsqltype="cf_sql_integer" value="#variables.account_id#">
I know it's the SP call that's getting the error, as a CFABORT before the SP ends normally, but after the SP results in the error.
It doesn't actually return anything back to CF -- it inserts some data into a table and ends. Am I missing something? Is there someway to get CF to run it without returning an error? I also tried a CFQUERY with "call duplicate(#variables.account_id#)" but got a message about it not having a recordset.
And as a workaround if needed -- are there any downsides to moving the same logic into a MySQL UDF? This may avoid the "null null" issue, but I don't know if there are any other differences.
Although I'm not that familiar with MySQL, it may be a security issue. If the stored procedure is simply doing an insert, try copying the insert statement out of the procedure and replace the cfstoredproc call with it to see if it is working with the same datasource. If that works, then look closer at the stored procedure itself. It could be a permissions type of issue there where the CF datasource doesn't have the appropriate access to execute it.