Posted by J.O. Aho on 10/14/85 11:37

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

[Back to original message]