Showing entries 1 to 5
Displaying posts with tag: Database Stored Procedures (reset)
Re-implementing udf_init_error in MySQL 5.5 and up

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 …
[Read more]
MySQL Conference 2010 Presentation: Optimizing Stored Routines

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

[Read more]
Validating MySQL data entry with triggers: A quick look at the SIGNAL syntax

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 …

[Read more]
MySQL Stored Procedure Result Sets in Pentaho Data Integration

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 …

[Read more]
MySQL stored procedurs: ...the CASE that they gave me...

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)
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
[Read more]
Showing entries 1 to 5