Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

phpmyadmin fehler rechte unerlaubte mischung, WWWXXXApc, lilo trying to map files from unnamed device 0x0000", Wwwxxxapc, CAM::PDF::Renderer::TextF Fragen, adodb.recordset error '800a0cc1', php save mails as eml, WWWXXXAPC, sqlexpress database file auto-creation error, sqlexpress database file auto-creation error

Links

XODOX
Impressum
Datenschutz

#1: Creating Stored Procedure from PHP

Posted on 2006-01-17 10:53:08 by 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);
?>

Report this message

#2: Re: Creating Stored Procedure from PHP

Posted on 2006-01-17 11:26:25 by 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

Report this message

#3: Re: Creating Stored Procedure from PHP

Posted on 2006-01-17 12:11:09 by 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);
?>

Report this message

#4: Re: Creating Stored Procedure from PHP

Posted on 2006-02-23 00:51:28 by Jim Michaels

"Tim Mickelson" <tim_mickelson@hotmail.com> 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.

Report this message