creating mysql functions using php

Hello,

Is it possible to create a mysql function from php, or is it command line only?

$db = get_db();

$a = $db->query( "DELIMITER $$ " );
echo $db->error;
// You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'DELIMITER $$' at line 1
$a = $db->query( "
DROP FUNCTION IF EXISTS `anti_space`$$
CREATE FUNCTION `anti_space` (
inString VARCHAR(1000),
replaceThis VARCHAR(1000),
replaceWith VARCHAR(1000)
)
// You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near '$$ CREATE FUNCTION `anti_space` ( inString VARCHAR(1000),
replaceThis VARCHA' at line 1

I get errors for these commands, can anybody shed any light on this please?

Many thanks,


Andy

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: creating mysql functions using php

Andrew Martin wrote:
> Hello,
>
> Is it possible to create a mysql function from php, or is it command line only?
>
> $db = get_db();
>
> $a = $db->query( "DELIMITER $$ " );
> echo $db->error;
> // You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near 'DELIMITER $$' at line 1
> $a = $db->query( "
> DROP FUNCTION IF EXISTS `anti_space`$$
> CREATE FUNCTION `anti_space` (
> inString VARCHAR(1000),
> replaceThis VARCHAR(1000),
> replaceWith VARCHAR(1000)
> )
> // You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near '$$ CREATE FUNCTION `anti_space` ( inString VARCHAR(1000),
> replaceThis VARCHA' at line 1

What's your query method look like?

What happens if you try using

mysql_query($query);

instead of $db->query($query) ?

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: creating mysql functions using php

Hello,

Here's the procedural code. Surely it's possible to create a MySQL
function through the API?

$link = mysql_connect('localhost', 'root', 'pass');
mysql_select_db( 'test' );
$ret = mysql_query( 'DELIMITER $$ DROP FUNCTION IF EXISTS `anti_space`$$
CREATE FUNCTION `anti_space` (
inString VARCHAR(1000),
replaceThis VARCHAR(1000),
replaceWith VARCHAR(1000)
)
RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN
DECLARE _outString VARCHAR(1000) DEFAULT TRIM(inString);
DECLARE _length INT DEFAULT LENGTH(_outString);
DECLARE _doneLoop BOOLEAN DEFAULT FALSE;
DECLARE _lengthNext INT DEFAULT 0;

IF ( _length != 0 ) THEN

WHILE (! _doneLoop AND _length != 0 ) DO

SET _outString = REPLACE( _outString, replaceThis, replaceWith );
SET _lengthNext = LENGTH(_outString);
SET _doneLoop = (_lengthNext = _length);
SET _length = _lengthNext;

END WHILE;

END IF;

RETURN _outString;

END$$

DELIMITER ;', $link );

if( !$ret ) echo mysql_error($link);

exit;

output:

You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'DELIMITER $$ DROP FUNCTION IF EXISTS `anti_space`$$ CREATE
FUNCTION `anti_spac' at line 1

I've tried putting the delimiter call in it's own query() call, but no
luck so far.

Thanks,


Andy


2008/10/8 Chris :
> Andrew Martin wrote:
>>
>> Hello,
>>
>> Is it possible to create a mysql function from php, or is it command line
>> only?
>>
>> $db = get_db();
>>
>> $a = $db->query( "DELIMITER $$ " );
>> echo $db->error;
>> // You have an error in your SQL syntax; check the manual that
>> corresponds to your MySQL server version for the right syntax to use
>> near 'DELIMITER $$' at line 1
>> $a = $db->query( "
>> DROP FUNCTION IF EXISTS `anti_space`$$
>> CREATE FUNCTION `anti_space` (
>> inString VARCHAR(1000),
>> replaceThis VARCHAR(1000),
>> replaceWith VARCHAR(1000)
>> )
>> // You have an error in your SQL syntax; check the manual that
>> corresponds to your MySQL server version for the right syntax to use
>> near '$$ CREATE FUNCTION `anti_space` ( inString VARCHAR(1000),
>> replaceThis VARCHA' at line 1
>
> What's your query method look like?
>
> What happens if you try using
>
> mysql_query($query);
>
> instead of $db->query($query) ?
>
> --
> Postgresql & php tutorials
> http://www.designmagick.com/
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: creating mysql functions using php

Right, problem's obvious really:

There's never any need to use "delimiter" in an API call, as
delimiters aren't used - separate calls are made instead. So the
following code works just fine:

$link = mysql_connect('localhost', 'root', 'pass');
mysql_select_db( 'test' );
$ret = mysql_query( 'DROP FUNCTION IF EXISTS `anti_space` ');
if( !$ret ) echo mysql_error($link);
$ret = mysql_query( 'CREATE FUNCTION `anti_space` (
inString VARCHAR(1000),
replaceThis VARCHAR(1000),
replaceWith VARCHAR(1000)
)
RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN
DECLARE _outString VARCHAR(1000) DEFAULT TRIM(inString);
DECLARE _length INT DEFAULT LENGTH(_outString);
DECLARE _doneLoop BOOLEAN DEFAULT FALSE;
DECLARE _lengthNext INT DEFAULT 0;

IF ( _length != 0 ) THEN

WHILE (! _doneLoop AND _length != 0 ) DO

SET _outString = REPLACE( _outString, replaceThis, replaceWith );
SET _lengthNext = LENGTH(_outString);
SET _doneLoop = (_lengthNext = _length);
SET _length = _lengthNext;

END WHILE;

END IF;

RETURN _outString;

END', $link );

D'oh. Hope that helps someone.


Andy

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php