MySQL Forums
Forum List  »  PHP

Stored procedure works from Query browser but not when called from PHP
Posted by: Steve Brewer
Date: October 16, 2009 01:57PM

From Query browser (on Windows).
CALL FRED('TABLE_A', 'FIELD_1', 'New Value', 'KEY');
Works perfectly.

From PHP (on Linux) using the same login account it gets nowhere. If I remove the EXECUTE statement the procedure runs so I don't think its to do with the passing of variables. If I dump out the CONCAT string it looks fine, and if I cut and paste it into a query browser it works perfectly.

I know there are sometimes differences between Windows and Linux and I'm also not on top of character sets, but nothing that usually solves funnies is working this time and I'm about to give up. I also have no problems calling procedures that don't use parameters from PHP and do so frequently.

I stared with a very long procedure which ultimately updates some stuff and calls various other procedures, it all worked perfectly from a query browser. I have broken it down to just this very simple step which breaks.

CREATE PROCEDURE `backlog`.`DB_HISTORY` (
IN myTABLE varchar(30),
IN myPROPERTY varchar(30),
IN myVALUE TEXT,
IN myKEY varchar(30)
)
MODIFIES SQL DATA
BEGIN
SET @R = NULL;
SET @myQuery = CONCAT("SELECT ",myPROPERTY," INTO @R FROM backlog.",myTABLE," WHERE ROW_IDENTIFIER"='",myKEY,"';");
PREPARE stmt FROM @myQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;

mySQL version 5.0.22 and I can't easily upgrade.

Any help very much appreciated as this is depressing me big time :-(
Thanks,
Steve

Options: ReplyQuote


Subject
Written By
Posted
Stored procedure works from Query browser but not when called from PHP
October 16, 2009 01:57PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.