MacLochlainns Weblog

In my database class, students write solutions as group exercises against the Oracle 11g XE database and then they port the solution individually to the MySQL 5.5 database. One of the students copied over a query like the one below to MySQL (a query used to track the expected number of row returns).

SELECT   COUNT(*)
FROM    (SELECT   DISTINCT
                  k.kingdom_id
         ,        kki.kingdom_name
         ,        kki.population
         FROM     kingdom_knight_import kki LEFT JOIN kingdom k
         ON       kki.kingdom_name = k.kingdom_name
         AND      kki.population = k.population);

It got an error they didn’t understand:

ERROR 1248 (42000): Every derived TABLE must have its own alias

Providing a dt query alias fixes the problem in MySQL for the following query. The fact that it was just an alias was a revelation to the student. That’s because Oracle databases don’t require aliases for inline views (what Oracle calls MySQL derived tables and Microsoft calls a Common Table Expression (CTE)).

SELECT   COUNT(*)
FROM    (SELECT   DISTINCT
                  k.kingdom_id
         ,        kki.kingdom_name
         ,        kki.population
         FROM     kingdom_knight_import kki LEFT JOIN kingdom k
         ON       kki.kingdom_name = k.kingdom_name
         AND      kki.population = k.population) dt;

MySQL requires that every derived table have its own alias. This make sense when you think about query optimization engines work, but that’s a story for a much longer post.

You can find more about inline views, derived tables, and common table expressions in this older post of mine. This post was promised so that future students can simply google and answer rather than ask me or a class tutor.

Somebody wanted to understand why you can backquote a single wildcard operator (that’s the underscore _ character) in MySQL, but can’t in Oracle. The answer is you can in Oracle when you know that you required an additional clause.

While I prefer using regular expression resolution, the LIKE operator is convenient. Here’s an example of backquoting an underscore in MySQL, where it looks for any string with an underscore anywhere in the string:

SELECT   common_lookup_type
FROM     common_lookup
WHERE    common_lookup_type LIKE '%\_%';

You can gain the same behavior in Oracle by appending the ESCAPE '\' clause, like this:

SELECT   common_lookup_type
FROM     common_lookup
WHERE    common_lookup_type LIKE '%\_%' ESCAPE '\';

The ESCAPE '\' clause is one of those Oracle details that often gets lost. It only works when the SQL*Plus ESCAPE parameter is set to OFF.

The SQL*Plus ESCAPE parameter default value is a backslash. and when the ESCAPE parameter is enabled a statement like this raises the following exception:

ERROR at line 3:
ORA-01425: escape CHARACTER must be CHARACTER string OF LENGTH 1

If you drop the ESCAPE '\' clause with the ESCAPE parameter enabled it will return all rows from the table not just those strings with an underscore in the string. Hope this helps if need to look for an underscore in a table.

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.

A question posed by a student: “Why are Oracle Native Dynamic SQL (NDS) USING clause parameters IN, IN OUT, or OUT when the RETURNING INTO clause manages output values?” It a great question, isn’t it? The followup question was also great, “How do you implement an example of NDS IN OUT parameters?”

The answer is two fold. First, you should use the USING clause for parameter list input values and the RETURNING INTO clause for return values whenever possible. Second, when it’s not possible you’re generally passing parameters into and out of an NDS PL/SQL anonymous block.

The basic prototype for passing and retrieving values from an NDS statement is:

EXECUTE IMMEDIATE sql_stmt
  USING { IN | IN OUT | OUT } local_variable [, ...]
  RETURNING INTO { IN OUT | OUT } local_variable [, ...];

A quick and hopefully fun example is this parody on Marvel’s The Avengers. The program creates an anonymous block with a super hero of Thor and super villain of Loki, then it uses a USING clause with IN OUT parameters to an anonymous block statement. That’s basically the trick to how you use IN OUT parameters in NDS statements.

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
68

-- Enable SERVEROUTPUT.
SET SERVEROUTPUT ON SIZE UNLIMITED
 
-- Declare an anonymous testing block.
DECLARE
 
  -- Declare two local variables.
  lv_super_hero     VARCHAR2(20) := 'Thor';
  lv_super_villain  VARCHAR2(20) := 'Loki';
 
  -- Declare a null statement variable.
  lv_stmt  VARCHAR2(32767);
 
  -- Declare a local procedure to parse the NDS block.
  PROCEDURE print_code_block (pv_block VARCHAR2) IS
    -- Declare local parsing variables.
    lv_length   INTEGER := 1;
    lv_start    INTEGER := 1;
    lv_end      INTEGER := 1;
  BEGIN
    -- Read line by line on a line return character.
    WHILE NOT (lv_end = 0) LOOP
      -- Check for line returns.
      lv_end := INSTR(lv_stmt,CHR(10),lv_start);
      -- Check whether line return has been read.
      IF NOT lv_end = 0 THEN     
        -- Reset the ending substring value and print substring.
        lv_end := INSTR(lv_stmt,CHR(10),lv_start);
        dbms_output.put_line('| '||SUBSTR(lv_stmt,lv_start,lv_end - lv_start));
      ELSE
        -- Print the last substring with a semicolon and exit the loop.      
        dbms_output.put_line('| '||SUBSTR(lv_stmt,lv_start,LENGTH(lv_stmt) - lv_start)||';');
      END IF;
      -- Reset the beginning of the string.
      lv_start := lv_end + 1;      
    END LOOP;    
  END print_code_block;
 
BEGIN
 
  -- Demonstrate good triumps over evil.
  dbms_output.put_line('The good '||lv_super_hero||' beats up the bad '||lv_super_villain||'!');
 
  -- Assign the anonymous block to the local statement variable.
  lv_stmt := 'DECLARE'||CHR(10)
          || '  lv_super_hero     VARCHAR2(20);'||CHR(10)
          || '  lv_super_villain  VARCHAR2(20);'||CHR(10)
          || 'BEGIN'||CHR(10)
          || '  lv_super_hero '||CHR(58)||'= :pv_super_hero;'||CHR(10)
          || '  lv_super_villain '||CHR(58)||'= :pv_super_villain;'||CHR(10)
          || '  :pv_super_hero '||CHR(58)||'= lv_super_villain;'||CHR(10)
          || '  :pv_super_villain '||CHR(58)||'= lv_super_hero;'||CHR(10)
          || 'END;';
 
  -- Run the NDS program.
  EXECUTE IMMEDIATE lv_stmt USING IN OUT lv_super_hero
                                , IN OUT lv_super_villain;
 
  -- Print the diagnostic code block, that's why it used line returns afterall.  
  dbms_output.put_line('--------------------------------------------------');
  print_code_block(lv_stmt);
  dbms_output.put_line('--------------------------------------------------');
 
  -- Demonstrate the world is upside down without Johnny Depp playing Capt'n Jack.
  dbms_output.put_line('The good '||lv_super_hero||' beats up the bad '||lv_super_villain||'!');
 
END;
/

You’ll get the following printed output:

The good Thor beats up the bad Loki!
--------------------------------------------------
| DECLARE
|   lv_super_hero     VARCHAR2(20);
|   lv_super_villain  VARCHAR2(20);
| BEGIN
|   lv_super_hero := :pv_super_hero;
|   lv_super_villain := :pv_super_villain;
|   :pv_super_hero := lv_super_villain;
|   :pv_super_villain := lv_super_hero;
| END;
--------------------------------------------------
The good Loki beats up the bad Thor!

As always, I hope it helps you understand the concept of the USING clause with IN OUT parameters but I hope there’s always better way.

Somebody asked about the possibility of putting DML statements inside MySQL stored functions. DML statements like the INSERT, UPDATE, and DELETE. When I said, “Yes, you can put DML statements inside functions.” They showed me the error they encountered, which is only raised at compilation when you put an explicit COMMIT statement or a Data Definition Language (DDL) statement (CREATE, ALTER, DROP, or RENAME) inside a MySQL function. The actual error message displayed is:

ERROR 1422 (HY000): Explicit OR implicit commit IS NOT allowed IN stored FUNCTION OR TRIGGER.

While an explicit COMMIT is obvious when placed inside a function, the implicit COMMIT statement isn’t obvious unless you know a DDL statement generates one. This means you can’t include any DDL statement inside a stored function.

The following example shows how a DDL statement creates an immediate implicit COMMIT. It requires two sessions and Transaction Control Language (TCL) statements.

In session one, create a table, start a transaction scope, and insert one row into the table:

-- Create a table.
CREATE TABLE message
( message_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, message_text  VARCHAR(20));
 
-- Start a transaction context.
BEGIN WORK;
 
-- Insert a row into the MESSAGE table.
INSERT INTO message (message_text) VALUES ('Implicit Commit?');

In session two, the empty set is displayed when you query the MESSAGE table. Returning to session one, add a new column to the MESSAGE table with this statement:

ALTER TABLE message ADD (sent DATETIME);

The ALTER statement automatically makes current any pending data changes, effectively committing the record from session one. You can return to the second session and query the table you get the following results:

+------------+------------------+------+
| message_id | message_text     | sent |
+------------+------------------+------+
|          1 | Implicit Commit? | NULL |
+------------+------------------+------+

When you embed DML statements inside functions, they rely on automatic commit behaviors or an external transaction scope. This function compiles and returns 1 when successful and 0 when unsuccessful, which effectively controls all error conditions within the function:

CREATE FUNCTION modifies_data ( pv_message  CHAR(20) ) RETURNS INT
MODIFIES SQL DATA
BEGIN
  /* Declare Boolean-like variables as FALSE. */
  DECLARE lv_return_value INT DEFAULT FALSE;
  DECLARE lv_error_value INT DEFAULT FALSE;
 
  /* Declare a generic exit handler to reset error control variable to true. */
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET lv_error_value := TRUE;   
 
  /* Insert statement with auto commit enabled. */
  INSERT INTO message (message_text) VALUES (pv_message);
 
  /* True unless the CONTINUE HANDLER disables the error control variable. */ 
  IF lv_error_value = FALSE THEN
    SET lv_return_value := TRUE;
  END IF;
 
  /* Return local variable. */
  RETURN lv_return_value;
END;
$$

With auto commit enabled, any call to the function in a query writes a row to the table. If you start a transaction and in the scope of the transaction test the function in one session and query the table in another you’ll see that transaction control can be managed outside the function. You can also manage the transaction control inside a stored procedure, which holds all the TCL commands. An example follows:

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

CREATE PROCEDURE test_modifies_data ( pv_message  CHAR(20) )
MODIFIES SQL DATA
BEGIN
  /* Declare a control variable that manages transaction
     success or failure. */
  DECLARE lv_success_value  INT DEFAULT FALSE;
 
  /* Start a transaction context. */
  START TRANSACTION;
 
  /* Set a SAVEPOINT in the transaction context. */
  SAVEPOINT before_transaction;
 
  /* Call the function. */
  SET lv_success_value := modifies_data(pv_message);
 
  /* Check the status of the control variable, and commit
     or rollback the transaction. */
  IF lv_success_value = TRUE THEN
    COMMIT;
  ELSE
    ROLLBACK TO before_transaction;
  END IF;
 
END;
$$

Test the two with the following call to the procedure:

CALL test_modifies_data('Agent Coulson');

Answers my question and I hope it helps others.

The topic of WebSockets came up today, and it seems a good idea to qualify WebSockets in the blog beyond simply pointing folks to Kaazing. HTML5’s WebSockets are a great fix for the half-duplex world of AJAX. They certainly have the potential to be more scalable than current Comet solutions, and present a real-time communication alternative. The latest draft of The WebSocket API was published earlier this month.

If you’re new to WebSockets, you may want to review Peter Lubbers’ PowerPoint presentation from the International Conference on Java Technology, 2010 (or the updated version he provided via a comment to this post). That is probably shorter than watching the multiple parts posted in 10-minute segments on YouTube.

More or less the PDF file of the presentation covers how HTTP is half-duplex not full duplex, and why it doesn’t support real-time data in a browser. It points to AJAX (Asynchronous JavaScript and XML) as a programming nightmare – or more precisely a workaround to a limitation of HTTP/HTTPS. It also covers the idea of COMET programming or frameworks as complex and incomplete attempts to simulate full duplex or bi-directional communication on the web.

As Peter Lubbers qualifies, AJAX and COMET solutions don’t scale against high transaction volumes or concurrency because their header traffic overwhelms the actual data transfers. This reality occurs more or less because browsers only implement unidirectional communication through a request and acknowledgement model and send large header sequences compared to small data footprints.

Peter Lubbers presents three types of HTTP solutions in the presentation:

• Polling involves periodic requests to the server for updated information, and it is the backbone of many Ajax applications that simulate real-time communication. The HTTP message headers involved in polling are frequently larger than the transmitted data; and while polling works well in low-message rate situations it doesn’t scale well. It also involves opening and closing many connections to the server and hence database needlessly in some cases.
• Long Polling or asynchronous-polling requests the server to keep the connection open for a set period of time. It doesn’t solve problems with high-message rates situations of polling in general because it creates a continuous loop of immediate polls and each poll, like ordinary polling messages, sends mostly HTTP headers not data.
• Streaming architecture opens a socket but can cause problems with proxy and firewall servers, create cross-domain issues due to browser connection limits, and periodically pose overhead to flush streams that have built up in the communication channel. Streaming solutions reduce HTTP header traffic but at a cost in overhead to the server.

Websockets are designed to fix these issues. The most interesting thing about polling, long polling, streaming, or Websockets is they require the same careful attention to how databases validate user access and serve up content. When the HTML5 standard nears completion, they’ll be a great need for database connection solutions, like Oracle’s Data Resident Connection Pooling.

By the way, here are some great video links for learning HTML5.

I finally got around to cleaning up old contact me messages. One of the messages raises a question about RESULT_CACHE functions. The writer wanted an example implementing both a standalone schema and package RESULT_CACHE function.

The question references a note from the Oracle Database 11g PL/SQL Programming book (on page 322). More or less, that note points out that at the time of writing a RESULT_CACHE function worked as a standalone function but failed inside a package. When you tried it, you raised the following error message:

PLS-00999: Implementation Restriction (may be temporary)

It’s no longer true in Oracle 11gR2, but it was true in Oracle 11gR1. I actually mentioned in a blog entry 4 years ago.

You can implement a schema RESULT_CACHE function like this:

1
2
3
4
5
6
7
8

CREATE OR REPLACE FUNCTION full_name
( pv_first_name   VARCHAR2
, pv_last_name    VARCHAR2 )
RETURN VARCHAR2 RESULT_CACHE IS
BEGIN  
  RETURN pv_first_name || ' ' || pv_last_name;
END full_name;
/

You would call it like this from a query:

SELECT   full_name(c.first_name, c.last_name)
FROM     contact c;

You can declare a published package RESULT_CACHE function like this:

1
2
3
4
5
6
7

CREATE OR REPLACE PACKAGE cached_function IS
  FUNCTION full_name
  ( pv_first_name   VARCHAR2
  , pv_last_name    VARCHAR2 )
  RETURN VARCHAR2 RESULT_CACHE;
END cached_function;
/

You would implement the function in a package body like this:

1
2
3
4
5
6
7
8
9
10

CREATE OR REPLACE PACKAGE BODY cached_function IS
  FUNCTION full_name
  ( pv_first_name   VARCHAR2
  , pv_last_name    VARCHAR2 )
  RETURN VARCHAR2 RESULT_CACHE IS
  BEGIN  
    RETURN pv_first_name || ' ' || pv_last_name;
  END full_name; 
END cached_function;
/

You would call the package function like this from a query:

SELECT   cached_function.full_name(c.first_name, c.last_name)
FROM     contact c;

I hope this answers the question.

Life’s interesting, and as they say, “Things always change.” While there’s a great debate about the future of books and their usefulness in certain formats, there’s also a discussion about whether they should be free, cheap or pricey? This debate is especially interesting in the arena of technology because the life-cycle of books becomes ever shorter as software release cycles narrow.

For example, you can purchase the new Introducing Microsoft SQL Server 2012 printed and bound book (shown in the illustration at the left) at Amazon.com, or you can download it for free from here as a PDF, EPUB, or Mobi file. While I only glanced through it, it looks like a well written and complete book. It’s also free electronically, which begs the questions how the authors and publishers recovered the cost of producing the work.

Is this the future of technical books? After all, technical books exist to smooth the uptake of new software and to humanize the information that is often too verbose (trying to be exhaustive of software uses) or too short (trying to be accessible in the smallest amount of time). It seems there will always be books for these reasons about technology. I would characterize three problems with technology books, and they’re readability, content, and affordability.

• Readability is an interesting trick with a technology book because of how we seems to measure it. Unlike a novel, The Da Vinci Code, technology books seem to be measured on different criteria. The criteria seem to be how well the books expose features, instruct audiences, and provide complete or comprehensive reference; and sometimes, seemingly rarer, they’re read cover-to-cover with an expectation of thematic story telling along with the features, instructions, or reference materials.
• Content should be accurate, concise, and accessible. This is always a challenge as the length of books get larger because the time allotted to the write, shrinks during editing and review cycles. Often the author doesn’t get to review the index or final proof galleys and typos invariable creep in to any book. Typos in code are annoying but incomplete code fragments drive reader’s nuts. Content should include complete programs or modules that enable the reader to test concepts explained in the text.
• Affordability is the largest hurdle because access to information drives success for technicians working with cutting edge technology. Paraphrasing what Alvin Toffler wrote in Powershift: Knowledge, Wealth, and Violence at the Edge of the 21st Century, knowledge or access to knowledge is power, more precisely power that captures wealth.

If vendors, like Microsoft, underwrite books by paying the author and publisher upfront, they remove the risk and vouchsafe financial return associated with producing the book. The likelihood is that the freely distributed copies may no longer yield revenue to the author or publisher, which means the author’s and publisher’s compensation is upfront and limited to a contracted amount. This would operate like the current advance amount, which is only exceeded when the book sells well. This type of arrangement guarantees books at or near production plus supply chain and inventory costs.

If the incentive to write, produce, and maintain (correct problems with) the book are reasonable and the book provides readability, accurate content, and affordability, this may be the future of technical publishing. It certainly begins to lower the barrier to entry cost of their technology. What do you think?

A question came up today about how to stripe a MySQL view, and this post shows you how. Along with the question, there was a complaint about why you can’t use session variables in a view definition. It’s important to note two things: there’s a workaround and there’s an outstanding request to add lift the feature limitation in Bug 18433.

A striped view lets authorized users see only part of a table, and is how Oracle Database 11g sets up Virtual Private Databases. Oracle provides both schema (or database) level access and fine-grained control access. Fine grained control involves setting a special session variable during a user’s login. This is typically done by checking the rights in an Access Control List (ACL) and using an Oracle built-in package.

You can do more or less the same thing in MySQL by using stored functions. One function would set the session variable and the other would fetch the value for comparison in a view.

Most developers who try this initially meet failure because they try to embed the session variable inside the view, like this trivial example with Hobbits (can’t resist the example with the first installment from Peter Jackson out later this year):

1
2

CREATE VIEW hobbit_v AS
SELECT * FROM hobbit WHERE hobbit_name = @sv_login_name;

The syntax is disallowed, as explained in the MySQL Reference 13.1.20 CREATE VIEW Syntax documentation. The attempt raises the following error message:

ERROR 1351 (HY000): VIEW's SELECT contains a variable or parameter

The fix is quite simple, you write a function that sets the ACL value for the session and another that queries the ACL session value. For the example, I’ve written the SET_LOGIN_NAME and a GET_LOGIN_NAME functions. (If you’re new to stored programs, you can find a 58 page 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.)

You would call the SET_LOGIN_NAME when you connect to the MySQL database as the first thing to implement this type of architecture. You would define the function like the following. (Please note that the example includes all setup statements from the command line and should enable you cutting and pasting it. ;-)):

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

-- Change the delimiter to something other than a semicolon.
DELIMITER $$
 
-- Conditionally drop the function.
DROP FUNCTION IF EXISTS set_login_name$$
 
-- Create the function.
CREATE FUNCTION set_login_name(pv_login_name VARCHAR(20)) RETURNS INT UNSIGNED
BEGIN
 
  /* Declare a local variable to verify completion of the task. */
  DECLARE  lv_success_flag  INT UNSIGNED  DEFAULT FALSE;
 
  /* Check whether the input value is something other than a null value. */
  IF pv_login_name IS NOT NULL THEN
 
    /* Set the session variable and enable the success flag. */
    SET @sv_login_name := pv_login_name;
    SET lv_success_flag := TRUE;
 
  END IF;
 
  /* Return the success flag. */
  RETURN lv_success_flag;
END;
$$
 
-- Change the delimiter back to a semicolon.
DELIMITER ;

You can use a query to set and confirm action like this:

SELECT IF(set_login_name('Frodo')=TRUE,'Login Name Set','Login Name Not Set') AS "Login Name Status";

Or, you can use the actual number 1 in lieu of the TRUE, like this:

SELECT IF(set_login_name('Frodo')=1,'Login Name Set','Login Name Not Set') AS "Login Name Status";

Please check this older post on how MySQL manages logical constants and the realities of TRUE and FALSE constants. A more practical example in an API would be this, which returns zero when unset and one when set:

SELECT set_login_name('Frodo') AS "Login Name Status";

The getter function for this example, simply reads the current value of the MySQL session variable. Like the prior example, it’s ready to run too.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- Change the delimiter to something other than a semicolon.
DELIMITER $$
 
-- Conditionally drop the function.
DROP FUNCTION IF EXISTS get_login_name$$
 
-- Create the function.
CREATE FUNCTION get_login_name() RETURNS VARCHAR(20)
BEGIN
  /* Return the success flag. */
  RETURN @sv_login_name;
END;
$$
 
-- Change the delimiter back to a semicolon.
DELIMITER ;

Before you test it, lets create a HOBBIT table, seed it with data, and create a HOBBIT_V view. They’re bundled together in the following microscript:

-- Conditionally drop the table.
DROP TABLE IF EXISTS hobbit;
 
-- Create the table.
CREATE TABLE hobbit
( hobbit_id    INT UNSIGNED
, hobbit_name  VARCHAR(20));
 
-- Seed two rows.
INSERT INTO hobbit VALUES ( 1,'Bilbo'),( 1,'Frodo');
 
-- Conditionally drop the view.
DROP VIEW IF EXISTS hobbit_v;
 
-- Create the function-enabled view.
CREATE VIEW hobbit_v AS
SELECT * FROM hobbit WHERE hobbit_name = get_login_name();

A query to the table after setting the session variable will only return one row, the row with Frodo in the HOBBIT_NAME column. It also guarantees an unfiltered UPDATE statement against the view only updates the single row returned, like this:

UPDATE hobbit_v SET hobbit_id = 2;

In a real solution, there are more steps. For example, you’d want your tables in one database, views in another, and functions and procedures in a library database. However, I hope this helps seed some ideas for those interested in creating fine-grained virtual private databases in MySQL with user-authenticated application controls.

Escape from New York, a cult classic, has Snake (Kurt Russell) rescue the President from Manhattan, which in this dystopian film’s theme is a prison. Windows 8 seems like a new dystopian version of Windows where only Microsoft’s browser works to perform real browser activities.

Gone is the European Union’s antitrust suit. Why? Oh, yes, Microsoft promised to provide options to users of its platform. Gone that! Will the European Union retake the mantle of the Avengers and free us from the mantle of Microsoft’s attempt to play Loki in compelling users back to their browser?

An excerpt from Greg Keizer’s ComputerWorld piece:

“Windows on ARM [the former name for Windows RT] prohibits any browser except for Internet Explorer from running in the privileged ‘Windows Classic’ environment,” said Anderson. “In practice, this means that only Internet Explorer will be able to perform many of the advanced computing functions vital to modern browsers in terms of speed, stability, and security to which users have grown accustomed.”

While I’ve used Microsoft Excel since version 3 – sent to me for review by Microsoft along with my first non-Mac mouse in 1990 – their browser is something to avoid. It’s the first thing that I disable when installing a new Windows’ instance, by substituting Mozilla’s Firefox.

My question is: Will all those accounts and finance folks who drive Microsoft Office sales because of Excel’s VBA features really drink the Kool-Aid and buy into this new generation of browser tyranny? Let’s hope they don’t! Maybe they’ll take a closer look at OpenOffice 3. 😉

However, I strongly doubt they’ll surrender their VBA driven models unless OpenOffice provides something similar. Let’s face it. Microsoft Excel’s VBA feature is probably one of the smartest thing Microsoft ever marketed because it’s seals the deal for the Microsoft cash cow – the Microsoft Office Suite.

Disclaimer: I’m primarily a Mac OS X, Linux, and Unix user, but like it or not, the reach of the Windows’ desktop is omnipresent in our lives; and let’s face it: Windows 8 will continue to be the most frequent choice for corporate desktops. It’s also the platform for Microsoft SQL Server. Therefore, it’s critical for us to rise up and shout for browser freedom – Firefox, Chrome, Safari, and the little guys too!