MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Overloading Procedures

with 10 comments

A student asked, “Can you successfully overload MySQL stored procedures, like PL/SQL does in stored packages?” I said, “MySQL doesn’t formally support overloading stored procedures, and doesn’t support packages like Oracle 11g. You can, however, mimic (or fake) overloading with prepared statements. It requires a careful combination of stored procedures and session variables.”

Unfortunately, they asked for an example. So I wrote this set of code. It uses queries as the dynamic statements to make it simple to test but you can substitute INSERT, UPDATE, or DELETE statements. I didn’t provide type validation in the example, which would be required for dates or date-timestamps.

It’s presented in steps with test programs at each level. If you see an opportunity to improve on any of the example components, leave a comment. As to whether I’d implement this in production code, the answer is no but I believe all possibilities should at least be explored.

Step #1

The first step requires defining a stored procedure that sets a session variable. It’s fairly straight forward, but remember to change those DELIMITER values when testing. (As a note, you don’t require a semicolon after the END statement of a stored function or procedure in MySQL. You only need the redefined DELIMITER, which is $$ in these examples.)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DELIMITER $$
 
-- Drop the procedure if it exists.
DROP PROCEDURE IF EXISTS set_session_var$$
 
-- Create the procedure.
CREATE PROCEDURE set_session_var
( pv_session_name   VARCHAR(32767)
, pv_session_value  VARCHAR(32767))
CONTAINS SQL
BEGIN
 
  /* Insert statement with auto commit enabled. */
  SET @SQL := concat('SET',' ','@',pv_session_name,' := ','?');
  SELECT @SQL AS "SQL String";
  PREPARE stmt FROM @SQL;
  SET @sv_session_value := pv_session_value;
  EXECUTE stmt USING @sv_session_value;
  DEALLOCATE PREPARE stmt;
 
END;
$$

The test case for the set_session_var is:

CALL set_session_var('sv_filter1','One');
CALL set_session_var('sv_filter2','Two');
SELECT @sv_filter1, @sv_filter2;

Important note: If you call this from another stored program you can’t use the stmt variable name in the calling program’s scope.

Step #2

The second step exists because you can’t pass arrays in MySQL (a restriction that also exists for T-SQL in Microsoft SQL Server). You have to pass a varying list of parameters as a serialized string. This is often called flexible parameter passing, which many PHP programmers leverage outside the database (flexible parameter passing is covered in this PHP tutorial I wrote a while back).

The deserialize example does three things:

  • Takes a serialized set of parameters and deserializes the parameters into a set of name-value pairs, which are stored by their name as session level variables.
  • It records the number of name-value pairs written as session variables in a temporary table. The use of a temporary table is required because a MySQL stored function can’t hold a dynamic statement or a call to a procedure with a dynamic statement.
  • The script checks for the existence of the table during execution and truncates it when found. It creates the table when it isn’t found. While it would be ideal to use a temporary table, they’re not disclosed in the the information_schema and therefore more tedious to manage. Creating the table in the MEMORY database avoids fragmenting the InnoDB tablespace.

Here’s the working code:

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
CREATE PROCEDURE deserialize
( pv_param_list VARCHAR(32767))
CONTAINS SQL
BEGIN
  DECLARE lv_name     VARCHAR(9) DEFAULT 'sv_filter';
  DECLARE lv_length   INT;
  DECLARE lv_start    INT DEFAULT 1;
  DECLARE lv_end      INT DEFAULT 1;
  DECLARE lv_counter  INT DEFAULT 1;
 
  /* Skip when call parameter list is null or empty. */	
  IF NOT (ISNULL(pv_param_list) OR LENGTH(pv_param_list) = 0) THEN
 
    /* Read line by line on a line return character. */
    parse: WHILE NOT (lv_end = 0) DO
 
      /* Check for line returns. */
      SET lv_end := LOCATE(',',pv_param_list,lv_start);
 
      /* Check whether line return has been read. */
      IF NOT lv_end = 0 THEN  /* Reset the ending substring value. */
        SET lv_end := LOCATE(',',pv_param_list,lv_start);
        CALL set_session_var(CONCAT(lv_name,lv_counter),SUBSTR(pv_param_list,lv_start,lv_end - lv_start));
      ELSE  /* Print the last substring with a semicolon. */
        CALL set_session_var(CONCAT(lv_name,lv_counter),SUBSTR(pv_param_list,lv_start,LENGTH(pv_param_list)));
      END IF;
 
      /* Reset the beginning of the string. */
      SET lv_start := lv_end + 1;      
      SET lv_counter := lv_counter + 1;
 
    END WHILE parse;    
 
  END IF;
 
  /* Check for a temporary table that holds a control variable,
     create the table if it doesn't exist, and remove rows from
     the table. */
  IF EXISTS (SELECT   NULL
             FROM     information_schema.TABLES
             WHERE    TABLE_NAME = 'counter') THEN
    TRUNCATE TABLE counter;
  ELSE
    /* It would be ideal to use a temporary table here but then
       it's not recorded in the INFORMATION_SCHEMA and cleansing
       the temporary table is more tedious. */
    CREATE TABLE counter ( counter INT ) ENGINE=MEMORY;
  END IF;
 
  /* Insert the counter value for a list of parameters. */
  INSERT INTO counter VALUES ( lv_counter - 1 );  
 
END;
$$

Bill Karwin made a wonderful point about the overhead of looking up a table name and using a table rather than a temporary table. You can replace the logic above with a CONTINUE HANDLER and a temporary table. It should resolve the problems mentioned in the note but as mentioned earlier there are other solutions for production systems.

Bill also referenced a great post by Jay Pipes. It points out that there are serious problems scaling MySQL stored programs. This occurs because MySQL stored procedures are not compiled and stored in a global stored procedure cache, like Microsoft SQL Server or Oracle.

This deficit in MySQL certainly plays to the philosophy of developing everything in the application layer, suggested by Bill. At least, that is true when the application layer is co-resident on the server-tier as a Server Side Includes (SSI) library.

It does beg the question why Microsoft SQL Server and Oracle enable placing an efficient set of stored logic in the database server, effectively creating an application layer that can be split between an SSI library and the database server. After all, the server has been the most scalable component and the application layer can distribute to the middle-tier.

Today’s limitations on MySQL stored programs present a clear and compelling case toward their general avoidance and specific use for delivering a scalable solutions. Will that hold true beyond 2015 when licensing becomes completely proprietary to Oracle? I’d venture (a complete guess or hope on my part) a global stored procedure cache may be the future of MySQL procedures. It would certainly serve to distinguish MySQL from MariaDB and provide an incentive to remain on MySQL or to return from MariaDB to MySQL. Such a change would certainly make MySQL even more competitive against Microsoft SQL Server, which is the only rival purely relational database it competes against.

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
CREATE PROCEDURE deserialize
( pv_param_list VARCHAR(32767))
CONTAINS SQL
BEGIN
  DECLARE lv_name     VARCHAR(9) DEFAULT 'sv_filter';
  DECLARE lv_length   INT;
  DECLARE lv_start    INT DEFAULT 1;
  DECLARE lv_end      INT DEFAULT 1;
  DECLARE lv_counter  INT DEFAULT 1;
 
  DECLARE CONTINUE HANDLER FOR 1146
    BEGIN
      /* Create a temporary table. */
      CREATE TEMPORARY TABLE counter ( counter INT ) ENGINE=MEMORY;
    END;
 
  /* Skip when call parameter list is null or empty. */	
  IF NOT (ISNULL(pv_param_list) OR LENGTH(pv_param_list) = 0) THEN
 
    /* Read line by line on a line return character. */
    parse: WHILE NOT (lv_end = 0) DO
 
      /* Check for line returns. */
      SET lv_end := LOCATE(',',pv_param_list,lv_start);
 
      /* Check whether line return has been read. */
      IF NOT lv_end = 0 THEN  /* Reset the ending substring value. */
        SET lv_end := LOCATE(',',pv_param_list,lv_start);
        CALL set_session_var(CONCAT(lv_name,lv_counter),SUBSTR(pv_param_list,lv_start,lv_end - lv_start));
      ELSE  /* Print the last substring with a semicolon. */
        CALL set_session_var(CONCAT(lv_name,lv_counter),SUBSTR(pv_param_list,lv_start,LENGTH(pv_param_list)));
      END IF;
 
      /* Reset the beginning of the string. */
      SET lv_start := lv_end + 1;      
      SET lv_counter := lv_counter + 1;
 
    END WHILE parse;    
 
  END IF;
 
  /* Truncate existing table. */
  TRUNCATE TABLE counter;
 
  /* Insert the counter value for a list of parameters. */
  INSERT INTO counter VALUES ( lv_counter - 1 );  
 
END;
$$

The test program for deserialize follows below. If you’re curious, those are the Italian version of one through four. ;-) I’ve a partiality for Italian after living in Italy for two years.

CALL deserialize('Uno,Due,Tre,Quattro');
SELECT counter AS "Parameter #" FROM counter;
SELECT @sv_filter1, @sv_filter2, @sv_filter3, @sv_filter4;

You can also eliminate the temporary table completely by replacing the counter with a @sv_counter session variable, as mentioned by Bill in his comment. The following is the version of deserialize uses a session variable.

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
CREATE PROCEDURE deserialize
( pv_param_list VARCHAR(32767))
CONTAINS SQL
BEGIN
  DECLARE lv_name     VARCHAR(9) DEFAULT 'sv_filter';
  DECLARE lv_length   INT;
  DECLARE lv_start    INT DEFAULT 1;
  DECLARE lv_end      INT DEFAULT 1;
 
  /* Set a session variable to enable a calling scope to
     read it without a function return. */
  SET @sv_counter := 1;
 
  /* Skip when call parameter list is null or empty. */	
  IF NOT (ISNULL(pv_param_list) OR LENGTH(pv_param_list) = 0) THEN
 
    /* Read line by line on a line return character. */
    parse: WHILE NOT (lv_end = 0) DO
 
      /* Check for line returns. */
      SET lv_end := LOCATE(',',pv_param_list,lv_start);
 
      /* Check whether line return has been read. */
      IF NOT lv_end = 0 THEN  /* Reset the ending substring value. */
        SET lv_end := LOCATE(',',pv_param_list,lv_start);
        CALL set_session_var(CONCAT(lv_name,@sv_counter),SUBSTR(pv_param_list,lv_start,lv_end - lv_start));
      ELSE  /* Print the last substring with a semicolon. */
        CALL set_session_var(CONCAT(lv_name,@sv_counter),SUBSTR(pv_param_list,lv_start,LENGTH(pv_param_list)));
      END IF;
 
      /* Reset the beginning of the string. */
      SET lv_start := lv_end + 1;      
      SET @sv_counter := @sv_counter + 1;
 
    END WHILE parse;    
 
  END IF;
 
  /* Reduce by one for 1-based numbering of name elements. */
  SET @sv_counter := @sv_counter - 1;
 
END;
$$

The test case changes for this version because there isn’t a table involved. You would use the following:

CALL deserialize('Uno,Due,Tre,Quattro');
SELECT @sv_filter1, @sv_filter2, @sv_filter3, @sv_filter4;

Like the earlier procedure, there’s no exception handling but it wouldn’t be a bad idea to add it. Especially, if you’re using the new SIGNAL feature of MySQL 5.6, which is now backported to MySQL 5.5.

Step #3

The last step creates a store procedure that takes two parameters, a parameterized statement and a serialized list of parameters. This means you can build any statement on the fly as required. The example uses queries simply because they’re the easiest to demonstrate the process.

You should note that the prepared statement is dynamic_stmt not stmt because stmt is used inside the set_session_var procedure. That means using stmt in the calling scope program would impact the called scope program because they use the same identifier (more or less a namespace scope issue).

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
CREATE PROCEDURE prepared_dml
( pv_query   VARCHAR(32767)
, pv_filter  VARCHAR(32767))
CONTAINS SQL
BEGIN
 
  /* Declare a local variable for the SQL statement. */
  DECLARE dynamic_stmt  VARCHAR(32767);
  DECLARE lv_counter    INT DEFAULT 0;
 
  /* Cleanup the message passing table when a case is not found. */
  DECLARE EXIT HANDLER FOR 1339
    BEGIN
      /* Step #5: */
      DEALLOCATE PREPARE dynamic_stmt;
 
      /* Cleanup the temporary table that exchanges data between
         procedures. */
      DROP TABLE IF EXISTS counter;
    END;
 
  /* Step #1:
     ========
     Set a session variable with two parameter markers. */
  SET @SQL := pv_query;
 
  /* Verify query is not empty. */
  IF NOT ISNULL(@SQL) THEN
 
    /* Step #2:
       ========
       Dynamically allocated and run statement. */
    PREPARE dynamic_stmt FROM @SQL;
 
    /* Step #3:
       ========
       Assign the formal parameters to session variables
       because prepared statements require them. */
    CALL deserialize(pv_filter);
 
    /* Secure the parameter count from a temporary table that
       exchanges data between procedures. */
    SELECT counter INTO lv_counter FROM counter;
 
    /* Step #4:
       ========
       Choose the appropriate overloaded prepared statement. */
    CASE
      WHEN lv_counter = 0 THEN
        EXECUTE dynamic_stmt;      
      WHEN lv_counter = 1 THEN
        EXECUTE dynamic_stmt USING @sv_filter1;
      WHEN lv_counter = 2 THEN
        EXECUTE dynamic_stmt USING @sv_filter1,@sv_filter2;
    END CASE;
 
    /* Step #5: */
    DEALLOCATE PREPARE dynamic_stmt;
 
    /* Cleanup the temporary table that exchanges data between
       procedures. */
    DROP TABLE IF EXISTS counter; 
 
  END IF;
 
END;
$$

If you’re using the deserialize procedure that uses a temporary table, you should make the following changes prepared_dml procedure on line 19 and 62. They modify the DROP TABLE statement to a DROP TEMPORARY TABLE statement. Though the change isn’t technically required because a DROP TABLE works against temporary tables as well as ordinary tables, it generally clearer to those new to MySQL syntax.

17
18
19
    /* Cleanup the temporary table that exchanges data between
       procedures. */
    DROP TEMPORARY TABLE IF EXISTS counter;

The line 62 change:

60
61
62
    /* Cleanup the temporary table that exchanges data between
       procedures. */
    DROP TEMPORARY TABLE IF EXISTS counter;

It would be much nicer to avoid the CASE statement or an if-else-if block but prepared statements are limited on what SQL statements they support. For example, you can create or drop stored functions or procedures, and you can’t dispatch a dynamic statement as a dynamic statement. That means you can’t replace the CASE statement with something like this:

43
44
45
46
    SET @dsql := 'EXECUTE dynamic_stmt USING @sv_filter1, @sv_filter2';
    PREPARE preparsed_stmt FROM @dsql;
    EXECUTE preparsed_stmt;
    DEALLOCATE PREPARE preparsed_stmt;

Attempt that, and you’ll get the following message. Does that mean there’s hope it’ll become a feature and possibility? Or, that it isn’t recommended?

ERROR 1295 (HY000): This command IS NOT supported IN the prepared statement protocol yet

The session variable solution requires another version of the prepared_dml procedure, as shown below. As you can imagine, removing the table and replacing a session variable simplifies the prepared_dml stored procedure version.

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
CREATE PROCEDURE prepared_dml
( pv_query   VARCHAR(32767)
, pv_filter  VARCHAR(32767))
CONTAINS SQL
BEGIN
 
  /* Declare a local variable for the SQL statement. */
  DECLARE dynamic_stmt  VARCHAR(32767);
  DECLARE lv_counter    INT DEFAULT 0;
 
  /* Cleanup the message passing table when a case is not found. */
  DECLARE EXIT HANDLER FOR 1339
    BEGIN
      /* Step #5: */
      DEALLOCATE PREPARE dynamic_stmt;
    END;
 
  /* Step #1:
     ========
     Set a session variable with two parameter markers. */
  SET @SQL := pv_query;
 
  /* Verify query is not empty. */
  IF NOT ISNULL(@SQL) THEN
 
    /* Step #2:
       ========
       Dynamically allocated and run statement. */
    PREPARE dynamic_stmt FROM @SQL;
 
    /* Step #3:
       ========
       Assign the formal parameters to session variables
       because prepared statements require them. */
    CALL deserialize(pv_filter);
 
     /* Step #4:
       ========
       Choose the appropriate overloaded prepared statement. */
    CASE
      WHEN @sv_counter = 0 THEN
        EXECUTE dynamic_stmt;      
      WHEN @sv_counter = 1 THEN
        EXECUTE dynamic_stmt USING @sv_filter1;
      WHEN @sv_counter = 2 THEN
        EXECUTE dynamic_stmt USING @sv_filter1,@sv_filter2;
    END CASE;
 
    /* Step #5: */
    DEALLOCATE PREPARE dynamic_stmt;
 
  END IF;
 
END;
$$

The test programs check without a parameter and with one or two parameters, as you can see below.

SELECT 'Test Case #1 ...' AS "Statement";
SET @param1 := 'SELECT "Hello World"';
SET @param2 := '';
CALL prepared_dml(@param1,@param2);
 
SELECT 'Test Case #2 ...' AS "Statement";
SET @param1 := 'SELECT item_title FROM item i WHERE item_title REGEXP ?';
SET @param2 := '^.*war.*$';
CALL prepared_dml(@param1,@param2);
 
SELECT 'Test Case #3 ...' AS "Statement";
SET @param1 := 'SELECT common_lookup_type FROM common_lookup cl WHERE common_lookup_table REGEXP ? AND common_lookup_column REGEXP ?';
SET @param2 := 'item,item_type';
CALL prepared_dml(@param1,@param2);

As always, I hope this helps those writing MySQL Stored Procedures. If you’re new to stored programs, you can find a chapter on writing them in my Oracle Database 11g & MySQL 5.6 Developer Handbook or you can use Guy Harrison’s MySQL Stored Procedure Programming.

Written by maclochlainn

June 16th, 2012 at 7:36 pm

10 Responses to 'Overloading Procedures'

Subscribe to comments with RSS or TrackBack to 'Overloading Procedures'.

  1. This blog post shows some clever techniques that may shed light on how MySQL works, but I would emphasize that this solution is really not a good thing to do in a real project.

    Here are a few serious problems with your solution that I see:

    Querying the INFORMATION_SCHEMA.TABLES is very slow because it results in I/O when MySQL refreshes InnoDB table statistics (unless you set innodb_stats_on_metadata=0). See this blog post by Jervin Real.

    Because you use a non-temp table for `counter`, you can’t let concurrent sessions run this procedure. If you make lv_counter a session variable instead of a local variable in the proc, then you don’t need the `counter` table at all.

    Stored procs in MySQL are recompiled for every connection, which surprises many Oracle developers. This can become a serious performance problem in a MySQL environment. See this blog post by Jay Pipes.

    Passing a full SQL statement to be executed by a stored proc is an invitation to getting SQL injection vulnerabilities that are very difficult to track down. Parameters are for data values — not for code! This applies to procedure arguments as well as query parameters. For this reason alone, I would have discouraged you from presenting this blog at all, because some naive developer might think the technique of passing full SQL statements to a procedure is legitimate.

    To answer your student’s question: NO, MySQL does not support packages or overloadable procedures as Oracle does. If you need object-oriented design features, you should write that code in your application layer.

    Bill Karwin

    17 Jun 12 at 11:17 am

  2. Bill, I really appreciate your comment! I’ve updated the blog post with a different version of deserialize that doesn’t suffer from the overhead or concurrency problems. Naturally, the code can’t address the basic architecture problem of recompilation and lack of a global stored procedure cache.

    My take on writing about coding or providing examples is to exhibit how you solve problems with my golden rule of thumb being, “Just because you can, doesn’t mean you should.”

    maclochlainn

    17 Jun 12 at 1:53 pm

  3. What if you make this part dynamically written:

    EXECUTE dynamic_stmt USING @sv_filter1,@sv_filter2,@sv_filter3;

    Can you use NDS to make something like this dynamically inside a loop?

    -- number of parameters
    SET lv_param_count := 3;
    -- number to refer to the session variable
    SET lv_counter := 1;
    SET SQL := 'EXECUTE dynamic_stmt USING ';
    parse: WHILE NOT (lv_param_count = 0) DO
      SET SQL := CONCAT('@sv_filter',lv_counter);
      -- add a comma if it is not the last parameter
      IF(lv_param_count != 0) SQL := ','; END IF;
      SET lv_param_count := lv_param_count - 1;
      SET lv_counter := lv_counter + 1;
    END WHILE parse;

    Michael Stokes

    17 Jun 12 at 7:28 pm

  4. Michael, No, you can’t do that in MySQL with prepared statements. Yes, you can do that in Oracle Native Dynamic SQL (NDS) with an anonymous block. The restrictions in MySQL are two fold. Prepared statements only run SQL statements (at that a restricted set of SQL Statements). MySQL doesn’t support an anonymous (unnamed) block like Oracle’s PL/SQL.

    maclochlainn

    17 Jun 12 at 10:06 pm

  5. [...] very much enjoyed reading Overloading Procedures by Michael McLaughlin: good [...]

  6. Thanks for the post, I appreciate all of the comments made earlier. It gives me more insight into the subject material. It is a great theory exercise at the very least.

    I have enjoyed playing with the code, and seeing how modifying the code adjusts the results and the security vulnerabilities that have already been mentioned earlier.

    Jordan

    18 Jun 12 at 9:40 am

  7. Very good article, thank you very much.

    A contribution to your assessment.

    It would be possible to eliminate the USING and parse the query using a function like this:

    DELIMITER $$
     
    DROP FUNCTION IF EXISTS `get_prepare_sql`$$
     
    CREATE FUNCTION `get_prepare_sql`(dynamic_stmt VARCHAR(32767), lv_name VARCHAR(9), lv_counter INT)
    RETURNS VARCHAR(32767)
     
    BEGIN
     
    /* get total parameters */
    DECLARE total_lv_counter INT DEFAULT lv_counter;
     
    /* replace each parameter */
    parse: while (lv_counter > 0) do
    	SET dynamic_stmt := (SELECT INSERT(dynamic_stmt, instr(dynamic_stmt, '?'), 1, concat('@', lv_name, total_lv_counter - (lv_counter - 1))));
    	SET lv_counter := lv_counter - 1;
    END while parse;
     
    /* return value */
    RETURN dynamic_stmt;
     
    END$$
     
    DELIMITER ;

    SP being as follows:

    CREATE PROCEDURE prepared_dml
    ( pv_query   VARCHAR(32767)
    , pv_filter  VARCHAR(32767))
    CONTAINS SQL
    BEGIN
     
      /* Declare a local variable for the SQL statement. */
      DECLARE dynamic_stmt  VARCHAR(32767);
      DECLARE lv_counter    INT DEFAULT 0;
     
      /* Cleanup the message passing table when a case is not found. */
      DECLARE EXIT HANDLER FOR 1339
        BEGIN
          /* Step #5: */
          DEALLOCATE PREPARE dynamic_stmt;
        END;
     
      /* Step #1:
         ========
         Set a session variable with two parameter markers. */
      SET @SQL := pv_query;
     
      /* Verify query is not empty. */
      IF NOT ISNULL(@SQL) THEN
     
        /* Step #2:
           ========
           Assign the formal parameters to session variables
           because prepared statements require them. */
        CALL deserialize(pv_filter);
     
        /* Step #3:
           ========
           Dynamically allocated and run statement. */
        SELECT get_prepare_sql(dynamic_stmt, 'sv_filter', @sv_counter) INTO @SQL;
        PREPARE dynamic_stmt FROM @SQL;
     
         /* Step #4:
           ========
           Choose the appropriate overloaded prepared statement.
        CASE
          WHEN @sv_counter = 0 THEN
            EXECUTE dynamic_stmt;      
          WHEN @sv_counter = 1 THEN
            EXECUTE dynamic_stmt USING @sv_filter1;
          WHEN @sv_counter = 2 THEN
            EXECUTE dynamic_stmt USING @sv_filter1,@sv_filter2;
        END CASE;
        */
     
        EXECUTE dynamic_stmt;
     
        /* Step #5: */
        DEALLOCATE PREPARE dynamic_stmt;
     
      END IF;
     
    END;
    $$

    William

    18 Jun 12 at 11:16 am

  8. A small modification to the code to solve a bug in the SP `prepared_dml`:

    /* BEGIN BUG */
    SELECT get_prepare_sql(dynamic_stmt, 'sv_filter', @sv_counter) INTO @SQL;
    /* END BUG */
     
    SELECT get_prepare_sql(pv_query, 'sv_filter', @sv_counter) INTO @SQL;

    William

    19 Jun 12 at 4:24 am

  9. William, your solution doesn’t work if the SQL statement contains a literal ‘?’ character. E.g., inside a string literal, a delimited identifier, or a comment.

    INSTR() is not a parser.

    Bill Karwin

    21 Jun 12 at 1:37 pm

  10. Bill is right, the SQL statement should parse correctly within the function `get_prepare_sql`. My idea was to propose an alternative to eliminate the limitation of CASE. Thanks.

    William

    22 Jun 12 at 3:11 am

Leave a Reply