MacLochlainns Weblog

Teaching SQL is interesting because folks try syntax that experience would tell you shouldn’t work. It was interesting when I discovered what should be broken from my perspective but was expected behavior in MySQL. It became clearer to me why it’s there as I did some experimenting with it enabled and disabled.

The reason that I find that it broken is a matter of perspective not standards. The fact that you can select a set of non-aggregated columns with an aggregated column, and exclude one, more than one, or all of the non-aggregated columns from the GROUP BY clause seemed like a broken behavior. The MySQL behavior is explained in Chapter 11.12.3 of the MySQL 5.1 Reference. What wasn’t clear from the documentation when I read it was why the behavior is allowed. Gary’s comment on the original post made me look more deeply into the behavior.

If you check the referenced documentation, the process is called GROUP BY hidden columns. That label didn’t immediately resonate with me. That’s probably of my background with IBM’s DB2 and Oracle. I’m so accustom to how they work, that sometimes I think they set the standards but they don’t. It turns out that the GROUP BY clause in the ANSI SQL standards doesn’t allow for expressions.

The default configuration of MySQL allows for the expressions (functions) in the GROUP BY clause. The documentation refers to hidden columns, which are in point of fact expressions in the GROUP BY clause. MySQL does support expressions in the GROUP BY by default, and requires, like Oracle, that when you use an expression in the SELECT clause that you mirror it in the GROUP BY clause. This means you group on the result of the expression, not a column in the table or view.

In the default configuration, you have three options. They are determinate results, indeterminate results, and hidden column results. The following cover these components.

Determinate Results

Determinate results are straight forward. They require that all non-aggregated columns in the SELECT clause are mirrored in the GROUP BY clause. This means that the non-aggregated column values are the key upon which results are aggregated.

An example of determinate results is:

1
2
3
4
5

SELECT   key_one
,        key_two
,        SUM(counter)
FROM     grouping
GROUP BY key_one;

Indeterminate Results

Indeterminate results isn’t straight forward but isn’t hard to grasp. An indeterminate result set is returned when one or more non-aggregated columns in a SELECT clause aren’t listed in the GROUP BY clause. The columns listed in the SELECT clause but excluded from the GROUP BY clause return meaningless values because they’re column values chosen indeterminately from all pre-aggregated rows.

The following query runs in a generically configured MySQL instance without an error. It returns a meaningless key_two column value from the pre-aggregated row set. In the example, the aggregation column counts the unique key_one column values. This behavior makes key_one a determinate value, and key_two an indeterminate value.

1
2
3
4
5

SELECT   key_one
,        key_two
,        SUM(counter)
FROM     grouping
GROUP BY key_one;

You can fix this mixed return set by adding the key_two column to the GROUP BY clause, which would return a determinate set. Alternatively, you can prevent the default behavior for the GROUP BY clause by adding the ONLY_FULL_GROUP_BY mode variable to your SQL_MODE system variable.

After setting the SQL_MODE>GROUP BY must contain all non-aggregated columns. When you make this change to the SQL_MODE system variable, you also disable any queries that use an expression in their GROUP BY clause. Those queries with expressions in the group by will now raise an ERROR 1055 exception, like this:

ERROR 1055 (42000): 'sampledb.grouping.key_two' isn't in GROUP BY

If you want to prevent indeterminate results and don't use expressions in the GROUP BY clause, you can add the ONLY_FULL_GROUP_BY mode to your SQL_MODE system variable. You can do that during a session with the following syntax:

SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));

Or, you can start the mysqld with the following option:

mysqld --sql_mode="sql_mode1,sql_mode2, ... ,sql_mode(n+1)"

A better alternative, is to add it to the my.cnf configuration file on Linux, or my.ini configuration file on Windows. You can add it to this line, which is done at the end of the line.

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"

If you opt for changing it at the server configuration file, you must stop and restart the mysqld process. You can do that on Windows from the command line, like this on Windows provide the service name is mysql. If you've set the Windows service to mysql51, then you need to substitute mysql51 for mysql.

Hidden Column Results

A hidden column result, is a result generated by an expression in the GROUP BY clause. The following is a query that lets you sum transactions by the month name. This is supported by the default behavior of hidden columns.

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

SELECT   il.month AS "MON-YEAR"
,        il.base AS "BASE"
,        il.plus10 AS "10_PLUS"
,        il.plus20 AS "20_PLUS"
FROM    (SELECT   CONCAT(UPPER(SUBSTRING(MONTHNAME(t.transaction_date),1,3)),'-',EXTRACT(YEAR FROM t.transaction_date)) AS month
         ,        MONTH(t.transaction_date) AS sortkey
         ,        LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.0,2)),10,' ') AS base
         ,        LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.1,2)),10,' ') AS plus10
         ,        LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.2,2)),10,' ') AS plus20
         FROM     transaction t
         WHERE    EXTRACT(YEAR FROM t.transaction_date) = 2009
         GROUP BY CONCAT(UPPER(SUBSTRING(MONTHNAME(t.transaction_date),1,3)),'-',EXTRACT(YEAR FROM t.transaction_date))
         ,        MONTH(t.transaction_date)) il
ORDER BY il.sortkey;

When you add the ONLY_FULL_GROUP_BY mode to your SQL_MODE system variable, you disallow this type of behavior and the query would no longer work. It would return the following error:

ERROR 1055 (42000): 'sampledb.t.transaction_date' isn't in GROUP BY

-- Conditionally drop sample table.
SELECT 'DROP TABLE IF EXISTS grouping' AS "Statement";
DROP TABLE IF EXISTS grouping;
 
-- Create sample table.
SELECT 'CREATE TABLE grouping' AS "Statement";
CREATE TABLE grouping
( key_one   CHAR(5)
, key_two   CHAR(5)
, counter   INT UNSIGNED );
 
-- Insert nine rows into the sample table.
SELECT 'INSERT INTO grouping' AS "Statement"
INSERT INTO grouping VALUES
('One','Uno-1',1),('Two','Due-1',2),('Three','Tre-1',3)
('One','Uno-2',1),('Two','Due-2',2),('Three','Tre-2',3)
('One','Uno-3',1),('Two','Due-3',2),('Three','Tre-3',3);
 
-- Query with hidden columns, no enforcement on non-aggregate columns.
SELECT 'SELECT non-aggregates FROM grouping with hidden columns' AS "Statement";
SELECT key_one, key_two, SUM(counter)
FROM   grouping
GROUP BY key_one;
 
-- Check current system mode variable assignment.
SELECT 'SELECT @@sql_mode' AS "Statement";
SELECT @@sql_mode;
 
-- Append the mode to the existing system mode variable.
SELECT 'SET SQL_MODE=(SELECT ...)' AS "Statement";
SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));
 
-- Check modified system mode variable assignment.
SELECT 'SELECT @@sql_mode' AS "Statement";
SELECT @@sql_mode;

A Gauss posted a question on my from last year’s Utah Oracle User’s Group Training Days presentation. If I understood his question correctly, this should help him work with his legacy code. Honestly, as I wrote the example something Bryn Llewellyn said kept banging around in my head, “Just because we can, doesn’t mean we should.” He was speaking of writing poorly engineered code.

Sometimes, we don’t get the opportunity to re-factor existing code. That leaves us with writing wrappers that aren’t pretty or effective. A realization and preface to showing everyone how to accomplish these tasks, and perhaps a watch out warning if you choose this path. I suspect that there may be a better way but I don’t know their code tree.

Here’s the question, as I understand it. They’ve got a library function in PL/SQL that returns a system reference cursor and is principally consumed by an external Java program. This type of architecture is more or less an Adapter OOAD pattern that I wrote about here, over a year and a half ago. The question comes to how to you wrap this approach and make it work in PL/SQL natively too.

The answer depends on some earlier posts because I don’t have a great deal of time to write new examples. It uses a COMMON_LOOKUP table, which is more or less a bunch of small tables grouped into a big table for use in user interaction forms. That way the values don’t get lost in a large code base and are always consistently maintained. These types of tables exist in all major ERP and CRM applications.

The base code for the example is found here, where I discussed how you can effectively use object tables – collections of user-defined object types (Oracle 9iR2 forward if I remember correctly). You can grab the full code at the bottom of the page by clicking the Code Script widget to unfold the code. That code also depends on the Oracle Database 11g PL/SQL Programming downloadable code, which you can download by clicking the link to the zip file location.

Here are the steps to wrap a function that returns a PL/SQL reference cursor so that it can also return a PL/SQL associative array.

1. Create a package specification to hold all the components that are required to manage the process. Assuming that they may have anchored the system reference cursor to something other than a table like a shared cursor, which is a cumbersome implementation design. (I actually chose to exclude this from the book because it’s a stretch as a good coding practice. At least, it is from my perspective. Also, I couldn’t find an example in the Oracle documentation, which led me to believe they didn’t think it’s a great idea either or I could have glossed over it.) You should note that the PL/SQL RECORD, Associative Array (collection), and the REF CURSOR are defined in this package specification.

-- Create a package to hold the PL/SQL record structure.
CREATE OR REPLACE PACKAGE example IS
 
  -- Force cursors to be read as if empty every time.
  PRAGMA SERIALLY_REUSABLE;
 
  -- Package-level record structure that mimics SQL object type.
  TYPE common_lookup_record IS RECORD
  ( common_lookup_id      NUMBER
  , common_lookup_type    VARCHAR2(30)
  , common_lookup_meaning VARCHAR2(255));
 
  -- Package-level collection that mimics SQL object table.
  TYPE common_lookup_record_table IS TABLE OF common_lookup_record
  INDEX BY PLS_INTEGER;
 
  -- Cursor structure to support a strongly-typed reference cursor.
  CURSOR c IS
    SELECT   common_lookup_id
    ,        common_lookup_type
    ,        common_lookup_meaning
    FROM     common_lookup;
 
  -- Package-level strongly-typed system reference cursor.
  TYPE cursor_lookup IS REF CURSOR RETURN c%ROWTYPE;
 
END;
/

2. Write a function to return a strongly typed system reference cursor that’s anchored to a cursor defined in the package. This is fairly straightforward when the package specification is done right. You should notice right away that anchoring the original cursor in the package was a horrible practice because you must repeat it all again in the function. In my opinion, you shouldn’t anchor any system reference cursor explicitly to anything other than a table. The cursor could have used the generic weak cursor data type – SYS_REFCURSOR. Doing so, saves all the extra lines required by a potential shared cursor.

CREATE OR REPLACE FUNCTION get_common_lookup_cursor
( table_name VARCHAR2, column_name VARCHAR2)
RETURN example.cursor_lookup IS
 
  -- Define a local variable of a strongly-typed reference cursor.
  lv_cursor EXAMPLE.CURSOR_LOOKUP;
 
BEGIN
 
  -- Open the cursor from a static cursor
  OPEN lv_cursor FOR
    SELECT common_lookup_id
    ,      common_lookup_type
    ,      common_lookup_meaning
    FROM   common_lookup
    WHERE  common_lookup_table = table_name
    AND    common_lookup_column = column_name;
 
  -- Return the cursor handle.
  RETURN lv_cursor;
 
END;
/

3. Write a wrapper function that takes the reference cursor as a formal parameter and returns an Associative Array. You should note that this can’t be called from a SQL context. You must only use it in a PL/SQL context because system reference cursors are PL/SQL only data types.

CREATE OR REPLACE FUNCTION convert_common_lookup_cursor
( pv_cursor EXAMPLE.CURSOR_LOOKUP) 
RETURN example.common_lookup_record_table IS
 
  -- Declare a local counter variable.
  counter INTEGER := 1;
 
  -- Local PL/SQL-only variable.
  out_record  EXAMPLE.COMMON_LOOKUP_RECORD;
  out_table   EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
BEGIN
 
  -- Grab the cursor wrapper and return values to a PL/SQL-only record collection.
  LOOP
    FETCH pv_cursor INTO out_record;
    EXIT WHEN pv_cursor%NOTFOUND;
 
    -- Assign it one row at a time to an associative array.
    out_table(counter) := out_record;
 
    -- Increment the counter.
    counter := counter + 1;
 
  END LOOP;
 
  -- Return the record collection.
  RETURN out_table;
 
END;
/

4. You can test the program in an anonymous block, like the one below. It defines a local Associative Array variable and then assigns the system reference cursor through the wrapper.

-- Open the session to see output from PL/SQL blocks.
SET SERVEROUTPUT ON
 
DECLARE
 
  -- Define a local associative array.
  process_table  EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
BEGIN
  -- Print title block.
  dbms_output.put_line('Converting a SYS_REFCURSOR to TABLE');
  dbms_output.put_line('---------------------------------------------------');
 
  -- Run the dynamic variables through the cursor generating function and then convert it.
  process_table := convert_common_lookup_cursor(get_common_lookup_cursor('ITEM','ITEM_TYPE'));
 
  -- Read the content of the Associative array.  
  FOR i IN 1..process_table.COUNT LOOP
    dbms_output.put('['||process_table(i).common_lookup_id||']');
    dbms_output.put('['||process_table(i).common_lookup_type||']');
    dbms_output.put_line('['||process_table(i).common_lookup_meaning||']');
  END LOOP;
 
END;
/

I hope this answers Gauss’s question. While writing it, I could envision another question that might pop-up. How do you convert an object table type to a PL/SQL context. It was an omission not to include it in that original post on object table types. Here’s how you wrap an object table type into a PL/SQL scope collection.

You might have guessed. It’s done with another wrapper function. At least this is the easiest way to convert the SQL data type to a PL/SQL data type that I see. If you’ve another approach, a better way, let us know.

CREATE OR REPLACE FUNCTION get_common_lookup_record_table
( table_name  VARCHAR2
, column_name VARCHAR2 )
RETURN example.common_lookup_record_table IS
 
  -- Declare a local counter variable.
  counter INTEGER := 1;
 
  -- Define a dynamic cursor that takes two formal parameters.
  CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS
    SELECT   *
    FROM     TABLE(get_common_lookup_object_table(UPPER(table_name_in),UPPER(table_column_name_in)));
 
  -- A local PL/SQL-only collection variable.
  list EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
BEGIN
 
  -- Grab the cursor wrapper and return values to a PL/SQL-only record collection.
  FOR i IN c(table_name, column_name) LOOP
    list(counter) := i;
    counter := counter + 1;
  END LOOP;
 
  -- Return the record collection.
  RETURN list;
END get_common_lookup_record_table;
/

You can then test this in an anonymous block, like so:

-- Open the session to see output from PL/SQL blocks.
SET SERVEROUTPUT ON
 
DECLARE
 
  -- Declare a local PL/SQL-only collection and assign the value from the function call.
  list EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
  BEGIN
 
  -- Print title block.
  dbms_output.put_line('Converting a SQL Collection to a PL/SQL Collection');
  dbms_output.put_line('---------------------------------------------------');
 
  -- Assign wrapped SQL collection to a PL/SQL-only collection.
  list := get_common_lookup_record_table('ITEM','ITEM_TYPE');
 
  -- Call the record wrapper function.
  FOR i IN 1..list.COUNT LOOP
    dbms_output.put('['||list(i).common_lookup_id||']');
    dbms_output.put('['||list(i).common_lookup_type||']');
    dbms_output.put_line('['||list(i).common_lookup_meaning||']');
  END LOOP;
 
END;
/

As always, I hope this helps somebody without paying a fee for content.

My students are into the upload lab and they’ve encountered the wonderful ORA-30926 error from a MERGE statement. An example of the MERGE for an upload is in this earlier blog post.

This is the wonderful error message, which doesn’t seem to have meaning for many. The key is the non-deterministic where clauses phrase. That phrase means that the the query inside the USING clause returns a non-unique set of rows. The returned rows can’t be matched uniquely against the target table in the ON clause. The ON clause is where the MERGE statment matches the source query’s rows against the target table’s rows.

The problem is very much like when you write what you think is a single-row subquery but find out it’s actually a multiple-row subquery when it return an ORA-01422 error in Oracle.

As a rule, I’ve found that using the DISTINCT operator in the source SELECT statement fixes it most of the time. Alternatively, you need to re-examine the logic of your WHERE clause.

Error: ORA-30926
Text:  Unable TO get a stable SET of rows IN the source TABLES.
--------------------------------------------------------------------
Cause:  A stable SET of rows could NOT be got because of large dml 
        activity OR a non-deterministic WHERE clause. 
 
Action: Remove any non-deterministic WHERE clauses AND reissue the dml.

Hope this helps my students and others …

This is to correct an earlier omission and provide instructions for configuring static and dynamic IP addresses for Virtual Machines runing on VMWare Fusion on Mac OS X. The benefit of static IP address is important for those installing multiple development and test instances of Oracle Databases in virtual environments.

Back in September 2008, I blogged about how to configure the Oracle Client 10g on Mac OS X. It’s been used a lot but in hindsight it could have been organized more effectively. One thing that I noticed (through somebody bringing it to my attention) is that explaining the VMWare Fusion component by itself would have been more helpful then listing the IP ranges for releases through the date of my post. Hopefully, this corrects that omission.

VMWare NAT Configuration

There are only a few steps that you must do. These are they:

1. Read the subnet value from the dhcpd.conf file.
2. Dynamic IP address only require you to set the guest operating system to DHCP.
3. Static IP addresses require you to set the IP address, subnet, default gateway, and preferred DNS server.
4. Add assigned IP address and the guest operating system hostname to your Mac OS X /etc/hosts file.

The next sections gives the details of where to find all the things that you may want to experiment with. Remember if you make an error that damages these configuration files, you have to fix it or re-install VMWare Fusion.

VMWare NAT Files and Configurations

You can find the IP ranges for the Network Address Translation (NAT) here:

/Library/Application Support/VMware Fusion/vmnet8/dhcpd.conf

You can open the file for editing like this:

sudo vi "/Library/Application Support/VMware Fusion/vmnet8/dhcpd.conf"

The file contains the subnet, which I’ve found changes with release. You can configure this file and assign fixed addresses in it. However, you don’t need to install fixed addresses in this file unless you want to reserve addresses in the dynamic range.

The dynamic range is between xxx.xxx.xxx.128 and xxx.xxx.xxx.254. The range of xxx.xxx.xxx.3 to xxx.xxx.xxx.127 is available for static IP addresses. You can set a static IP address inside the native operating system of the VM.

The dhcpd.conf file with it’s instructions, looks like this:

# Configuration file for ISC 2.0 vmnet-dhcpd operating on vmnet8.
#
# This file was automatically generated by the VMware configuration program.
# See Instructions below if you want to modify it.
#
# We set domain-name-servers to make some DHCP clients happy
# (dhclient as configured in SuSE, TurboLinux, etc.).
# We also supply a domain name to make pump (Red Hat 6.x) happy.
#
 
###### VMNET DHCP Configuration. Start of "DO NOT MODIFY SECTION" #####
# Modification Instructions: This section of the configuration file contains
# information generated by the configuration program. Do not modify this
# section.
# You are free to modify everything else. Also, this section must start 
# on a new line 
# This file will get backed up with a different name in the same directory 
# if this section is edited and you try to configure DHCP again.
 
# Written at: 02/18/2010 23:30:54
allow unknown-clients;
default-lease-time 1800;                # default is 30 minutes
max-lease-time 7200;                    # default is 2 hours
 
subnet 172.16.123.0 netmask 255.255.255.0 {
	range 172.16.123.128 172.16.123.254;
	option broadcast-address 172.16.123.255;
	option domain-name-servers 172.16.123.2;
	option domain-name localdomain;
	default-lease-time 1800;                # default is 30 minutes
	max-lease-time 7200;                    # default is 2 hours
	option routers 172.16.123.2;
}
host vmnet8 {
	hardware ethernet 00:50:56:C0:00:08;
	fixed-address 172.16.123.1;
	option domain-name-servers 0.0.0.0;
	option domain-name "";
	option routers 0.0.0.0;
}
####### VMNET DHCP Configuration. End of "DO NOT MODIFY SECTION" #######

Unless you’ve changed the location of your VM repository on your Mac OS X, you can set a fixed-address for the virtual machine. You add the following lines at the bottom of the dhcpd.conf file:

host mclaughlinxp32 {
	hardware ethernet 00:0c:29:55:38:1b;
        fixed-address 172.16.123.21;
}

You pick whichever IP address you’d like to use. You also need to configure the guest opearting system in the VM with that same IP address. You can find the ethernet hardware value in the following file:

~/Documents/Virtual Machines/VMName/VMName.vmx

You can open the file and hunt for it, or simply run this command from the directory where the file exists:

grep ethernet0.generatedAddress *.vmx

As always, I hope this helps some folks.

In my second database class we focus on PL/SQL but we’ve begun highlighting the alternatives in MySQL and SQL Server. A student asked how they could debug runtime variable values in a MySQL Stored Function or Procedure (or subroutines according to some documentation).

In Oracle, we debug with the DBMS_OUTPUT package. Packages, like DBMS_OUTPUT hold related functions and procedures, and are a corollary to System.out.println() in Java.

Before you can see the output at the command-line in Oracle (that is if you’re not using SQL*Developer or Toad), you must set a SQL*Plus environment variable. These variables don’t exist in MySQL or SQL Server command-line tools because they never served the function of a report writer like SQL*Plus.

You enable output display in Oracle by setting this in SQL*Plus:

SQL> SET SERVEROUTPUT ON SIZE 1000000

You can test your anonymous or named block. Since MySQL doesn’t support anonymous named block, the examples using a trivial procedure that prints Hello World! (orginal, right ).

1
2
3
4
5
6
7
8
9
10
11
12

-- Create a procedure in Oracle.
CREATE OR REPLACE PROCEDURE hello_world IS
BEGIN
  -- Print a word without a line return.
  dbms_output.put('Hello ');
  -- Print the rest of the phrase and a line return.
  dbms_output.put_line('World!');
END;
/
 
-- Call the procedure.
EXECUTE hello_world;

It’s seems useless to print the output because it should be evident. MySQL procedures are a bit different because there’s no OR REPLACE syntax. The equivalent to calling the DBMS_OUTPUT package procedures in MySQL is to simply select a string. Now you can do this with or without the FROM dual clause in MySQL, don’t we wish we could likewise do that in Oracle.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- Conditionally drop the procedure.
SELECT 'DROP PROCEDURE hello_world' AS "Statement";
DROP PROCEDURE IF EXISTS hello_world;
 
-- Reset the delimiter to write a procedure.
DELIMITER $$
 
-- Create a procedure in Oracle.
CREATE PROCEDURE hello_world()
BEGIN
  -- Print the phrase and a line return.
  SELECT 'Hello World!';
END;
$$
 
-- Reset the delimiter back to a semicolon to work again.
DELIMITER ;
 
-- Call the procedure.
SELECT 'CALL hello_world' AS "Statement";
CALL hello_world();

Originally, I tried to keep this short but somebody wanted an example in a loop. Ouch, loops are so verbose in MySQL. Since I was modifying this post, it seemed like a good idea to put down some guidelines for successful development too.

Guidelines for Development of Procedures

Declaration Guidelines

The sequencing of components in MySQL procedures is important. Unlike, PL/SQL, there’s no declaration block, declarations must be at the top of the execution block. They also must appear in the following order:

1. Variable declarations must go first, you can assign initial values with the DEFAULT keyword. While not required, you should:

2. After local variables and before handlers, you put your cursor definitions. You should note that MySQL doesn’t support explicit dynamic cursors, which means you can’t define one with a formal signature.
3. Last in your declaration block, you declare your handler events.

Execution Guidelines

1. Variable assignments are made one of two ways:

2. You must assign values from cursors called in a loop into local variables when you want to use the results in nested SQL statements or loops.
3. You must reset looping variables, like the fetched control variable at the end of the loop to reuse the handler variable in subsequent loops.
4. You must assign values to local variables if you want to use them in the exception handler.
5. If you’ve started a transaction, don’t forget to COMMIT your work.

Exception Guidelines

1. Leave out the exception handler until you’ve tested all outcomes, and make sure you document them and add them as potential handlers.
2. When you deploy exception blocks, they’re the last element at the bottom of the exception block.
3. You should consider explicit exception handlers for each error unless the action taken is the same.
4. You should consider grouping all exception handlers when the action taken is the same.
5. You should include a ROLLBACK whenever you’ve performed two or more SQL statements that may modify data.

Below is an example for putting debug code inside a loop.

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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

-- Conditionally drop a sample table.
SELECT 'DROP TABLE IF EXISTS sample' AS "Statement";
DROP TABLE IF EXISTS sample;
 
-- Create a table.
CREATE TABLE sample
( sample_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, sample_msg   VARCHAR(20));
 
-- Insert into sample.
INSERT INTO sample (sample_msg) VALUES
 ('Message #1')
,('Message #2')
,('Message #3');
 
-- Conditionally drop the procedure.
SELECT 'DROP PROCEDURE debug_loop' AS "Statement";
DROP PROCEDURE IF EXISTS debug_loop;
 
-- Reset the delimiter to write a procedure.
DELIMITER $$
 
-- Create a procedure in Oracle.
CREATE PROCEDURE debug_loop()
BEGIN
 
  /* Declare a counter variable. */
  DECLARE lv_counter INT DEFAULT 1;
 
  /* Declare local control loop variables. */
  DECLARE lv_sample_id  INT;
  DECLARE lv_sample_msg VARCHAR(20);
 
  /* Declare a local variable for a subsequent handler. */
  DECLARE duplicate_key INT DEFAULT 0;
  DECLARE fetched INT DEFAULT 0;
 
  /* Declare a SQL cursor fabricated from local variables. */  
  DECLARE sample_cursor CURSOR FOR
    SELECT * FROM sample;
 
  /* Declare a duplicate key handler */
  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Start transaction context. */
  START TRANSACTION;
 
  /* Set savepoint. */  
  SAVEPOINT all_or_none;
 
  /* Open a sample cursor. */
  OPEN sample_cursor;
  cursor_sample: LOOP
 
    /* Fetch a row at a time. */  
    FETCH sample_cursor
    INTO  lv_sample_id
    ,     lv_sample_msg;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_sample; END IF;
 
    -- Print the cursor values.
    SELECT CONCAT('Row #',lv_counter,' [',lv_sample_id,'][',lv_sample_msg,']') AS "Rows";
 
    -- Increment counter variable.
    SET lv_counter = lv_counter + 1;
 
  END LOOP cursor_sample;
  CLOSE sample_cursor;
 
  /* This acts as an exception handling block. */  
  IF duplicate_key = 1 THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT all_or_none;
 
  END IF;
 
END;
$$
 
-- Reset the delimiter back to a semicolon to work again.
DELIMITER ;
 
-- Call the procedure.
SELECT 'CALL debug_loop' AS "Statement";
CALL debug_loop();

This post certainly answers the student question. Hopefully, it also helps other who must migrate Oracle skills to MySQL. Since IBM DB2 has introduced a PL/SQL equivalent, wouldn’t it be nice if Oracle did that for MySQL. Don’t tell me if you think that’s a pipe dream, I’d like to hope for that change.

Somebody posted a quick question about the outcome of defining a table with a bool data type in PHPMyAdmin. They were subsequently surprised when they checked the MySQL database and found that it was actually a tinyint(1). The natural question they had was: “What do you enter – true/false or 1/0?”

I promised to post an answer tonight, and morning counts too. You can enter a true or false because they’re synonyms for a 1 or 0 respectively. TINYINT is the supported data type, and BIT, BOOL, and BOOLEAN are synonyms for the base data type.

Here’s an example in MySQL:

mysql> CREATE TABLE data_type (type bool);
 
mysql> DESCRIBE data_type;
+-------+------------+------+-----+---------+-------+
| FIELD | Type       | NULL | KEY | DEFAULT | Extra |
+-------+------------+------+-----+---------+-------+
| type  | tinyint(1) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
 
mysql> INSERT INTO data_type VALUES (true),(false);
 
mysql> SELECT * FROM data_type;
+------+
| type |
+------+
|    1 |
|    0 |
+------+

I’ve been working on a Basic Aggregation tutorial for my students. I think this might be close to what may benefit them. However, I thought it would be great to put it out there and solicit ideas. If you have some on improving this post, please let me know.

My first take at the post …

This is a lesson on basic aggregation in SQL. Aggregation in SQL means counting, adding, and grouping by results of counts or sums. Aggregation is a critical part of using the SQL language. At a basic level, aggregation includes the COUNT, SUM, AVERAGE, MAX, and MIN aggregation functions; and the ORDER BY, GROUP BY, and HAVING clauses.

You’ll find the setup scripts for these examples at the bottom of this blog page. The best way to use this page is to copy the setup code, run it in your database, and then test the examples as you work though them.

-- Oracle only SQL*Plus formatting command to smooth output display.
SET PAGESIZE 99
 
-- Query ordered by the sets and then the integers in ascending order.
SELECT * FROM ordinal ORDER BY 2, 4;

ORDINAL_ID LIST_SET             LIST_NAME  LIST_VALUE
---------- -------------------- ---------- ----------
         1 Value Set A          Zero                0
         2 Value Set A          One                 1
         3 Value Set A          Two                 2
         4 Value Set A          Three               3
         5 Value Set A          Four                4
         6 Value Set A          Five                5
         7 Value Set A          Six                 6
         8 Value Set A          Seven               7
         9 Value Set A          Eight               8
        10 Value Set A          Nine                9
        11 Value Set A
        12 Value Set B          Zero                0
        13 Value Set B          One                 1
        14 Value Set B          Two                 2
        15 Value Set B          Three               3
        16 Value Set B          Four                4
        17 Value Set B          Five                5
        18 Value Set B          Six                 6
        19 Value Set B          Seven               7
        20 Value Set B          Eight               8
        21 Value Set B          Nine                9
        22 Value Set B

SELECT COUNT(*) FROM ordinal;

  COUNT(*)
----------
        22

SELECT COUNT(list_set) FROM ordinal;

  COUNT(*)
----------
        22

SELECT COUNT(DISTINCT list_set) FROM ordinal;

  COUNT(*)
----------
         2

SELECT COUNT(ALL list_name) AS name_number
,      COUNT(DISTINCT list_value) AS value_number
FROM   ordinal;

NAME_NUMBER VALUE_NUMBER
----------- ------------
         20           10

SELECT SUM(ALL list_value) AS sum_all
,      SUM(DISTINCT list_value) AS sum_distinct
,      AVG(ALL list_value) AS avg_all
,      AVG(DISTINCT list_value) AS avg_distinct
FROM   ordinal;

   SUM_ALL SUM_DISTINCT    AVG_ALL AVG_DISTINCT
---------- ------------ ---------- ------------
        90           45        4.5          4.5

SELECT MIN(ALL list_value) AS min_all
,      MIN(DISTINCT list_value) AS min_distinct
,      MAX(ALL list_value) AS max_all
,      MAX(DISTINCT list_value) AS max_distinct
FROM   ordinal;

   MIN_ALL MIN_DISTINCT    MAX_ALL MAX_DISTINCT
---------- ------------ ---------- ------------
         0            0          9            9

1
2
3
4
5

SELECT   list_set AS grouping_by_column
,        COUNT(*)
,        COUNT(list_value)
FROM     ordinal
GROUP BY list_set;

GROUPING_BY_COLUMN     COUNT(*) COUNT(LIST_VALUE)
-------------------- ---------- -----------------
Value SET A                  11                10
Value SET B                  11                10

1
2
3
4
5
6
7
8

SELECT   list_set AS grouping_by_not_null
,        list_name AS group_by_null_too
,        COUNT(*)
,        COUNT(list_value)
FROM     ordinal
WHERE    list_set = 'Value Set A'
GROUP BY list_set
,        list_name;

GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO   COUNT(*) COUNT(LIST_VALUE)
-------------------- ----------------- ---------- -----------------
Value SET A          Zero                       1                 1
Value SET A          Five                       1                 1
Value SET A          Three                      1                 1
Value SET A          Four                       1                 1
Value SET A          One                        1                 1
Value SET A          Two                        1                 1
Value SET A          Eight                      1                 1
Value SET A          Nine                       1                 1
Value SET A          Seven                      1                 1
Value SET A          Six                        1                 1
Value SET A                                     1                 0

1
2
3
4
5
6
7
8
9

SELECT   list_set AS grouping_by_not_null
,        list_name AS group_by_null_too
,        COUNT(*)
,        COUNT(list_value)
FROM     ordinal
WHERE    list_set = 'Value Set A'
GROUP BY list_set
,        list_name
ORDER BY list_value;

ORDER BY list_value
         *
ERROR at line 9:
ORA-00979: NOT a GROUP BY expression

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

SELECT   list_set AS grouping_by_not_null
,        list_name AS group_by_null_too
,        COUNT(*)
,        COUNT(list_value)
FROM     ordinal
WHERE    list_set = 'Value Set A'
GROUP BY list_set
,        list_name
ORDER BY CASE
           WHEN list_name = 'Zero'  THEN 0
           WHEN list_name = 'One'   THEN 1
           WHEN list_name = 'Two'   THEN 2
           WHEN list_name = 'Three' THEN 3
           WHEN list_name = 'Four'  THEN 4
           WHEN list_name = 'Five'  THEN 5
           WHEN list_name = 'Six'   THEN 6
           WHEN list_name = 'Seven' THEN 7
           WHEN list_name = 'Eight' THEN 8
           WHEN list_name = 'Nine'  THEN 9
         END;

GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO   COUNT(*) COUNT(LIST_VALUE)
-------------------- ----------------- ---------- -----------------
Value SET A          Zero                       1                 1
Value SET A          One                        1                 1
Value SET A          Two                        1                 1
Value SET A          Three                      1                 1
Value SET A          Four                       1                 1
Value SET A          Five                       1                 1
Value SET A          Six                        1                 1
Value SET A          Seven                      1                 1
Value SET A          Eight                      1                 1
Value SET A          Nine                       1                 1
Value SET A                                     1                 0

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

SELECT   ilv.grouping_by_not_null
,        ilv.group_by_null_too
,        ilv.row_count
,        ilv.column_count
FROM    (SELECT   list_set AS grouping_by_not_null
         ,        list_name AS group_by_null_too
         ,        COUNT(*) AS row_count
         ,        COUNT(list_value) AS column_count
         FROM     ordinal
         WHERE    list_set = 'Value Set A'
         GROUP BY list_set
         ,        list_name) ilv LEFT JOIN ordinal o
ON       ilv.grouping_by_not_null = o.list_set
AND      ilv.group_by_null_too = o.list_name
ORDER BY o.list_value;

GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO  ROW_COUNT COLUMN_COUNT
-------------------- ----------------- ---------- ------------
Value Set A          Zero                       1            1
Value Set A          One                        1            1
Value Set A          Two                        1            1
Value Set A          Three                      1            1
Value Set A          Four                       1            1
Value Set A          Five                       1            1
Value Set A          Six                        1            1
Value Set A          Seven                      1            1
Value Set A          Eight                      1            1
Value Set A          Nine                       1            1
Value Set A                                     1            0

SELECT   list_set AS grouping_by_not_null
,        SUM(list_value) AS ordinal_sum
,        AVG(list_value) AS ordinal_avg
,        MIN(list_value) AS ordinal_min
,        MAX(list_value) AS ordinal_max
FROM     ordinal
GROUP BY list_set;

GROUPING_BY_NOT_NULL ORDINAL_SUM ORDINAL_AVG ORDINAL_MIN ORDINAL_MAX
-------------------- ----------- ----------- ----------- -----------
Value SET A                   45         4.5           0           9
Value SET B                   45         4.5           0           9

1
2
3
4
5
6
7
8
9
10

SELECT    list_set AS grouping_by_not_null
,         list_name AS group_by_null_too
,         SUM(list_value) AS ordinal_sum
,         AVG(list_value) AS ordinal_avg
,         MIN(list_value) AS ordinal_min
,         MAX(list_value) AS ordinal_max
FROM      ordinal
WHERE NOT list_set = 'Value Set B'
GROUP BY  list_set
,         list_name;

GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO ORDINAL_SUM ORDINAL_AVG ORDINAL_MIN ORDINAL_MAX
-------------------- ----------------- ----------- ----------- ----------- -----------
Value SET A          Zero                        0           0           0           0
Value SET A          Five                        5           5           5           5
Value SET A          Three                       3           3           3           3
Value SET A          Four                        4           4           4           4
Value SET A          One                         1           1           1           1
Value SET A          Two                         2           2           2           2
Value SET A          Eight                       8           8           8           8
Value SET A          Nine                        9           9           9           9
Value SET A          Seven                       7           7           7           7
Value SET A          Six                         6           6           6           6
Value SET A

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

SELECT   ilv.grouping_by_not_null
,        ilv.group_by_null_too
,        ilv.ordinal_sum
,        ilv.ordinal_avg
,        ilv.ordinal_min
,        ilv.ordinal_max
FROM    (SELECT   list_set AS grouping_by_not_null
         ,        list_name AS group_by_null_too
         ,        SUM(list_value) AS ordinal_sum
         ,        AVG(list_value) AS ordinal_avg
         ,        MIN(list_value) AS ordinal_min
         ,        MAX(list_value) AS ordinal_max
         FROM     ordinal
         WHERE    list_set = 'Value Set B'
         GROUP BY list_set
         ,         list_name) ilv LEFT JOIN ordinal o
ON       ilv.grouping_by_not_null = o.list_set
AND      ilv.group_by_null_too = o.list_name
ORDER BY o.list_value;

GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO ORDINAL_SUM ORDINAL_AVG ORDINAL_MIN ORDINAL_MAX
-------------------- ----------------- ----------- ----------- ----------- -----------
Value SET B          Zero                        0           0           0           0
Value SET B          One                         1           1           1           1
Value SET B          Two                         2           2           2           2
Value SET B          Three                       3           3           3           3
Value SET B          Four                        4           4           4           4
Value SET B          Five                        5           5           5           5
Value SET B          Six                         6           6           6           6
Value SET B          Seven                       7           7           7           7
Value SET B          Eight                       8           8           8           8
Value SET B          Nine                        9           9           9           9
Value SET B

1
2
3
4
5
6
7
8
9
10
11

SELECT   list_set AS grouping_by_not_null
,        list_name AS group_by_null_too
,        SUM(list_value) AS ordinal_sum
,        AVG(list_value) AS ordinal_avg
FROM     ordinal
WHERE    list_set = 'Value Set A'
HAVING   SUM(CASE
               WHEN MOD(list_value,2) = 1 THEN list_value ELSE 0
             END) > 0
GROUP BY list_set
,        list_name;

GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO ORDINAL_SUM ORDINAL_AVG
-------------------- ----------------- ----------- -----------
Value Set A          Five                        5           5
Value Set A          Three                       3           3
Value Set A          One                         1           1
Value Set A          Nine                        9           9
Value Set A          Seven                       7           7

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

SELECT   ilv.grouping_by_not_null
,        ilv.group_by_null_too
,        ilv.ordinal_sum
,        ilv.ordinal_avg
FROM    (SELECT   list_set AS grouping_by_not_null
         ,        list_name AS group_by_null_too
         ,        SUM(list_value) AS ordinal_sum
         ,        AVG(list_value) AS ordinal_avg
         FROM     ordinal
         WHERE    list_set = 'Value Set A'
         HAVING   SUM(CASE
                        WHEN MOD(list_value,2) = 1 THEN list_value ELSE 0
                      END) > 0
         GROUP BY list_set
         ,        list_name) ilv LEFT JOIN ordinal o
ON       ilv.grouping_by_not_null = o.list_set
AND      ilv.group_by_null_too = o.list_name
ORDER BY o.list_value;

GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO ORDINAL_SUM ORDINAL_AVG
-------------------- ----------------- ----------- -----------
Value Set A          One                         1           1
Value Set A          Three                       3           3
Value Set A          Five                        5           5
Value Set A          Seven                       7           7
Value Set A          Nine                        9           9

-- Conditionally drop the table and sequence.
BEGIN
  FOR i IN (SELECT table_name
            FROM   user_tables
            WHERE  table_name = 'ORDINAL') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name;
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
            WHERE  sequence_name = 'ORDINAL_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/
 
-- Create the aggregation sample table and sequence.
CREATE TABLE ordinal
( ordinal_id  NUMBER
, list_set    VARCHAR2(20)
, list_name   VARCHAR2(5)
, list_value  NUMBER);
 
CREATE SEQUENCE ordinal_s1;
 
-- Seeding values in the aggregation sample table.
DECLARE
  -- Define local types.
  TYPE listg IS TABLE OF VARCHAR2(20);
  TYPE listn IS TABLE OF NUMBER;
  TYPE lists IS TABLE OF VARCHAR2(5);
 
  -- Declare local variables.
  groups LISTG := listg('Value Set A','Value Set B');
  labels LISTS := lists('Zero','One','Two','Three','Four','Five','Six','Seven','Eight','Nine');
  valuen LISTN := listn(0,1,2,3,4,5,6,7,8,9);
 
BEGIN
  -- Outer loop sets the group level.
  FOR i IN 1..groups.COUNT LOOP
    -- Inner loop sets the row level.
    FOR j IN 1..labels.COUNT LOOP
      INSERT INTO ordinal
      VALUES (ordinal_s1.NEXTVAL,groups(i),labels(j),valuen(j));
    END LOOP;
 
    -- Insert the null values for each group.
    INSERT INTO ordinal
    VALUES (ordinal_s1.NEXTVAL,groups(i),NULL,NULL);
  END LOOP;
  COMMIT;
END;
/
 
-- Commit the inserts.
COMMIT;

-- Conditionally drop the table.
SELECT 'DROP TABLE ordinal' AS Statement;
DROP TABLE IF EXISTS ordinal;
 
-- Create the table.
SELECT 'CREATE TABLE ordinal' AS Statement;
CREATE TABLE ordinal
( ordinal_id  INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, list_set    VARCHAR(20)
, list_name   VARCHAR(5)
, list_value  INT UNSIGNED);
 
-- Seed the row values.
SELECT 'INSERT INTO ordinal' AS Statement;
INSERT INTO ordinal
VALUES
 (NULL,'Value Set A','Zero','0')
,(NULL,'Value Set A','One','1')
,(NULL,'Value Set A','Two','2')
,(NULL,'Value Set A','Three','3')
,(NULL,'Value Set A','Four','4')
,(NULL,'Value Set A','Five','5')
,(NULL,'Value Set A','Six','6')
,(NULL,'Value Set A','Seven','7')
,(NULL,'Value Set A','Eight','8')
,(NULL,'Value Set A','Nine','9')
,(NULL,'Value Set A',NULL,NULL)
,(NULL,'Value Set B','Zero','0')
,(NULL,'Value Set B','One','1')
,(NULL,'Value Set B','Two','2')
,(NULL,'Value Set B','Three','3')
,(NULL,'Value Set B','Four','4')
,(NULL,'Value Set B','Five','5')
,(NULL,'Value Set B','Six','6')
,(NULL,'Value Set B','Seven','7')
,(NULL,'Value Set B','Eight','8')
,(NULL,'Value Set B','Nine','9')
,(NULL,'Value Set B',NULL,NULL);
 
-- Commit the inserts.
COMMIT;

After I wrote the post for students on the multiple row MERGE statement for an upload through an external table in Oracle, I thought to check how it might be done with MySQL. More or less because I try to keep track of how things are done in several databases.

MySQL’s equivalent to a MERGE statement is an INSERT statement with an ON DUPLICATE KEY clause, which I blogged about a while back. It was interesting to note that you can’t use an INSERT statement with the ON DUPLICATE KEY clause except in a VALUES clause. While you can do multiple row inserts in the VALUES clause in MySQL, like SQL Server or IBM’s DB2, you can’t do it with the ON DUPLICATE KEY clause. There’s also no support for that clause when you attempt to perform an INSERT statement with a subquery.

The workaround was to write a stored procedure with two cursor loops, explicitly pass the values from the cursor to local variables, and then put the local variables in the VALUES clause. On parity, clearly Oracle’s MERGE statement (shown here) is far superior than MySQL’s approach.

Demonstration

Here are the steps to accomplish an import/upload with the INSERT statement and ON DUPLICATE KEY clause. In this example, you upload data from a flat file, or Comma Separated Value (CSV) file to a denormalized table (actually in unnormalized form). This type of file upload transfers information that doesn’t have surrogate key values. You have to create those in the scope of the transformation to the normalized tables.

Step #1 : Position your CSV file in the physical directory

After creating the virtual directory, copy the following contents into a file named kingdom_mysql_import.csv in the C:\Data\Download directory or folder. If you have Windows UAC enabled in Windows Vista or 7, you should disable it before performing this step.

Place the following in the kingdom_mysql_import.csv file. The trailing commas are meaningful in MySQL and avoid problems when reading CSV files.

Narnia, 77600,'Peter the Magnificent',12720320,12920609,
Narnia, 77600,'Edmund the Just',12720320,12920609,
Narnia, 77600,'Susan the Gentle',12720320,12920609,
Narnia, 77600,'Lucy the Valiant',12720320,12920609,
Narnia, 42100,'Peter the Magnificent',15310412,15310531,
Narnia, 42100,'Edmund the Just',15310412,15310531,
Narnia, 42100,'Susan the Gentle',15310412,15310531,
Narnia, 42100,'Lucy the Valiant',15310412,15310531,
Camelot, 15200,'King Arthur',06310310,06861212,
Camelot, 15200,'Sir Lionel',06310310,06861212,
Camelot, 15200,'Sir Bors',06310310,06351212,
Camelot, 15200,'Sir Bors',06400310,06861212,
Camelot, 15200,'Sir Galahad',06310310,06861212,
Camelot, 15200,'Sir Gawain',06310310,06861212,
Camelot, 15200,'Sir Tristram',06310310,06861212,
Camelot, 15200,'Sir Percival',06310310,06861212,
Camelot, 15200,'Sir Lancelot',06700930,06821212,

Step #2 : Connect as the student user

Disconnect and connect as the student user, or reconnect as the student user. The reconnect syntax that protects your password is:

mysql -ustudent -p

Connect to the sampledb database, like so:

mysql> USE sampledb;

Step #3 : Run the script that creates tables and sequences

Copy the following into a create_mysql_kingdom_upload.sql file within a directory of your choice. Then, run it as the student account.

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

-- This enables dropping tables with foreign key dependencies.
-- It is specific to the InnoDB Engine.
SET FOREIGN_KEY_CHECKS = 0; 
 
-- Conditionally drop objects.
SELECT 'KINGDOM' AS "Drop Table";
DROP TABLE IF EXISTS KINGDOM;
 
SELECT 'KNIGHT' AS "Drop Table";
DROP TABLE IF EXISTS KNIGHT;
 
SELECT 'KINGDOM_KNIGHT_IMPORT' AS "Drop Table";
DROP TABLE IF EXISTS KINGDOM_KNIGHT_IMPORT;
 
-- Create normalized kingdom table.
SELECT 'KINGDOM' AS "Create Table";
CREATE TABLE kingdom
( kingdom_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, kingdom_name  VARCHAR(20)
, population    INT UNSIGNED) ENGINE=INNODB;
 
-- Create normalized knight table.
SELECT 'KNIGHT' AS "Create Table";
CREATE TABLE knight
( knight_id             INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, knight_name           VARCHAR(24)
, kingdom_allegiance_id INT UNSIGNED
, allegiance_start_date DATE
, allegiance_end_date   DATE
, CONSTRAINT fk_kingdom FOREIGN KEY (kingdom_allegiance_id)
  REFERENCES kingdom (kingdom_id)) ENGINE=INNODB;
 
-- Create external import table in memory only - disappears after rebooting the mysqld service.
SELECT 'KINGDOM_KNIGHT_IMPORT' AS "Create Table";
CREATE TABLE kingdom_knight_import
( kingdom_name          VARCHAR(20)
, population            INT UNSIGNED
, knight_name           VARCHAR(24)
, allegiance_start_date DATE
, allegiance_end_date   DATE) ENGINE=MEMORY;

Step #4 : Load the data into your target upload table

There a number of things that could go wrong but when you choose LOCAL there generally aren’t any problems. Run the following query from the student account while using the sampledb database, and check whether or not you can access the kingdom_import.csv file.

1
2
3
4
5
6

LOAD DATA LOCAL INFILE 'c:/Data/kingdom_mysql_import.csv'
INTO TABLE kingdom_knight_import
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';

Step #5 : Create the upload procedure

Copy the following into a create_mysql_upload_procedure.sql file within a directory of your choice. You should note that unlike Oracle’s MERGE statement, this is done with the ON DUPLICATE KEY clause and requires actual values not a source query. This presents few options other than a stored routine, known as a stored procedure. As you can see from the code, there’s a great deal of complexity to the syntax and a much more verbose implementation than Oracle’s equivalent PL/SQL.

Then, run it as the student account. As you look at the structure to achieve this simple thing, the long standing complaint about PL/SQL being a verbose language comes to mind. Clearly, stored procedures are new to MySQL but they’re quite a bit more verbose than PL/SQL.

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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150

-- Conditionally drop the procedure.
SELECT 'UPLOAD_KINGDOM' AS "Drop Procedure";
DROP PROCEDURE IF EXISTS upload_kingdom;
 
-- Reset the execution delimiter to create a stored program.
DELIMITER $$
 
-- The parentheses after the procedure name must be there or the MODIFIES SQL DATA raises an compile time exception.
CREATE PROCEDURE upload_kingdom() MODIFIES SQL DATA
 
BEGIN
 
  /* Declare local variables. */
  DECLARE lv_kingdom_id            INT UNSIGNED;
  DECLARE lv_kingdom_name          VARCHAR(20);
  DECLARE lv_population            INT UNSIGNED;
  DECLARE lv_knight_id             INT UNSIGNED;
  DECLARE lv_knight_name           VARCHAR(24);
  DECLARE lv_kingdom_allegiance_id INT UNSIGNED;
  DECLARE lv_allegiance_start_date DATE;
  DECLARE lv_allegiance_end_date   DATE;
 
  /* Declare a handler variables. */
  DECLARE duplicate_key INT DEFAULT 0;
  DECLARE foreign_key   INT DEFAULT 0;
  DECLARE fetched       INT DEFAULT 0;
 
  /* Cursors must come after variables and before event handlers. */
 
  /* Declare a SQL cursor with a left join on the natural key. */  
  DECLARE kingdom_cursor CURSOR FOR
    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; 
 
  /* Declare a SQL cursor with a join on the natural key. */  
  DECLARE knight_cursor CURSOR FOR
    SELECT   kn.knight_id
    ,        kki.knight_name
    ,        k.kingdom_id
    ,        kki.allegiance_start_date AS start_date
    ,        kki.allegiance_end_date AS end_date
    FROM     kingdom_knight_import kki INNER JOIN kingdom k
    ON       kki.kingdom_name = k.kingdom_name
    AND      kki.population = k.population LEFT JOIN knight kn 
    ON       k.kingdom_id = kn.kingdom_allegiance_id
    AND      kki.knight_name = kn.knight_name
    AND      kki.allegiance_start_date = kn.allegiance_start_date
    AND      kki.allegiance_end_date = kn.allegiance_end_date; 
 
  /* Event handlers must always be last in the declaration section. */
 
  /* Declare a duplicate key handler */
  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1;
  DECLARE CONTINUE HANDLER FOR 1216 SET foreign_key = 1;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* ---------------------------------------------------------------------- */
 
  /* Start transaction context. */
  START TRANSACTION;
 
  /* Set savepoint. */  
  SAVEPOINT both_or_none;
 
  /* Open a local cursor. */  
  OPEN kingdom_cursor;
  cursor_kingdom: LOOP
 
    FETCH kingdom_cursor
    INTO  lv_kingdom_id
    ,     lv_kingdom_name
    ,     lv_population;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_kingdom; END IF;
 
    INSERT INTO kingdom
    VALUES
    ( lv_kingdom_id
    , lv_kingdom_name
    , lv_population ) 
    ON DUPLICATE KEY
    UPDATE kingdom_name = lv_kingdom_name;
 
  END LOOP cursor_kingdom;
  CLOSE kingdom_cursor;
 
  /* Reset the continue handler to zero. */
  SET fetched = 0;  
 
  /* Open a local cursor. */  
  OPEN knight_cursor;
  cursor_knight: LOOP
 
    /* Fetch records until they're all read, and a NOT FOUND SET is returned. */  
    FETCH knight_cursor
    INTO  lv_knight_id
    ,     lv_knight_name
    ,     lv_kingdom_allegiance_id
    ,     lv_allegiance_start_date
    ,     lv_allegiance_end_date;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_knight; END IF;
 
    INSERT INTO knight
    VALUES
    ( lv_knight_id
    , lv_knight_name
    , lv_kingdom_allegiance_id
    , lv_allegiance_start_date
    , lv_allegiance_end_date ) 
    ON DUPLICATE KEY
    UPDATE knight_name = lv_knight_name;
 
  END LOOP cursor_knight;
  CLOSE knight_cursor;
 
  /* Reset the continue handler to zero. */
  SET fetched = 0;  
 
  /* ---------------------------------------------------------------------- */
 
  /* This acts as an exception handling block. */  
  IF duplicate_key = 1 OR foreign_key = 1 THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT both_or_none;
 
  ELSE
 
    /* This commits the writes. */
    COMMIT;
 
  END IF;
 
END;
$$
 
-- Reset the delimiter to the default.
DELIMITER ;

Step #6 : Run the upload procedure

You can run the file by calling the stored procedure built by the script. The procedure ensures that records are inserted or updated into their respective tables.

CALL upload_kingdom;

Step #7 : Test the results of the upload procedure

You can test whether or not it worked by running the following queries.

-- Check the kingdom table.
SELECT * FROM kingdom;
SELECT * FROM knight;

It should display the following information:

+------------+--------------+------------+
| kingdom_id | kingdom_name | population |
+------------+--------------+------------+
|          1 | Narnia       |      77600 |
|          2 | Narnia       |      42100 |
|          3 | Camelot      |      15200 |
+------------+--------------+------------+
 
+-----------+-------------------------+-----------------------+-----------------------+---------------------+
| knight_id | knight_name             | kingdom_allegiance_id | allegiance_start_date | allegiance_end_date |
+-----------+-------------------------+-----------------------+-----------------------+---------------------+
|         1 | 'Peter the Magnificent' |                     1 | 1272-03-20            | 1292-06-09          |
|         2 | 'Edmund the Just'       |                     1 | 1272-03-20            | 1292-06-09          |
|         3 | 'Susan the Gentle'      |                     1 | 1272-03-20            | 1292-06-09          |
|         4 | 'Lucy the Valiant'      |                     1 | 1272-03-20            | 1292-06-09          |
|         5 | 'Peter the Magnificent' |                     2 | 1531-04-12            | 1531-05-31          |
|         6 | 'Edmund the Just'       |                     2 | 1531-04-12            | 1531-05-31          |
|         7 | 'Susan the Gentle'      |                     2 | 1531-04-12            | 1531-05-31          |
|         8 | 'Lucy the Valiant'      |                     2 | 1531-04-12            | 1531-05-31          |
|         9 | 'King Arthur'           |                     3 | 0631-03-10            | 0686-12-12          |
|        10 | 'Sir Lionel'            |                     3 | 0631-03-10            | 0686-12-12          |
|        11 | 'Sir Bors'              |                     3 | 0631-03-10            | 0635-12-12          |
|        12 | 'Sir Bors'              |                     3 | 0640-03-10            | 0686-12-12          |
|        13 | 'Sir Galahad'           |                     3 | 0631-03-10            | 0686-12-12          |
|        14 | 'Sir Gawain'            |                     3 | 0631-03-10            | 0686-12-12          |
|        15 | 'Sir Tristram'          |                     3 | 0631-03-10            | 0686-12-12          |
|        16 | 'Sir Percival'          |                     3 | 0631-03-10            | 0686-12-12          |
|        17 | 'Sir Lancelot'          |                     3 | 0670-09-30            | 0682-12-12          |
+-----------+-------------------------+-----------------------+-----------------------+---------------------+

You can rerun the procedure to check that it doesn’t alter any information, then you could add a new knight to test the insertion portion.

While working through examples for my students on uploading data, I thought it would be interesting to demonstrate how to create a re-runnable upload. Especially when chatting with a friend who was unaware that you could use joins inside the source element of a MERGE statement. Naturally, the MERGE statement seemed like the best approach in an Oracle database because with my criteria:

• The source file would not include any surrogate key values.
• The source file would have denormalized record sets with data that should belong to parent and child tables, technically unnormalized form (UNF).
• Primary and foreign key values would be determined on load to the tables.
• There could be a one-to-many relationship between the parent and child tables in the original source.
• Subsequent data sets may replicate data already seeded or not in the tables.
• Avoid any complex PL/SQL structures.

Step #1 : Create a Virtual Directory

You can create a virtual directory without a physical directory but it won’t work when you try to access it. Therefore, you should create the physical directory first. Assuming you’ve created a C:\Data\Download file directory on the Windows platform, you can then create a virtual directory and grant permissions to the student user as the SYS privileged user. The syntax for these steps is:

CREATE DIRECTORY download AS 'C:\Data\Download';
GRANT READ, WRITE ON DIRECTORY download TO student;

If you want more detail on these steps, check this older post on the blog.

Step #2 : Create the Target and External Tables

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

-- Conditionally drop tables and sequences.
BEGIN
  FOR i IN (SELECT table_name
            FROM   user_tables
            WHERE  table_name IN ('KINGDOM','KNIGHT','KINGDOM_KNIGHT_IMPORT')) LOOP 
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
            WHERE  sequence_name IN ('KINGDOM_S1','KNIGHT_S1')) LOOP 
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/
 
-- Create normalized kingdom table.
CREATE TABLE kingdom
( kingdom_id    NUMBER
, kingdom_name  VARCHAR2(20)
, population    NUMBER);
 
-- Create a sequence for the kingdom table.
CREATE SEQUENCE kingdom_s1;
 
-- Create normalized knight table.
CREATE TABLE knight
( knight_id             NUMBER
, knight_name           VARCHAR2(24)
, kingdom_allegiance_id NUMBER
, allegiance_start_date DATE
, allegiance_end_date   DATE);
 
-- Create a sequence for the knight table.
CREATE SEQUENCE knight_s1;
 
-- Create external import table.
CREATE TABLE kingdom_knight_import
( kingdom_name          VARCHAR2(20)
, population            NUMBER
, knight_name           VARCHAR2(24)
, allegiance_start_date DATE
, allegiance_end_date   DATE)
  ORGANIZATION EXTERNAL
  ( TYPE oracle_loader
    DEFAULT DIRECTORY download
    ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
      BADFILE     'DOWNLOAD':'kingdom_import.bad'
      DISCARDFILE 'DOWNLOAD':'kingdom_import.dis'
      LOGFILE     'DOWNLOAD':'kingdom_import.log'
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY "'"
      MISSING FIELD VALUES ARE NULL )
    LOCATION ('kingdom_import.csv'))
REJECT LIMIT UNLIMITED;

Step #3 : Create a Procedure to ensure an all or nothing transaction

The procedure ensures that an all or nothing transaction occurs to both tables. Inside the procedure you have two MERGE statements.

The first MERGE statement uses a LEFT JOIN to ensure that any new kingdom_name will be added to the kingdom table. The kingdom_name and population columns are the natural key in this model. The second MERGE statement uses an INNER JOIN to ensure that knight rows are only inserted when they belong to an existing kingdom_name. Naturally, the primary key capture occurs in this statement and it maps the primary key to the foreign key column in the knight table.

The complete procedure code 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
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 a procedure to wrap the transaction.
CREATE OR REPLACE PROCEDURE upload_kingdom IS 
BEGIN
  -- Set save point for an all or nothing transaction.
  SAVEPOINT starting_point;
 
  -- Insert or update the table, which makes this rerunnable when the file hasn't been updated.  
  MERGE INTO kingdom target
  USING (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) source
  ON (target.kingdom_id = source.kingdom_id)
  WHEN MATCHED THEN
  UPDATE SET kingdom_name = source.kingdom_name
  WHEN NOT MATCHED THEN
  INSERT VALUES
  ( kingdom_s1.NEXTVAL
  , source.kingdom_name
  , source.population);
 
  -- Insert or update the table, which makes this rerunnable when the file hasn't been updated.  
  MERGE INTO knight target
  USING (SELECT   k.kingdom_id
         ,        kki.knight_name
         ,        kki.allegiance_start_date AS start_date
         ,        kki.allegiance_end_date AS end_date
         FROM     kingdom_knight_import kki INNER JOIN kingdom k
         ON       kki.kingdom_name = k.kingdom_name
         AND      kki.population = k.population) source
  ON (target.kingdom_allegiance_id = source.kingdom_id)
  WHEN MATCHED THEN
  UPDATE SET allegiance_start_date = source.start_date
  ,          allegiance_end_date = source.end_date
  WHEN NOT MATCHED THEN
  INSERT VALUES
  ( knight_s1.NEXTVAL
  , source.knight_name
  , source.kingdom_id
  , source.start_date
  , source.end_date);
 
  -- Save the changes.
  COMMIT;
 
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO starting_point;
    RETURN;
END;
/

Step #4 : Test the Process

You can test it by calling the procedure. Rerunning it will demonstrate that it doesn’t violate any of the rules.

EXECUTE upload_kingdom;

As always, I hope this is useful to somebody besides me.

Somebody asked me about how you bind a parameter in mysqli when a multiple character wildcard needs to be next to the variable value. They’d found that it worked when creating a SQL statement by concatenation (string gluing), like this:

$sql = "SELECT item_title FROM item WHERE item_title LIKE '%".$title."%'";

However, they tried to bind the variable following the same pattern, and found that it failed. They used the following type of syntax:

$sql = "SELECT item_title FROM item WHERE item_title LIKE '%?%'";

It raised the following error:

Warning: mysqli_stmt_bind_param() [function.mysqli-stmt-bind-param]: Number of variables doesn't match number of parameters in prepared statement in program_name on line #

The reason is the parser, it expects variables to be independent tokens in the SQL string. You can’t bind a string into the shell of an apostrophe delimited string. You could naturally, make an assignment to the local PHP variable before binding it to the variable, like this:

$title = '%'.$title.'%';

It’s actually a better idea to concatenate the multiple character wildcard operator inside the SQL statement. The correct syntax requires that you use the CONCAT() function. You could reset to use piped concatenation but generally you should avoid that on the MySQL platform (see this post for an explanation of SQL concatenation on Oracle, MySQL, and SQL Server).

This is the required statement when using a MySQL database:

$sql = "SELECT item_title FROM item WHERE item_title LIKE CONCAT('%',?,'%')";

<?php
  // Connection variables.
  define('HOSTNAME',"localhost");
  define('USERNAME',"student");
  define('PASSWORD',"student");
  define('DATABASE',"sampledb");
?>

<?php
  // Set database credentials.
  include_once("MySQLCredentials.inc");
 
  // Return successful attempt to connect to the database.
  if (!$c = @mysqli_connect(HOSTNAME,USERNAME,PASSWORD,DATABASE)) {
    // Print user message.
    print "Sorry! The connection to the database failed. Please try again later.";
 
    // Assign the OCI error and format double and single quotes.
    print mysqli_error();
 
    // Kill the resource.
    die();
  }
  else {
    // Declare input variables.
    $title = (isset($_GET['title'])) ? $_GET['title'] : $title = "RoboCop";
 
    query_insert($title);
  }
 
  // Query results afret an insert.
  function query_insert($title)
  {
    // Return successful attempt to connect to the database.
    if (!$c  = @mysqli_connect(HOSTNAME,USERNAME,PASSWORD,DATABASE)) {
      // Print user message.
      print "Sorry! The connection to the database failed. Please try again later.";
 
      // Assign the OCI error and format double and single quotes.
      print mysqli_error();
 
      // Kill the resource.
      die();
    }
    else {
 
      // Initialize a statement in the scope of the connection.
      $stmt = mysqli_stmt_init($c);
 
      // Declare a SQL SELECT statement returning a CLOB.
      $sql = "SELECT item_title FROM item WHERE item_title LIKE CONCAT('%',?,'%')";
 
      // Prepare statement.
      if (mysqli_stmt_prepare($stmt,$sql)) {
        mysqli_stmt_bind_param($stmt,"s",$title);
 
        // Execute it and print success or failure message.
        if (mysqli_stmt_execute($stmt)) {
 
          // Store result.
          mysqli_stmt_store_result($stmt);
 
          // Bind result to local variable.
          mysqli_stmt_bind_result($stmt,$item_title);
 
          // Open HTML table and print header.
          $out = '<table border="1" cellpadding="3" cellspacing="0">';
          $out .= '<tr>';
          $out .= '<th align="center" style="font-weight:bold;padding:2px;margin:1px;background:#8DB3E2;width:300px">Item Title</th>';
          $out .= '</tr>';
 
          // Read result.
          while (mysqli_stmt_fetch($stmt)) {
            $out .= '<tr>';
            $out .= '<td style="padding:2px;margin:1px;background:#DBE5F1;">'.$item_title.'</td>';
            $out .= '</tr>';
          }
 
          // Close the HTML table.
          $out .= '</table>';
 
 
          // Print the HTML table.
          print $out;
        }
      }
 
      // Free system resources.
      mysqli_stmt_free_result($stmt);
 
      // Disconnect from database.
      mysqli_close($c);
    }
  }
?>