From: Kristian Nielsen
Date: August 4 2009 4:28pm
Subject: Re: DELIMETER problem in testing, and other testing stuff
List-Archive: http://lists.mysql.com/internals/37249
Message-Id: <87ocqv8rw9.fsf@knielsen-hq.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Joseph Lukas <jlukas79@stripped> writes:

> I am trying to test the command in using a stored function.  The log
> file stops at the DELIMITER every time and will not set however I have
> no issues with running the commands outside the test suite in normal
> MySQL. The content of the test is at the bottom of the e-mail.

> ####################################################################
> #   No 1 - Check works with FUNCTIONS
> #   No 2 - Test with DROP command
> ####################################################################
> --echo '# set initial variable value, make prepared statement
> SET SESSION myisam_sort_buffer_size=500000, myisam_repair_threads=1;
> --echo '# Pre-STATEMENT variable value'
> SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
> SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';

> DELIMITER :

From a quick glance at the existing tests, they all seem to put a semicolon at
the end, so you probably should too.

Also, you need to use a delimiter that does not appear in the code! The colon
appears below in "tax:"!

So try

    DELIMITER |;

> CREATE FUNCTION myProc (cost DECIMAL(10,2))
>    RETURNS DECIMAL(10,2)
>
>    SQL SECURITY DEFINER
>
>    tax: BEGIN
>        DECLARE order_tax DECIMAL(10,2);
>        SET order_tax = cost * .05;
>        RETURN order_tax;
>    END:
> DELIMITER ;

And here

    DELIMITER ;|

Hope this helps,

 - Kristian.