issues creating function - MySQL

[Ted]

I created two functions using MySQL Administrator. I copied the entire
SQL code from the editor and pasted it to a text editor. Then I told
MySQL Admijnistrator to execute the SQL.

The exact code is pasted below.

CREATE FUNCTION `alert_db`.`my_max_market_date` () RETURNS DATE
BEGIN
DECLARE mmmd DATE;
SELECT max(h_market_date) into mmmd FROM holdings_tmp;
RETURN mmmd;
END;

CREATE FUNCTION `my_market_date_less_a_week`() RETURNS DATE
BEGIN
DECLARE mmmd DATE;
DECLARE mmmdlaw DATE;
SELECT max(h_market_date) into mmmd FROM holdings_tmp;
SET mmmdlaw := DATE_SUB(mmmd,INTERVAL 7 DAY);
return mmmdlaw;
END;

We KNOW this code is precisely what MySQL Administrator showed in its
SQL editor.

I used the following statement to test whether or not this worked.

SELECT `my_max_market_date` (),`my_market_date_less_a_week` ();

It worked exactly as expected!

The problem is that when I try to put this into a script to be executed
from a command line such as the following, it fails!

mysql -u root -p --database=alert_db < Date_Calculation.sql

The error I get is:

ERROR 1064 (42000) at line 1: 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 'DATE' at line 3

I do not understand why code that works fine within MySQL
Administrator's SQL editor should fail when submitting it through the
mysql client on the command line.

Yes, for our other development activities, it is enough that we have
these functions present and working. However, I have a suite of small
scripts that create the database de novo, and thus need to add code to
create these functions to my scripts.

Any ideas?

Thanks

Ted

[Axel Schwenke]

"Ted" <r.ted.byersrogers.com> wrote:

> CREATE FUNCTION `alert_db`.`my_max_market_date` () RETURNS DATE
> BEGIN
> DECLARE mmmd DATE;
> SELECT max(h_market_date) into mmmd FROM holdings_tmp;
> RETURN mmmd;
> END;

> The problem is that when I try to put this into a script to be executed
> from a command line such as the following, it fails!
>
> mysql -u root -p --database=alert_db < Date_Calculation.sql
>
> The error I get is:
>
> ERROR 1064 (42000) at line 1: 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 'DATE' at line 3

This is easy. The MySQL command line client *interprets* lines you
type. Especially it scans for the DELIMITER to break your lines into
SQL statements. The default delimiter is ';'. So your SQL statement
ends with the 'DATE' in line 3. It is thus incomplete and yields a
syntax error.

The solution is easy: specify a temporary new delimiter for mysql

mysql> DELIMITER //
mysql> CREATE FUNCTION my_max_market_date () RETURNS DATE
-> BEGIN
-> DECLARE mmmd DATE;
-> SELECT max(h_market_date) into mmmd FROM holdings_tmp;
-> RETURN mmmd;
-> END//
Query OK, 0 rows affected (1,95 sec)

mysql> DELIMITER ;

This trick is also shown in the manual:
[url]http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html[/url]


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
MySQL User Forums: [url]http://forums.mysql.com/[/url]

[Ted]

Thanks Axel

That was it.

Thanks

Ted