I may be wrong, but I think that MariaDB has a strange characteristic: it has many good features, some of which were not implemented intentionally. Well, this sentence is weird too, I know. But I have some examples: I’m not sure that the
SEQUENCE engine was designed to generate a sequence of dates and/or times; but it can. And I believe that the
CONNECT author had no idea that someone would have used his engine to create cursors for
CALL statements; but I do.
Now I have a new example. MariaDB 10.1 supports compound statements out of stored procedures. Which means that you can write
WHILE in your install.sql files to create your databases in a dynamic way. This is done via the
BEGIN NOT ATOMIC construct.
I played with this feature, like I usually do with new features. And what I’ve found out is amazing for me:
BEGIN NOT ATOMIC allows us to nest prepared statements!
Uh, wait… maybe what I’ve just written sounds weird to you. Maybe you’re thinking: “prepared statements can be nested since they were first implemented!”. Which is only true in the documentation. The docs doesn’t lie of course, but it doesn’t work out there, in the real world’s complexity.
DEALLOCATE PREPARE statements cannot be prepared, and this limitation can be very frustrating if you try to write a reusable (perhaps public) stored procedure library.
I tried to explain the reason in this post, but it was becoming way too long, so I had to delete that boring explanation. I’ll just mention an example. You can write a procedure that prepares and executes a statement; but the prepared statement cannot call the procedure itself, recursively. Why? Because you cannot reuse its name, and annot dynamically generate a name for the new prepared statement. If this explanation is too short, just code and you’ll find out.
BEGIN NOT ATOMIC possibly fix this problem? Well, for a reason that’s far beyond me, the following blocks can be prepared and executed:
BEGIN NOT ATOMIC PREPARE ... ; END;
BEGIN NOT ATOMIC EXECUTE ... ; END;
BEGIN NOT ATOMIC DEALLOCATE PREPARE ... ; END;
Now you may be thinking that this feature is totally useless. But it isn’t. Thanks to this change, I’ve written a procedure that:
- Executes a specified SQL string.
- Autogenerates a “free” name for that statement, or uses an id passed by the user.
- Returns the autogenerated id, so you can reuse it, or deallocate the statement.
Writing this procedure has been a bit annoying, because after all it uses a dirty trick. But now the procedure is written, and the dirty trick is encapsulated in it. You can use it as if it was a native feature, and forget the trick. Here’s the code:
CREATE DATABASE IF NOT EXISTS _; CREATE TABLE IF NOT EXISTS _.prepared_statement ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ) ENGINE = MEMORY ; CREATE PROCEDURE _.exec(IN p_sql TEXT, INOUT p_stmt_id INTEGER UNSIGNED) BEGIN IF p_stmt_id IS NULL THEN INSERT INTO _.prepared_statement (id) VALUES (DEFAULT); SET p_stmt_id := LAST_INSERT_ID(); END IF; SET @_SQL_exec := CONCAT( 'BEGIN NOT ATOMIC PREPARE stmt_dyn_', p_stmt_id, ' ' , 'FROM ', QUOTE(p_sql), IF(RIGHT(p_sql, 1) = ';', ' ', '; ') , 'END;' ); PREPARE _stmt_exec FROM @_SQL_exec; EXECUTE _stmt_exec; SET @_SQL_exec := CONCAT( 'BEGIN NOT ATOMIC EXECUTE stmt_dyn_', p_stmt_id, '; END;' ); PREPARE _stmt_exec FROM @_SQL_exec; EXECUTE _stmt_exec; DEALLOCATE PREPARE _stmt_exec; SET @_SQL_exec := NULL; END;
How do I use it? Very simple:
MariaDB [_]> -- redundant: @id is not set, so it's NULL MariaDB [_]> SET @id := NULL; Query OK, 0 rows affected (0.00 sec) MariaDB [_]> CALL _.exec('SELECT 42 AS answer', @id); +--------+ | answer | +--------+ | 42 | +--------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) MariaDB [_]> -- reuse @id MariaDB [_]> CALL _.exec('SHOW SCHEMAS LIKE \'info%\'', @id); +--------------------+ | Database (info%) | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
I am writing a general purpose stored procedure library, that should make stored procedures developing more friendly. It will include this procedure, as well as a procedure for deallocating a specified statement, or all statements you prepared. As soon as the code is interesting and tested, I’ll make it public.