Re: creating a function in mysql

2007-04-13 Thread Peter Brawley 
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_3569846_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
mysqlSET avg = (n1+n2+n3*2+n4*4)/8;
ERROR 1193 (HY000): Unknown system variable 'avg'
mysqlRETURN 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

RE: creating a function in mysql

2007-04-13 Thread Andrey Dmitriev 
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
   mysqlSET avg = (n1+n2+n3*2+n4*4)/8;
   ERROR 1193 (HY000): Unknown system variable 'avg'
   mysqlRETURN 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

Re: creating a function in mysql

2007-04-13 Thread 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
   mysqlSET avg = (n1+n2+n3*2+n4*4)/8;
   ERROR 1193 (HY000): Unknown system variable 'avg'
   mysqlRETURN 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

Re: creating a function in mysql

2007-04-13 Thread Baron Schwartz 

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.


Eliminate the spaces between the function name and the parens and it'll be 
happier.  There's a configuration setting that controls this if you really want 
to tweak it, but I always just eliminate the parens.


Baron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]