To whom it may concern -

Today, I received an email from a user of the udf_init_error UDF (which resides in the lib_mysqludf_udf library). The purpose of this UDF is to generate an error condition, which can be used to abruptly terminate a trigger or stored procedure. As such it is a workaround for bug #11661. This is all described extensively in my now ancient article here.

The user wrote me because of a problem experienced in MySQL 5.5: ...calling

select …

Yesterday I delivered my presentation for the MySQL User Conference and Expo 2010: Optimizing MySQL Stored Routines. If you are interested in the slides, you can find them on both the MySQL conference site as well as on slideshare.net. Here's the abstract of my presentation so you can decide if this is interesting for you: MySQL stored routines (functions, procedures, triggers and events) can be useful. But many casually written stored routines are unnecessarily slow. The main reason is that MySQL does not apply even simple code optimizations to stored routine code. …

The latest MySQL 5.5 milestone release offers support for an ANSI/ISO standard feature called the SIGNAL syntax. You can use this syntax inside stored routines (including triggers) to raise an error condition which can be used to invoke specific error handling, or otherwise abort the stored routine. In addition, you can use the SIGNAL syntax to convey information about what went wrong, which may be used by the caller to handle the error.

I have written about MySQL data entry validation procedures in the past. At the time, MySQL did not support any proper means to raise an error condition inside a stored routine, and …

Quick tip - suppose you need the result set of a MySQL stored procedure in your Pentaho Data Integration (a.k.a. Kettle) Transformation, what do you do?

A Call DB Procedure sounds promising, but as it turns out, you can't use it to retrieve any result sets. Rather, this type of step is meant have an input stream from another source:

• 
  
• drive stored procedure execution presumably for some useful side-effect
  
• invoke a database stored function and obtain the scalar result
  

So, what can we do? The answer is simpler than might be expected.

Just use an ordinary …

Let's see if you can solve this little puzzle...

Consider this stored procedure:

-- finds the first slash and exits
create procedure p_find_slash(p_text text)
begin
    declare v_index int default 1;
    declare v_length int default character_length(p_text);
    declare v_char char(1);

    _main_loop: while v_index <= v_length do                       -- loop over all characters

        set v_char := substring(p_text, v_index, 1);               -- grab the current character
        case v_char
            when v_char = '/' then                                 -- found a slash!                
                select concat('A slash at ', v_index) message;     -- report it
                leave _main_loop;                                  -- and then stop
            else                                                   
 …