Re: creating a function in mysql

[Peter Brawley]

>Does MySql support using it's own functions within this code?DROP 
FUNCTION IF EXISTS testconcat;

Works for me (though CONCAT maddenly trims its args):
DELIMITER |
CREATE FUNCTION testconcat( str CHAR(20) )
RETURNS CHAR(50)
DETERMINISTIC
BEGIN
 SET str = CONCAT( str, ' ', 'suffix' );
 RETURN str;
END;
|
DELIMITER ;
select testconcat('this has a ' ) as test;
+-------------------+
| test              |
+-------------------+
| this has a suffix |
+-------------------+
PB

-----

Andrey Dmitriev wrote:
Thanks, that works..
 
Next question
 
Does MySql support using it's own functions within this code?
 
E.g. I tried
 
set str = select concat (str,'hello');
set str = concat (str, 'hello');
 
And it didn't seem to like either.
 
Thanks,
-andrey

  _____  

From: Peter Brawley [mailto:[EMAIL PROTECTED] 
Sent: Friday, April 13, 2007 5:37 PM
To: Andrey Dmitriev
Cc: [EMAIL PROTECTED]
Subject: Re: creating a function in mysql



  
I've determined that I don't seem to 
be able to create functions at all.
    
Declare a delimiter, and lose the quote around the function name, eg

DROP FUNCTION IF EXISTS weighted_average;
DELIMITER |
CREATE FUNCTION weighted_average(n1 INT, n2 INT, n3 INT, n4 INT) RETURNS
int(11)
DETERMINISTIC
BEGIN
  DECLARE avg INT;
  SET avg = (n1+n2+n3*2+n4*4)/8;
  RETURN avg;
END;
|
DELIMITER ;

PB

-----

Andrey Dmitriev wrote: 

   All,
   
   I've been trying to create a function that will generate URLs so that
   I wouldn't have to wrote ugly SQL all the time. After no success,
   I've determined that I don't seem to be able to create functions at
   all. I am relatively new to mysql development (or management), so any
   advice is appreciated (coming from oracle world, btw)
   
   [EMAIL PROTECTED] ~]# rpm -qa|grep -i mysql
   MySQL-shared-standard-5.0.18-0.rhel4
   mysql-gui-tools-5.0r3-1rhel4
   mysqlclient10-3.23.58-4.RHEL4.1
   mysql-administrator-5.0r3-1rhel4
   MySQL-server-pro-5.0.18-0.rhel4
   MySQL-client-pro-5.0.18-0.rhel4
   
   following:
   http://www.databasejournal.com/features/mysql/article.php/10897_35698
   46_2
   
   getting
   Database changed
   mysql> CREATE FUNCTION 'WEIGHTED_AVERAGE'(n1 INT, n2 INT, n3 INT, n4
   INT)
       ->  RETURNS int(11)
       ->     DETERMINISTIC
       -> BEGIN
       ->    DECLARE avg INT;
   ERROR 1064 (42000): 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 ''WEIGHTED_AVERAGE'(n1 INT, n2 INT, n3 INT, n4
   INT)
    RETURNS int(11)
       DETERMI' at line 1
   mysql>    SET avg = (n1+n2+n3*2+n4*4)/8;
   ERROR 1193 (HY000): Unknown system variable 'avg'
   mysql>    RETURN avg;
   ERROR 1064 (42000): 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 'RETURN avg' at line 1
   mysql>   END
       ->
       -> ;
   ERROR 1064 (42000): 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 'END' at line 1
   mysql>
   
   
   
   
     
   
  _____  


   No virus found in this incoming message.
   Checked by AVG Free Edition.
   Version: 7.5.446 / Virus Database: 269.4.0/759 - Release Date:
   4/12/2007 7:58 PM
     



  
------------------------------------------------------------------------

No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 269.4.0/759 - Release Date: 4/12/2007 7:58 PM