Professional Web Applications Themes

issues creating function - MySQL

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 ...

  1. #1

    Default issues creating function

    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

    Ted Guest

  2. #2

    Default Re: issues creating function

    "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]
    Axel Schwenke Guest

  3. #3

    Default Re: issues creating function


    Thanks Axel

    That was it.

    Thanks

    Ted

    Ted Guest

Similar Threads

  1. Issues creating cookies
    By mikey999 in forum Macromedia ColdFusion
    Replies: 1
    Last Post: March 9th, 06:24 PM
  2. #25038 [NEW]: call_user_func issues warning if function throws exception
    By tater at potatoe dot com in forum PHP Development
    Replies: 0
    Last Post: August 11th, 11:34 AM
  3. PHP issues when creating site cache
    By tonomud webforumsuser@macromedia.com in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 15th, 02:55 PM
  4. Help with creating function
    By Hans Hagen in forum Oracle Server
    Replies: 1
    Last Post: December 17th, 05:53 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139