MySQL Bugs: #12458: cant return parameters from stored procs when called from inside recursive func

Bug #12458	cant return parameters from stored procs when called from inside recursive func
Submitted:	9 Aug 2005 12:22	Modified:	21 Oct 2005 23:10
Reporter:	Karen Jude	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	Ver 14.11 Distrib 5.0.9 beta	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I am trying to return a DATE value from a mysql stored procedure to a php script. I am using the mysqli extensions (PHP ver 5.0.4) to do this and have tried both the output parameter and result set methods to do this with the same results. 

It seems that inless u call DATE_FORMAT() in your sql statement that retrieves the value to be output from the sp, then it just gets returned to the script as null: 

I had to change:

SELECT aDateField INTO outputParam FROM aTable ...

to: 

SELECT DATE_FORMAT( aDateField "%Y-%d-%m") INTO outputParam FROM aTable

to get anything back at all. 

However, when I just do a select statement straight from the php script using the mysqli_query function I can return the same DATE value fine whether I format them or not. 

It seems also that if I call the sp containing the statements from Mysql Query Browser, then the DATE value also appears correctly in the results returned wether I format it or not. 

I've therefore been unable to come to a conclusion whether this is a problem with the mysql stored procedures or the php extensions im using. 

In addition to this it seems that version 1.1.13 of MySql Query Browser does not like the % symbols inside the stored procs. I created one containing this symbol for the date formatting, and was not able to re-open it by right clicking on the name and selecting 'Edit Procedure'. I got a message 'symbol not recognised for %'. All other sps without this in them are fine.  

How to repeat:
as above

11 Aug - I had similar problems today with an int output parameter, so I dont think the data type is of any significance now. However in both this case today and the one with the date parameter, I am calling the stored proc using mysqli from a recursive function inside a php script. Again, the stored procedure fails to return the int parameter without giving any errors, but if  I do the same thing as a straight sql query, it works fine and the value from the db is there. 
This is not a showstopper at all, as I can get my application working with the ordinary sql queries, but I would like to know what is causing this and if there is a solution to the prob (even if it is just me changing the way I code :)  Please let me know if further info is required as I can probably send u the actual code im working on (I didnt want to paste it all into the bug report though).

changed title

Hi!

Can you please give me a reproducible test case that shows this behavour ?  Please upload your php scripts as well as your SQL queries so that we can try to reproduce this problem.

Thanks

Further to your request for feedback. It is difficult for me to send u a working version of my scripts and stored procs without sending u almost all of the database they rely on. I can send u this as import files for each table, but they will be quite large, so let me know if this is ok or not.

Hi!

Yes, its ok for you to send us the scripts.  YOu can either upload them here, or email them directly to me at jorge@mysql.com

thanks!

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".