Archive for the ‘MySQL’ Category
Single Wildcard Operator
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.
Overloading Procedures
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_schemaand 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.
Implicit Commit Functions?
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.
MySQL Striped Views
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.
Value or Reference?
IN-only mode) parameters and pass-by-reference (INOUT and OUT mode) parameters for stored procedures. The analogy that finally seemed to hit home for the students was linking the modes to the story of Alice in Wonderland.
Here’s the analogy and below is the code to support it:
“A pass-by-value parameter in a procedure is like sending an immutable copy of Alice into the rabbit hole, which means she can’t shrink, grow, or learn throughout the story; whereas, a pass-by-reference parameter in a procedure is like sending Alice into the rabbit hole where she can shrink, grow, fight the Jabberwocky, and learn things that make her life better when she exits the rabbit hole – consistent with the storyline of Alice’s revisit to Wonderland.”
The example code creates a stored procedure that accepts two parameters – one pass-by-value and one pass-by-reference. Inside the procedure there’s a local variable and a reassignment of value to the pass-by-reference parameter. It’s in this wonderland procedure (by the way don’t forget to manage the DELIMITER value when you test 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 | CREATE PROCEDURE wonderland ( IN pv_value_param VARCHAR(20) , INOUT pv_ref_param VARCHAR(20)) BEGIN /* Declare a variable. */ DECLARE lv_value_param VARCHAR(20); /* Query the local variable and reference parameter before changing values. */ SELECT 'On Entry' AS "Where" , pv_value_param AS "Value Parameter" , pv_ref_param AS "Reference Parameter" , IFNULL(lv_value_param,' ') AS "Local Variable"; /* Assign a lowercase value parameter to a local variable. */ SET lv_value_param := LOWER(pv_value_param); /* Assign a uppercase reference parameter value to the reference parameter. */ SET pv_ref_param := UPPER(pv_ref_param); /* Query the local variable and reference parameter after changing values. */ SELECT 'On Exit ' AS "Where" , pv_value_param AS "Value Parameter" , pv_ref_param AS "Reference Parameter" , IFNULL(lv_value_param,' ') AS "Local Variable"; END; $$ |
A tester procedure than tests how the pass-by-value and pass-by-reference modes of operation differ. It’s here:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | CREATE PROCEDURE tester ( IN pv_value_param VARCHAR(20) , IN pv_ref_param VARCHAR(20)) BEGIN /* Query the local and reference parameters. */ SELECT 'Before ' AS "Where" , pv_value_param AS "Value Parameter" , pv_ref_param AS "Reference Parameter"; /* Call the wonderland procedure that changes the pass-by-reference parameter. */ CALL wonderland(pv_value_param, pv_ref_param); /* Query the local and reference parameters. */ SELECT 'After ' AS "Where" , pv_value_param AS "Value Parameter" , pv_ref_param AS "Reference Parameter"; END; $$ |
You call the tester program with this syntax:
CALL tester('Alice','Alice'); |
The test case returns the following values:
+----------+-----------------+---------------------+ | Where | Value Parameter | Reference Parameter | +----------+-----------------+---------------------+ | Before | Alice | Alice | +----------+-----------------+---------------------+ 1 row in set (0.00 sec) +----------+-----------------+---------------------+----------------+ | Where | Value Parameter | Reference Parameter | Local Variable | +----------+-----------------+---------------------+----------------+ | On Entry | Alice | Alice | | +----------+-----------------+---------------------+----------------+ 1 row in set (0.02 sec) +----------+-----------------+---------------------+----------------+ | Where | Value Parameter | Reference Parameter | Local Variable | +----------+-----------------+---------------------+----------------+ | On Exit | Alice | ALICE | alice | +----------+-----------------+---------------------+----------------+ 1 row in set (0.02 sec) +----------+-----------------+---------------------+ | Where | Value Parameter | Reference Parameter | +----------+-----------------+---------------------+ | After | Alice | ALICE | +----------+-----------------+---------------------+ 1 row in set (0.03 sec) |
Basically, Alice inside the pv_ref_param parameter grows to uppercase during the trip through the wonderland procedure, while Alice inside the pv_value_param remains unchanged. If it didn’t help you learn a principle, maybe it gave you a laugh on how to view the travels of IN-only and INOUT parameters. 😉
NOTE: Line 12 in the alice procedure is impossible with an immutable variable because the value of a call parameter to an immutable IN-only formal parameter shouldn’t allow the call parameter value to change during the execution of the program. This means that MySQL IN-only mode parameter values actually hold a mutable copy of the call parameter and the call parameter can be either a variable or literal value. The parameter value is discarded at the completion of procedure. This is more easily demonstrated with this assignment procedure:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE PROCEDURE assignment ( IN pv_value_param VARCHAR(20)) BEGIN /* Query the local and reference parameters. */ SELECT 'Before ' AS "Where" , pv_value_param AS "Value Parameter"; /* Call the wonderland procedure that changes the pass-by-reference parameter. */ SET pv_value_param := UPPER(pv_value_param); /* Query the local and reference parameters. */ SELECT 'After ' AS "Where" , pv_value_param AS "Value Parameter"; END; $$ |
Therefore a call like this prints an uppercase INBOUND string inside the program but can return nothing to the calling scope since the call parameter is a string literal.
SET @sv_session = 'inbound'; CALL assignment(@sv_session); SELECT @sv_test AS "Current Value"; |
The query displays:
+---------------+ | Current Value | +---------------+ | inbound | +---------------+ |
I hope this helps.
Collaborate 2012 – Day 4
Last day of Collaborate 2012 and Scott Spendolini, Sumneva, gave a great presentation on APEX. Only caught the beginning Jan Visser’s Perl presentation because of the distance to the Luxor from the Mandalay South Conference Center and anticipated queuing time for checkout.
We can now look forward to Collaborate 2013 in Denver, Colorado.
Back to observing and working with code, here’s a nice article from MacWorld on how you set up a WebDAV on the Mac. While I’m mentioning Mac OS X and development, there’s still no firm upgrade window for the missing text editing tool – TextMate, and WWDC 2012 tickets sold out in two hours.
Collaborate 2012 – Day 3
Virtualization is important and Dave Welch from the House of Brick gave a great presentation of experiences with VMWare and Tier 1 databases. It was a comprehensive presentation, but the white paper was easier to follow. The slides were complete but the volume of information was a lot for an hour presentation. Well worth the time though.
Utah Oracle User Group (UTOUG) announced a call for Fall Symposium papers today. The Fall Symposium will be in Salt Lake City on 9/6/2012. If you’re interested in presenting on Oracle or MySQL, the call for presentations will be open until 6/15/2012.
The conference party was tonight, and it provided some nice orderves and pizza. The theme was a return to 1980s music, and some folks really dressed their parts. You can listen to a short snapshot of the band by clicking the image to launch a small video segment.
I’m looking forward to the APEX Behind the Scenes presentation at 8:30 a.m. tomorrow. When the conference is over, I won’t miss the smoke filled air that we walk through from the Luxor to the Mandalay. It’s really amazing that the complex is more than a mile in length. It runs from the Luxor to the Mandalay South Conference Center.
Collaborate 2012 – Day 2
It seems the Titanic is everywhere, even inside the pyramid of the Luxor hotel. While the Luxor is within the Mandalay Bay complex, it’s about a half mile walk to the conference and a half mile back. We go by the Mandalay Conference Center’s aquarium. We thought it might be interesting but at $18 an admission, we opted to pass on it. It’s amazing to have an aquarium in the desert, but it’s probably not as nice as the Monterey Bay aquarium.
It was interesting to start the day listening to Rich Niemiec on partitioning tables and using Exadata in Oracle. The NoSQL (Not Only SQL) presentations were interesting, as was the upgrading of Oracle 11gR2 in an E-Business Suite environment presentation. Then, I finished the day with what’s new with the Oracle VM Server.
Checking out the exhibit hall I managed to get a signed copy of Rich Niemiec’s Oracle Database 11g Release 2 Performance Tuning Tips & TechniquesOracle Database 11g Release 2 Performance Tuning Tips & Techniques and a copy of MongoDB: The Definitive GuideMongoDB The Definitive Guide.
Collaborate 2012 – Day 1
Collaborate 2012 started on Sunday but for me I began on Monday. I enjoyed Bob Burgess, SalesForce, presentation on shell scripting for MySQL Administration today. It preceded my presentation in the same room, which I thought was an interesting coincidence since we got our conference credentials together.
I presented on portable SQL between Oracle and MySQL. The presentation went well. Before I took questions, I got to ask them because I had three copies of my new Oracle Press book to give away: Oracle Database 11g and MySQL 5.6 Developer Handbook. Handing out the books served as a nice ice breaker for the audience to ask questions about the presentation.
My favorite question was, “Will Oracle continue to improve MySQL?” My answer to that is always simple because Oracle’s support for MySQL has and continues to be great, “Oracle only spends money on winners and that means MySQL wins.” Oracle product management was in attendance and they re-enforced Oracle’s commitment to MySQL.
At 6 p.m., the Exhibit Hall opened and I checked it out. Cisco hired Kathy Bailey to draw caricatures, and she drew mine as you can see at the left. I’m looking forward to more presentations tomorrow.
MySQL REGEXP Error
While working through prepared statements in MySQL, there was an interesting MySQL regular expression question raised. A student wanted to know how to address the following error message:
ERROR 1139 (42000): Got error 'repetition-operator operand invalid' FROM REGEXP |
They had substituted * for a .+ in a metasequence. A metasequence is a parenthetical expression that evaluates based on multiple alternative conditions, and the pipe (|) acts as an OR operator. The full code example is found on page 482 of the Oracle Database 11g & MySQL 5.6 Developer Handbook. The student’s change would have worked without an error had he replaced the metasequence with .* instead of the solitary *.
The original call to the procedure passes the following well formed regular expression:
CALL prepared_dml('(^|^.+)war(.+$|$)'); |
Or, they could eliminate the metasequences and use:
CALL prepared_dml('^.*war.*$'); |
Either returns the following entries from a column with movie titles from the sample code:
Charlie's War Star Wars I Star Wars II Star Wars III |
The dot (.) means any possible character, and the plus (+) means one-to-many possible repeating characters of a preceding character. When the dot precedes the plus, it means one-to-many wildcard characters. The student replaced the metasequence with an asterisk by itself and generated the badly formed regular expression error.
The misunderstanding occurs because the asterisk (*) by itself doesn’t mean zero-to-many wildcard. The combination of the dot and asterisk creates a zero-to-many wildcard, which works when there is or isn’t a character before the first character of a string or after the last character of a string. It also eliminates the need for a metasequence.
Here’s a small test case outside of the book’s stored procedure:
-- Conditionally drop the table. DROP TABLE IF EXISTS list; -- Create the table. CREATE TABLE list ( list_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, list_item VARCHAR(20)); -- Insert for rows. INSERT INTO list (list_item) VALUES ('Star'),(' Star'),(' Star '),('Star '); -- Query for zero-to-many leading characters. SELECT list_id AS "ID" , list_item AS "Zero-to-many leading characters " , LENGTH(list_item) AS "Length" , '^.*Star.*$' AS "Regular Expression" FROM list WHERE list_item REGEXP '^.*Star.*$'; -- Query for zero-to-many leading characters. SELECT list_id AS "ID" , list_item AS "One-to-many leading characters " , LENGTH(list_item) AS "Length" , '^.+Star.*$' AS "Regular Expression" FROM list WHERE list_item REGEXP '^.+Star.*$'; -- Query for one-to-many leading characters. SELECT list_id AS "ID" , list_item AS "Zero-to-many trailing characters" , LENGTH(list_item) AS "Length" , '^.*Star.*$' AS "Regular Expression" FROM list WHERE list_item REGEXP '^.*Star.*$'; -- Query for one-to-many leading characters. SELECT list_id AS "ID" , list_item AS "One-to-many trailing characters " , LENGTH(list_item) AS "Length" , '^.*Star.+$' AS "Regular Expression" FROM list WHERE list_item REGEXP '^.*Star.+$'; |
The output from the scripts is:
+----+----------------------------------+--------+--------------------+ | ID | Zero-to-many leading characters | Length | Regular Expression | +----+----------------------------------+--------+--------------------+ | 1 | Star | 4 | ^.*Star.*$ | | 2 | Star | 5 | ^.*Star.*$ | | 3 | Star | 6 | ^.*Star.*$ | | 4 | Star | 5 | ^.*Star.*$ | +----+----------------------------------+--------+--------------------+ 4 rows in set (0.02 sec) +----+----------------------------------+--------+--------------------+ | ID | One-to-many leading characters | Length | Regular Expression | +----+----------------------------------+--------+--------------------+ | 2 | Star | 5 | ^.+Star.*$ | | 3 | Star | 6 | ^.+Star.*$ | +----+----------------------------------+--------+--------------------+ 2 rows in set (0.00 sec) +----+----------------------------------+--------+--------------------+ | ID | Zero-to-many trailing characters | Length | Regular Expression | +----+----------------------------------+--------+--------------------+ | 1 | Star | 4 | ^.*Star.*$ | | 2 | Star | 5 | ^.*Star.*$ | | 3 | Star | 6 | ^.*Star.*$ | | 4 | Star | 5 | ^.*Star.*$ | +----+----------------------------------+--------+--------------------+ 4 rows in set (0.02 sec) +----+----------------------------------+--------+--------------------+ | ID | One-to-many trailing characters | Length | Regular Expression | +----+----------------------------------+--------+--------------------+ | 3 | Star | 6 | ^.*Star.+$ | | 4 | Star | 5 | ^.*Star.+$ | +----+----------------------------------+--------+--------------------+ 2 rows in set (0.02 sec) |
Hope this helps.
Subscribe via RSS