MySQL Forums
Forum List  »  PHP

Creating Stored Procedure from PHP script
Posted by: Franko I:
Date: October 07, 2009 01:57PM

Hello,

I'm using stored procedures for some time, it is a great way to improve the work, get better performance and so on.
On my server, things are working fine, using terminal, or mysql-query-browser or even phpmyadmin.
Now, on the host have a lot of problems putting stored procedure.
Within phpmyadmin 2.11. succeeded to put stored procedure, but not the restore from dump (also created by phpmyadmin), but putting one by one stored procedure.
On another host can't put anyhow the same procedures (phpmyadmin 2.6. maybe old version, maybe permissions are not set up properly.)
So, I'm trying to create st.proc. from the PHP code, and tried something like this:

// HOW SHOULD THIS QUERY BE??
$query = "DELIMITER $$ ";
$query.= "CREATE PROCEDURE test.users(name VARCHAR(30)) ";
$query.= "BEGIN";
$query.= "DECLARE comment VARCHAR(100);";
$query.= "SET comment = CONCAT(name,' is added');";
$query.= "INSERT INTO users (id,name,comment) VALUES
(NULL,name,comment);";
$query.= "END";
$query.= "$$";
$query.= "DELIMITER ;";

(ok, this example is being found on the web and isn't perfect because man has no whitespace, i.e. BEGINDECLARE and so on shall be split instead.
I've tried to put this in the single query like this:

$query = "
DELIMITER $$
DROP PROCEDURE IF EXISTS test3;

CREATE PROCEDURE test3 ( )
BEGIN
SELECT * FROM `ispit` ;
END $$
DELIMITER ;
";

I've tried many variations, with and without delimiters, think that ; confuses PHP within the query ?
Maybe I'm wrong.
Any help would be appreciated.
Frank



Edited 2 time(s). Last edit at 10/07/2009 03:41PM by Franko I:.

Options: ReplyQuote


Subject
Written By
Posted
Creating Stored Procedure from PHP script
October 07, 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.