Archive for the ‘MySQL Developer’ tag
Setup SQL Server Schema
After you’ve installed Microsoft SQL Server 2012 and created a database schema, you still need to setup or configure the database schema. This is the recommended Microsoft SQL Server Best Practice.That means you must grant permissions to a user to work in the database schema, otherwise you get the following type of error message.
Msg 2760, Level 16, State 1, Line 1 The specified schema name "studentdb" either does not exist or you do not have permission to use it. |
You receive the foregoing error message when trying to create a table in a database schema that hasn’t been assigned a user or role. This typically occurs after you create a new database schema, which has a <default>
owner.
For those new to the idea of schemas (or scheme), they were introduced by Microsoft in SQL Server 2005. Here are some rules of thumb on schemas:
- Database schema names are distinct from user names.
- Multiple users may share a schema, which means it can hold objects owned by multiple users.
- Permissions to act inside the schema may be granted to individual users, and you have a varied set of privileges that you can assign to users.
- Ownership of objects in a schema can be transferred using the
ALTER SCHEMA
command. - Ownership of a schema can be changed by using the
ALTER AUTHORIZATION
command. - Database users can be dropped without changing objects in schemas, which is a big difference between Oracle and SQL Server.
The following example occurs when you try to create a table in a studentdb
database schema. It doesn’t expose you to the SQL syntax but demonstrates how to manage the changes within the SQL Server Management Studio (SSMS).
By the way, you start the new schema creation process by right clicking on the Databases folder in the SQL Server Management Studio. Then, you give the new database schema a name in the New Database dialog; and click the OK button to complete the process.
The balance of the instructions show you how to create a user account that will map to your new database schema. It assumes you’ve installed it on your local machine and have privileges through local Windows Authentication to the System Administrator (sa) account. You create a student
user account, assign the studentdb
database schema, log off as the System Administrator, log in as the new user, conditionally drop a table from the studentdb
schema, create a table in the studentdb
schema, and query the results. It’s also possible to create the user first, database second, and assign the database as the default database for the user account.
- The first screen asks you to authenticate as the System Administrator using Windows Authentication. Click the Connect button to connect to the SQL Server Management System (SMSS).
- Click the Security folder and expand the subordinate list of folders. Right click on the Logins folder and click on the New Login option in the context menu. It launches a new dialog where you enter a user account.
- On the General page of the Login – New dialog, enter a Login name and click the SQL Server authentication radio button. Clicking the radio button enables the Password and Confirm password fields where you enter the same password twice. Click the Default database drop down and choose the studentdb created earlier. Click the Server Roles page to continue.
- On the Server Roles page, ensure that only the public server role is checked. Click on the User Mapping page to continue.
- On the User Mapping page, click the Map check box for the
studentdb
database, enterstudent
in the User field, and enterstudentdb
in the Default Schema field. Click the db_owner and public database role membership forstudentdb
. Click on the Securables page to continue.
- There are no required changes on the Securables page. Check that screen matches the illustration at the left, and click on the Status page to continue.
- There are no required changes on the Status page. Check that screen matches the illustration at the left, and click on the OK button to complete creating the user. Then, exit the SQL Server Management Studio as the System Administrator.
- Launch the SQL Server Management Studio, choose SQL Server Authentication as the authentication method, and enter the
student
Login (user name) and Password before clicking the Connect button to continue.
- The first time a user logs in, they must re-enter a valid password. After entering the password twice, click the OK button to continue.
- The initial display shows only the Object Explorer. Click the New Query option in the menu toolbar.
- Click on the Databases folder to expand the list and you will see the
studentdb
database schema displayed, as shown in the illustration on the left.
- Enter a conditional drop of a
conquistador
table from thestudentdb
database schema. Click the Execute button from the toolbar menu to run the statement.
IF OBJECT_ID('studentdb.conquistador','U') IS NOT NULL DROP TABLE studentdb.conquistador; |
- Enter a
CREATE
statement for aconquistador
table in thestudentdb
database schema. Click the Execute button from the toolbar menu to run the statement.
CREATE TABLE studentdb.conquistador ( conquistador_id INT NOT NULL IDENTITY(1,1) CONSTRAINT pk_conquistador PRIMARY KEY , conquistador VARCHAR(30) , actual_name VARCHAR(30) , nationality VARCHAR(30)); |
- Enter an
INSERT
statement to theconquistador
table in thestudentdb
database schema. Click the Execute button from the toolbar menu to run the statement and see that it inserted 9 rows.
INSERT INTO conquistador VALUES ('Juan de Fuca','Ioánnis Fokás','Greek') ,('Nicolás de Federmán','Nikolaus Federmann','German') ,('Sebastián Caboto','Sebastiano Caboto','Venetian') ,('Jorge de la Espira','Georg von Speyer','German') ,('Eusebio Francisco Kino','Eusebius Franz Kühn','Italian') ,('Wenceslao Linck','Wenceslaus Linck','Bohemian') ,('Fernando Consag','Ferdinand Konšcak','Croatian') ,('Américo Vespucio','Amerigo Vespucci','Italian') ,('Alejo García','Aleixo Garcia','Portuguese'); |
- Click the
studentdb
folder to expand content, and you should see theconquistador
table.
SELECT * FROM conquistador; |
- Click in the SQL Query frame and enter a query against the
conquistador
table. Click the Execute button to run the query and you should see the following output from theconquistador
table.
As always, I hope this helps those trying to sort out how to solve a similar problem.
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_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.