Creating Stored Procedure from PHP

Creating Stored Procedure from PHP

am 17.01.2006 10:53:08 von Tim Mickelson

Hello

I have this problem, making a script file to use the "mysql"
application, I can create the file my_sp.sql, and launch as:
mysql -u pippo -ppluto test < my_sp.sql
This works fine, the problem is doing this from php, with the
mysql_query, how should I change the delimiter, this is not
a standard SQL query, so I get an error, I have really tried all
different possiblities to fix this, please help me someone.

Tim

my_sp.sql:

DELIMITER $$
CREATE PROCEDURE `test`.`users`(name VARCHAR(30))
BEGIN
DECLARE comment VARCHAR(100);
SET comment = CONCAT(name,' is added');
INSERT INTO users (id,name,comment) VALUES (NULL,name,comment);
END
$$
DELIMITER ;

This works fine, my problem is, how can I create this from PHP, using
the mysql libraries?

$con = mysql_connect('localhost','pippo','pluto');
mysql_select_db('test');

// 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 ;";

mysql_query($query);
mysql_close($con);
?>

Re: Creating Stored Procedure from PHP

am 17.01.2006 11:26:25 von Shion

Tim Mickelson wrote:
> Hello
>
> I have this problem, making a script file to use the "mysql"
> application, I can create the file my_sp.sql, and launch as:
> mysql -u pippo -ppluto test < my_sp.sql
> This works fine, the problem is doing this from php, with the
> mysql_query, how should I change the delimiter, this is not
> a standard SQL query, so I get an error, I have really tried all
> different possiblities to fix this, please help me someone.

I have never done this from php, so I don't know if this will work or not.

> This works fine, my problem is, how can I create this from PHP,
> using the mysql libraries?
>
> > $con = mysql_connect('localhost','pippo','pluto');
> mysql_select_db('test');
>
> // 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 ;";
>
> mysql_query($query);
> mysql_close($con);
> ?>

This creates a one line string

$query="DELIMITER $$ CREATE PROCEDURE test.users(name VARCHAR(30))
BEGINDECLARE comment VARCHAR(100);SET comment = CONCAT(name,' is
added');INSERT INTO users (id,name,comment) VALUES
(NULL,name,comment);END$$DELIMITER ;"

and this isn't what you want, think the '$$' has to be the first and only
thing on a line to the mysql server, try this instead

$con = mysql_connect('localhost','pippo','pluto');
mysql_select_db('test');

// HOW SHOULD THIS QUERY BE??

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

mysql_query($query);
mysql_close($con);
?>



//Aho

Re: Creating Stored Procedure from PHP

am 17.01.2006 12:11:09 von Tim Mickelson

Hello everyone, I resolved it myself in the end, from the documentation
of mysql_query:
"The query string should not end with a semicolon"
I never knew this, so I have always ended my queries with a semicolon,
knowing this, it was sufficiento to do:

$con = mysql_connect('localhost','pippo','pluto');
mysql_select_db('test');

// THIS WORKS :-)
$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";

mysql_query($query);
mysql_close($con);
?>

Re: Creating Stored Procedure from PHP

am 23.02.2006 00:51:28 von Jim Michaels

"Tim Mickelson" wrote in message
news:th4zf.35560$av6.8679@tornado.fastwebnet.it...
> Hello everyone, I resolved it myself in the end, from the documentation of
> mysql_query:
> "The query string should not end with a semicolon"
> I never knew this, so I have always ended my queries with a semicolon,
> knowing this, it was sufficiento to do:
>
> > $con = mysql_connect('localhost','pippo','pluto');
> mysql_select_db('test');
>
> // THIS WORKS :-)
> $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";
>
> mysql_query($query);
> mysql_close($con);
> ?>

You are forgetting Aho's point. you are concatenating strings without
whitespace between them. so you end up with BEGINDECLARE as one word.
you could at least put a \n on those lines or a space.