MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Stored Procedures’ Category

Oracle’s Sparse Lists

without comments

Oracle’s PL/SQL Programming Language is really quite nice. I’ve written 8 books on it and still have fun coding in it. One nasty little detail about Oracle’s lists, introduced in Oracle 8 as PL/SQL Tables according their documentation, is they rely on sequential numeric indexes. Unfortunately, Oracle lists support a DELETE method, which can create gaps in the sequential indexes.

Oracle calls a sequence without gaps densely populated and a sequence with gaps sparsely populated. This can cause problems when PL/SQL code inadvertently removes elements at the beginning, end, or somewhere in the middle of the list. That’s because a program can then pass the sparsely populated list as a parameter to another stored function or procedure where the developer may traverse the list in a for-loop. That traversal may raise an exception in a for-loop, like this when it has gaps in the index sequence:

DECLARE
*
ERROR AT line 1:
ORA-01403: no data found
ORA-06512: AT line 20

Oracle’s myriad built-in libraries don’t offer a function to compact a sparsely populated list into a densely populated list. This post provides a compact stored procedure that converts a sparsely populated list to a densely populated list.

The first step to using the compact stored procedure requires that you create an object type in SQL, like this list of 20-character strings:

DROP TYPE list;
CREATE OR REPLACE
  TYPE list IS TABLE OF VARCHAR2(20);
/

Now, you can implement the compact stored procedure by passing the User-Defined Type as it’s sole parameter.

CREATE OR REPLACE
  PROCEDURE compact ( sparse IN OUT LIST ) IS
    /* Declare local variables. */
    iterator  NUMBER;           -- Leave iterator as null.
 
    /* Declare new list. */
    dense     LIST := list();
  BEGIN
    /*
      Initialize the iterator with the starting value, which is
      necessary because the first element of the original list
      could have been deleted in earlier operations. Setting the
      initial iterator value to the first numeric index value
      ensures you start at the lowest available index value.
    */
    iterator := sparse.FIRST;
 
    /* Convert sparsely populated list to densely populated. */
    WHILE (iterator <= sparse.LAST) LOOP
      dense.EXTEND;
      dense(dense.COUNT) := sparse(iterator);
      iterator := sparse.NEXT(iterator);
    END LOOP;
 
    /* Replace the input parameter with the compacted list. */
    sparse := dense;
  END;
/

Before we test the compact stored procedure, let’s create deleteElement stored procedure for our testing:

CREATE OR REPLACE
  PROCEDURE deleteElement ( sparse   IN OUT LIST
                          , element  IN     NUMBER ) IS
  BEGIN
    /* Delete a value. */
    sparse.DELETE(element);
  END;
/

Now, let’s use an anonymous block to test compacting a sparsely populated list into a densely populated list. The test program will remove the first, last, and one element in the middle before printing the sparsely populated list’s index and string values. This test will show you gaps in the remaining non-sequential index values.

After you see the gaps, the test program compacts the remaining list values into a new densely populated list. It then prints the new index values with the data values.

DECLARE
  /* Declare a four item list. */
  lv_strings  LIST := list('one','two','three','four','five','six','seven');
BEGIN
  /* Check size of list. */
  dbms_output.put_line('Print initial list size:  ['||lv_strings.COUNT||']');
  dbms_output.put_line('===================================');
 
  /* Delete a value. */
  deleteElement(lv_strings,lv_strings.FIRST);
  deleteElement(lv_strings,3);
  deleteElement(lv_strings,lv_strings.LAST);
 
  /* Check size of list. */
  dbms_output.put_line('Print modified list size: ['||lv_strings.COUNT||']');
  dbms_output.put_line('Print max index and size: ['||lv_strings.LAST||']['||lv_strings.COUNT||']');
  dbms_output.put_line('===================================');
  FOR i IN 1..lv_strings.LAST LOOP
    IF lv_strings.EXISTS(i) THEN
      dbms_output.put_line('List list index and item: ['||i||']['||lv_strings(i)||']');
    END IF;
  END LOOP;
 
  /* Call a procedure by passing current sparse collection and
     the procedure returns dense collection. */
  dbms_output.put_line('===================================');
  dbms_output.put_line('Compacting list.');
  compact(lv_strings);
  dbms_output.put_line('===================================');
 
  /* Print the new maximum index value and list size. */
  dbms_output.put_line('Print new index and size: ['||lv_strings.LAST||']['||lv_strings.COUNT||']');
  dbms_output.put_line('===================================');
  FOR i IN 1..lv_strings.COUNT LOOP
    dbms_output.put_line('List list index and item: ['||i||']['||lv_strings(i)||']');
  END LOOP;
  dbms_output.put_line('===================================');
END;
/

It produces output, like:

Print initial list size:  [7]
===================================
Print modified list size: [4]
Print max index and size: [6][4]
===================================
List list index and item: [2][two]
List list index and item: [4][four]
List list index and item: [5][five]
List list index and item: [6][six]
===================================
Compacting list.
===================================
Print new index and size: [4][4]
===================================
List list index and item: [1][two]
List list index and item: [2][four]
List list index and item: [3][five]
List list index and item: [4][six]
===================================

You can extend this concept by creating User-Defined Types with multiple attributes, which are essentially lists of tuples (to draw on Pythonic lingo).

Written by maclochlainn

October 4th, 2021 at 11:49 pm

MySQL Shell Parsing

without comments

I’ve been experimenting with the mysqlsh since installing it last week. It’s been interesting. Overall, I’m totally impressed but I did find a problem with how it parses stored procedures.

First thought is always, is it my code? I checked the file by running it as a script file through MySQL Workbench. It ran perfectly in MySQL Workbench but failed repeatedly when run from the mysqlsh utility. Next step, reduce the code to a small test case, retest it, and log a bug if it is replicated. My test case in a test.sql file generates the following errors when run from the mysqlsh utility:

 MySQL  localhost:33060+ ssl  studentdb  SQL > source test.sql
Query OK, 0 rows affected (0.0003 sec)
ERROR: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE PROCEDURE test
( pv_input1  CHAR(12)
, pv_input2  CHAR(19)) MODIFIES SQL ' at line 2
Query OK, 0 rows affected (0.0002 sec)
Query OK, 0 rows affected (0.0003 sec)
ERROR: 1305: PROCEDURE studentdb.test does not exist

The same file generates this series of successful messages when run as a script file from MySQL Workbench:

Preparing...
Importing test.sql...
Finished executing script
Statement
CREATE PROCEDURE test
pv_input1
One
Operation completed successfully

For those who are curious enough to review the test case, here it is:

-- Reset the delimiter so that a semicolon can be used as a statement and block terminator.
DELIMITER $$
 
SELECT 'CREATE PROCEDURE test' AS "Statement";
CREATE PROCEDURE test
( pv_input1  CHAR(12)
, pv_input2  CHAR(19)) MODIFIES SQL DATA
BEGIN
  SELECT CONCAT(pv_input1,', ',pv_input2) AS message;
END;
$$
 
-- Reset the standard delimiter to let the semicolon work as an execution command.
DELIMITER ;
 
-- Call the test procedure.
CALL test('One','Two');

The reply in the bug explained the behavior difference between MySQL Workbench and the MySQL Shell (mysqlsh) environments. MySQL Workbench uses the MySQL client, which supports multiple client statements with the CLIENT_MULTI_STATEMENTS option. Recognizing that, the logging entry SELECT statement should move to a position before setting the DELIMITER, like:

-- Set a label for the log file.
SELECT 'CREATE PROCEDURE test' AS "Statement";
 
-- Reset the delimiter so that a semicolon can be used as a statement and block terminator.
DELIMITER $$
 
CREATE PROCEDURE test
( pv_input1  CHAR(12)
, pv_input2  CHAR(19)) MODIFIES SQL DATA
BEGIN
  SELECT CONCAT(pv_input1,', ',pv_input2) AS message;
END;
$$
 
-- Reset the standard delimiter to let the semicolon work as an execution command.
DELIMITER ;
 
-- Call the test procedure.
CALL test('One','Two');

The new test case only submits one statement at a time. The logging query is submitted by the semicolon, and the test procedure by the double dollar ($$) symbol set.

So, I was correct identifying a parsing behavior difference between MySQL Workbench and MySQL Shell. It appears to be a difference by design but the MySQL Shell documentation fails to explain it can’t manage multiple statements. I hope identifying this saves others time.

It’s also true that the MySQL client software supports TEE and NOTEE to write log files. Unfortunately, MySQL Shell (mysqlsh) doesn’t support the TEE and NOTEE syntax. You can only do minimal logging with the control of standard error (stderr) by using the application and AdminAPI log utilities, which are covered in Chapter 8 of the MySQL Shell 8.0 documentation.

Written by maclochlainn

September 29th, 2020 at 2:45 pm

Postgres Overloaded Routines

without comments

Earlier I showed how to write an anonymous block in PostgreSQL PL/pgSQL to drop routines, like functions and procedures. However, it would only work when they’re not overloaded functions or procedures. The following lets you drop all routines, including overloaded functions and procedures. Overloaded procedures are those that share the same name but have different parameter lists.

Before you can test the anonymous block, you need to create a set of overloaded functions or procedures. You can create a set of overloaded hello procedures with the following syntax:

CREATE FUNCTION hello()
RETURNS text AS
$$
DECLARE
  output  VARCHAR;
BEGIN
  SELECT 'Hello World!' INTO output;
  RETURN output;
END
$$ LANGUAGE plpgsql;
 
CREATE FUNCTION hello(whom text)
RETURNS text AS
$$
DECLARE
  output  VARCHAR;
BEGIN
  SELECT CONCAT('Hello ',whom,'!') INTO output;
  RETURN output;
END
$$ LANGUAGE plpgsql;
 
CREATE FUNCTION hello(id int, whom text)
RETURNS text AS
$$
DECLARE
  output  VARCHAR;
BEGIN
  SELECT CONCAT('[',id,'] Hello ',whom,'!') INTO output;
  RETURN output;
END
$$ LANGUAGE plpgsql;

You can test the overloaded hello function, like so from the videodb schema:

videodb=> SELECT hello();
    hello     
--------------
 Hello World!
(1 ROW)
 
videodb=> SELECT hello('Captain Marvel');
         hello         
-----------------------
 Hello Captain Marvel!
(1 ROW)
 
videodb=> SELECT hello(1,'Captain America');
           hello            
----------------------------
 [1] Hello Captain America!
(1 ROW)

Then, you can query the information_schema to verify that you’ve created a set of overloaded procedures with the following query:

SELECT   proc.specific_schema AS procedure_schema
,        proc.specific_name
,        proc.routine_name AS procedure_name
,        proc.external_language
,        args.parameter_name
,        args.parameter_mode
,        args.data_type
FROM     information_schema.routines proc left join information_schema.parameters args
ON       proc.specific_schema = args.specific_schema
AND      proc.specific_name = args.specific_name
WHERE    proc.routine_schema NOT IN ('pg_catalog', 'information_schema')
AND      proc.routine_type IN ('FUNCTION','PROCEDURE')
ORDER BY procedure_schema
,        specific_name
,        procedure_name
,        args.ordinal_position;

It should return the following:

 procedure_schema | specific_name | procedure_name | external_language | parameter_name | parameter_mode | data_type 
------------------+---------------+----------------+-------------------+----------------+----------------+-----------
 public           | hello_35451   | hello          | PLPGSQL           |                |                | 
 public           | hello_35452   | hello          | PLPGSQL           | whom           | IN             | text
 public           | hello_35453   | hello          | PLPGSQL           | id             | IN             | integer
 public           | hello_35453   | hello          | PLPGSQL           | whom           | IN             | text
(4 rows)

The set session command maps the videodb catalog for the following anonymous block program.

SET SESSION "videodb.catalog_name" = 'videodb';

The following anonymous block lets you get rid of any ordinary or overloaded function and procedure:

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
DO $$
DECLARE
  /* Declare an indefinite length string for SQL statement. */
  sql  VARCHAR;
 
  /* Declare variables to manage cursor return values. */
  row  RECORD;
  arg  VARCHAR;
 
  /* Declare parameter list. */
  list VARCHAR;
 
  /* Declare a routine cursor. */
  routine_cursor CURSOR FOR
    SELECT routine_name
    ,      specific_name
    ,      routine_type
    FROM   information_schema.routines
    WHERE  specific_catalog = current_setting('videodb.catalog_name')
    AND    routine_schema = 'public';
 
  /* Declare a parameter cursor. */
  parameter_cursor CURSOR (cv_specific_name varchar) FOR
    SELECT args.data_type
    FROM   information_schema.parameters args
    WHERE  args.specific_schema = 'public'
    AND    args.specific_name = cv_specific_name;
 
BEGIN
  /* Open the cursor. */
  OPEN routine_cursor;
  <<row_loop>>
  LOOP
    /* Fetch table names. */
    FETCH routine_cursor INTO row;
 
    /* Exit when no more records are found. */
    EXIT row_loop WHEN NOT FOUND;
 
    /* Initialize parameter list. */
    list := '(';
 
    /* Open the parameter cursor. */
    OPEN parameter_cursor(row.specific_name::varchar);
    <<parameter_loop>>
    LOOP
      FETCH parameter_cursor INTO arg;
 
      /* Exit the parameter loop. */
      EXIT parameter_loop WHEN NOT FOUND;
 
      /* Add parameter and delimit more than one parameter with a comma. */
      IF LENGTH(list) > 1 THEN
        list := CONCAT(list,',',arg);
      ELSE
        list := CONCAT(list,arg);
      END IF;
    END LOOP;
 
    /* Close the parameter list. */
    list := CONCAT(list,')');
 
    /* Close the parameter cursor. */
    CLOSE parameter_cursor;
 
    /* Concatenate together a DDL to drop the table with prejudice. */
    sql := 'DROP '||row.routine_type||' IF EXISTS '||row.routine_name||list;
 
    /* Execute the DDL statement. */
    EXECUTE sql;
  END LOOP;
 
  /* Close the routine_cursor. */
  CLOSE routine_cursor;
END;
$$;

Now, you possess the magic to automate cleaning up your schema when you combine this with my earlier post on dynamically dropping tables, sequences, and triggers.

Written by maclochlainn

November 5th, 2019 at 12:11 am

Functions disallow NDS

with 2 comments

My students asked if you could embed an OFFSET x ROWS FETCH NEXT y ROWS ONLY clause in a SQL Server T-SQL user-defined function. The answer is no, it isn’t Oracle (yes, you can do that in Oracle Database 12c with an NDS statement). There’s an example in Chapter 2 of my Oracle Database 12c PL/SQL Programming book if you’re interested. I also demonstrate a different approach to SQL Server T-SQL table functions in this older post. However, an attempt to add the clause to a SQL Server T-SQL function, like this:

CREATE FUNCTION studentdb.getBatch
(@rows   AS INT
,@offset AS INT) RETURNS @output TABLE
( marvel_id    INT 
, avenger_name VARCHAR(30)
, first_name   VARCHAR(20)
, last_name    VARCHAR(20)) AS
BEGIN
  /* Insert the results into the table variable. */
  INSERT @output
  SELECT marvel_id
  ,      avenger_name
  ,      first_name
	,      last_name
  FROM   studentdb.marvel
  OFFSET (@offset - 1) ROWS FETCH NEXT @rows ROWS ONLY;
 
  /* Return the table variable from the function. */  
  RETURN;
END;

Throws the following errors trying to compile the function:

Msg 102, Level 15, State 1, Procedure getBatch, Line 16
Incorrect syntax near '@offset'.
Msg 153, Level 15, State 2, Procedure getBatch, Line 16
Invalid usage of the option NEXT in the FETCH statement.

If you have a strong background in Oracle and can sort through the dynamic SQL syntax for T-SQL, you might try re-writing the function to use the EXEC SP_EXECUTESQL @var; command. That rewrite that attempts to use NDS (Native Dynamic SQL) would look like this:

CREATE FUNCTION studentdb.getBatch
(@rows   AS INT
,@offset AS INT) RETURNS @output TABLE
( marvel_id    INT 
, avenger_name VARCHAR(30)
, first_name   VARCHAR(20)
, last_name    VARCHAR(20)) AS
BEGIN
  DECLARE
    /* Declare a variable for a dynamic SQL statement. */
    @stmt          VARCHAR(400);
 
    /* Assign the SQL statement to a variable. */
    SET @stmt = N'SELECT marvel_id ' +
                N',      avenger_name ' +
                N',      first_name ' +
                N',      last_name ' +
                N'FROM   studentdb.marvel ' +
                N'OFFSET ' + (@offset - 1) + N' ' +
                N'ROWS FETCH NEXT ' + @rows + N' ROWS ONLY;';
  BEGIN
    /* Insert the results into the table variable. */
    INSERT @output
    EXEC sp_executesql @stmt; 
  END;
 
  /* Return the table variable from the function. */  
  RETURN;
END;

Throws the following exception because you can’t use dynamic dispatch inside a T-SQL function:

Msg 443, Level 16, State 14, Procedure getBatch, Line 23
Invalid use of a side-effecting operator 'INSERT EXEC' within a function.

On the other hand you can rewrite the statement with a BETWEEN operator and it works somewhat like an OFFSET and FETCH operation. That refactored function would be written as follows:

CREATE FUNCTION studentdb.getBatch
(@rowa  AS INT
,@rowb AS INT) RETURNS @output TABLE
( marvel_id    INT 
, avenger_name VARCHAR(30)
, first_name   VARCHAR(20)
, last_name    VARCHAR(20)) AS
BEGIN
  /* Insert the results into the table variable. */
  INSERT @output
  SELECT marvel_id
  ,      avenger_name
  ,      first_name
  ,      last_name
  FROM   studentdb.marvel
  WHERE  marvel_id BETWEEN @rowa AND @rowb;
 
  /* Return the table variable from the function. */  
  RETURN;
END;

It doesn’t raise an exception. You can call the table function like this:

SELECT * FROM getBatch(2,3);

It returns the two rows for Iron Man and Black Widow. As always, I hope this helps.

If you want to create the test case, here’s the script you need:

SELECT 'Conditionally drop studentdb.marvel table.' AS "Statement";
IF OBJECT_ID('studentdb.marvel','U') IS NOT NULL
  DROP TABLE studentdb.marvel;
 
SELECT 'Create studentdb.marvel table.' AS "Statement";
CREATE TABLE studentdb.marvel
( marvel_id     INT          NOT NULL IDENTITY(1,1) CONSTRAINT marvel_pk PRIMARY KEY
, avenger_name  VARCHAR(30)  NOT NULL
, first_name    VARCHAR(20)  NOT NULL
, last_name     VARCHAR(20)  NOT NULL);
 
/* Insert the rows. */
INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Hulk','Bruce','Banner');
INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Iron Man','Tony','Stark');
INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Black Widow','Natasha','Romanoff');
INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Thor','Thor','Odinsson');
INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Captain America','Steve','Rogers');
INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Hawkeye','Clint','Barton');
INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Winter Soldier','Bucky','Barnes');
INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Iron Patriot','James','Rhodey');
 
/* Query the contents of the MARVEL table. */
SELECT * FROM studentdb.marvel;

Written by maclochlainn

February 14th, 2015 at 5:20 pm

Capture MySQL Foreign Keys

with one comment

Shantanu asked a follow-up question on my Cleanup a MySQL Schema post from last month. He wanted to know if there was a way to capture foreign keys before removing them. The answer is yes, but how you do it depends on whether the primary key is based on a surrogate key using an auto incrementing sequence of a natural key using descriptive columns.

You can capture foreign keys with a simple query when they’re determined by a single column value. However, this script creates ALTER statements that will fail when a table holds a multiple column foreign key value. The SELECT statement would look like this when capturing all foreign key values in a MySQL Server:

1
2
3
4
5
6
7
8
9
10
11
SELECT   CONCAT('ALTER TABLE',' ',tc.table_schema,'.',tc.table_name,' '
               ,'ADD CONSTRAINT',' fk_',tc.constraint_name,' '
               ,'FOREIGN KEY (',kcu.column_name,')',' '
               ,'REFERENCES',' ',kcu.referenced_table_schema,'.',kcu.referenced_table_name,' ' 
               ,'(',kcu.referenced_column_name,');') AS script
FROM     information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu
ON       tc.constraint_name = kcu.constraint_name
AND      tc.constraint_schema = kcu.constraint_schema
WHERE    tc.constraint_type = 'foreign key'
ORDER BY tc.TABLE_NAME
,        kcu.column_name;

In MySQL 8 forward, you must make a case sensitive comparison, like:

9
WHERE    tc.constraint_type = 'FOREIGN KEY'

You would add a line in the WHERE clause to restrict it to a schema and a second line to restrict it to a table within a schema, like this:

AND      tc.table_schema = 'your_mysql_database'
AND      tc.table_name = 'your_table_name'

Unfortunately, when the primary and foreign keys involve two or more columns you require a procedure and function. The function because you need to read two cursors, and the NOT FOUND can’t be nested in the current deployment of MySQL’s SQL/PSM stored programs. In this example the storedForeignKeys procedure finds the table’s foreign key constraints, and the columnList function adds the column detail. The command_list table stores the commands to restore foreign key constraints.

The command_list table that stores the values is:

CREATE TABLE command_list
( command_list_id  INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, sql_command      VARCHAR(6)    NOT NULL
, sql_object       VARCHAR(10)   NOT NULL
, sql_constraint   VARCHAR(11)
, sql_statement    VARCHAR(768)  NOT NULL);

This is the storedForeignKeys procedure:

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
CREATE PROCEDURE storeForeignKeys
( pv_schema_name  VARCHAR(64)
, pv_table_name   VARCHAR(64))
BEGIN
 
  /* Declare local variables. */
  DECLARE lv_schema_name              VARCHAR(64);
  DECLARE lv_table_name               VARCHAR(64);
  DECLARE lv_constraint_name          VARCHAR(64);
  DECLARE sql_stmt                    VARCHAR(1024);
 
  /* Declare control variable for handler. */
  DECLARE fetched    INT DEFAULT 0;
 
  /* Declare local cursor for foreign key table, it uses null replacement
     because the procedure supports null parameters. When you use null 
     parameters, you get all foreign key values. */
  DECLARE foreign_key_table CURSOR FOR
    SELECT   tc.table_schema
    ,        tc.table_name
    ,        tc.constraint_name
    FROM     information_schema.table_constraints tc
    WHERE    tc.table_schema = IFNULL(lv_schema_name, tc.table_schema)
    AND      tc.table_name = IFNULL(lv_table_name, tc.table_name)
    AND      tc.constraint_type = 'FOREIGN KEY'
    ORDER BY tc.table_name;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Assign parameter values to local variables. */
  SET lv_schema_name := pv_schema_name;
  SET lv_table_name := pv_table_name;
 
  /* Open a local cursor. */  
  OPEN foreign_key_table;
  cursor_foreign_key_table: LOOP
 
    /* Fetch a row into the local variables. */
    FETCH foreign_key_table
    INTO  lv_schema_name
    ,     lv_table_name
    ,     lv_constraint_name;
 
    /* Catch handler for no more rows found from the fetch operation. */
    IF fetched = 1 THEN LEAVE cursor_foreign_key_table; END IF;
 
    /* The nested calls to the columnList function returns the list of columns
       in the foreign key. Surrogate primary to foreign keys can be resolved 
       with a simply query but natural primary to foreign key relationships
       require the list of columns involved in the primary and foreign key.
       The columnList function returns the list of foreign key columns in 
       the dependent table and the list of referenced columns (or the primary
       key columns) in the independent table. */
    SET sql_stmt := CONCAT('ALTER TABLE ',' ',lv_schema_name,'.',lv_table_name,' '
                          ,'ADD CONSTRAINT ',lv_constraint_name,' '
                          ,'FOREIGN KEY (',columnList(lv_schema_name,lv_table_name,lv_constraint_name));
 
    /* Record the SQL statements. */
    INSERT INTO command_list
    ( sql_command
    , sql_object
    , sql_constraint
    , sql_statement )
    VALUES
    ('ALTER'
    ,'TABLE'
    ,'FOREIGN KEY'
    , sql_stmt );
 
  END LOOP cursor_foreign_key_table;
  CLOSE foreign_key_table;  
 
END;
$$

This is the columnList function:

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
CREATE FUNCTION columnList
( pv_schema_name      VARCHAR(64)
, pv_table_name       VARCHAR(64)
, pv_constraint_name  VARCHAR(64)) RETURNS VARCHAR(512)
BEGIN
 
  /* Declare local variables. */
  DECLARE lv_schema_name              VARCHAR(64);
  DECLARE lv_table_name               VARCHAR(64);
  DECLARE lv_constraint_name          VARCHAR(64);
  DECLARE lv_column_count             INT UNSIGNED;
  DECLARE lv_column_name              VARCHAR(64);
  DECLARE lv_column_list              VARCHAR(512);
  DECLARE lv_column_ref_list          VARCHAR(64);
  DECLARE lv_referenced_table_schema  VARCHAR(64);
  DECLARE lv_referenced_table_name    VARCHAR(64);
  DECLARE lv_referenced_column_name   VARCHAR(64);
  DECLARE lv_return_string            VARCHAR(768);
 
  /* Declare control variable for handler. */
  DECLARE fetched    INT DEFAULT 0;
 
  /* Declare local cursor for foreign key column. */
  DECLARE foreign_key_column CURSOR FOR
    SELECT   kcu.column_name
    ,        kcu.referenced_table_schema
    ,        kcu.referenced_table_name
    ,        kcu.referenced_column_name
    FROM     information_schema.key_column_usage kcu
    WHERE    kcu.referenced_table_schema = lv_schema_name
    AND      kcu.table_name = lv_table_name
    AND      kcu.constraint_name = lv_constraint_name
    ORDER BY kcu.column_name;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Assign parameter values to local variables. */
  SET lv_schema_name := pv_schema_name;
  SET lv_table_name := pv_table_name;
  SET lv_constraint_name := pv_constraint_name;
 
  /* Set the first column value. */
  SET lv_column_count := 1;
 
  /* Open the nested cursor. */
  OPEN  foreign_key_column;
  cursor_foreign_key_column: LOOP
 
    /* Fetch a row into the local variables. */    
    FETCH foreign_key_column
    INTO  lv_column_name
    ,     lv_referenced_table_schema
    ,     lv_referenced_table_name
    ,     lv_referenced_column_name;
 
    /* Catch handler for no more rows found from the fetch operation. */
    IF fetched = 1 THEN LEAVE cursor_foreign_key_column; END IF;
 
    /* Initialize the column list or add to it. */
    IF lv_column_count = 1 THEN
      SET lv_column_list := lv_column_name;
      SET lv_column_ref_list := lv_referenced_column_name;
 
      /* Increment the counter value. */
      SET lv_column_count := lv_column_count + 1;
    ELSE
      SET lv_column_list := CONCAT(lv_column_list,',',lv_column_name);
      SET lv_column_ref_list := CONCAT(lv_column_ref_list,',',lv_referenced_column_name);
    END IF;
 
  END LOOP cursor_foreign_key_column;
  CLOSE foreign_key_column;
 
  /* Set the return string to a list of columns. */
  SET lv_return_string :=
        CONCAT(lv_column_list,')',' '
              ,'REFERENCES',' ',lv_referenced_table_schema,'.',lv_referenced_table_name,' ' 
              ,'(',lv_column_ref_list,');');
 
  RETURN lv_return_string;
END;
$$

You can call the procedure with a schema and table name, and you’ll get the foreign keys from just that table. You can create the following parent and child tables to test how multiple column foreign keys work in the script (provided because most folks use surrogate keys):

CREATE TABLE parent
( first_name  VARCHAR(20)  NOT NULL DEFAULT ''
, last_name   VARCHAR(20)  NOT NULL DEFAULT ''
, PRIMARY KEY (first_name, last_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE child
( child_name  VARCHAR(20)  NOT NULL
, first_name  VARCHAR(20)  DEFAULT NULL
, last_name   VARCHAR(20)  DEFAULT NULL
, PRIMARY KEY (child_name)
, KEY fk_parent(first_name, last_name)
, CONSTRAINT fk_parent FOREIGN KEY (first_name, last_name)
  REFERENCES parent (first_name, last_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

You call the storeForeignKeys procedure for the child table with this syntax:

CALL storeForeignKeys('studentdb', 'child');

You call the storeForeignKeys procedure for all tables in a schema with this syntax:

CALL storeForeignKeys('studentdb', null);

While unlikely you’ll need this, the following calls the storeForeignKeys procedure for all tables in all schemas:

CALL storeForeignKeys(null, null);

You can export the command sequence with the following command to a script file:

SELECT sql_statement
INTO OUTFILE 'c:/Data/MySQL/apply_foreign_keys.sql'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
FROM command_list;

While preservation of tables and foreign keys is best managed by using a tool, like MySQL Workbench, it’s always handy to have scripts to do specific tasks. I hope this helps those looking for how to preserve foreign keys. You also can find a comprehensive treatment on how to write SQL/PSM code in Chapter 14 of my Oracle Database 11g and MySQL 5.6 Developer Handbook.

Written by maclochlainn

March 17th, 2014 at 11:27 pm

Signal from a procedure

without comments

As I review with my students, a stored function works like a standalone program, while a stored procedure runs in the scope of another program unit. For example, you can compare the result of a function as an expression in an IF statement, like:

  IF add_numbers(1,3) > 3 THEN
    ...
  ELSE
    ...
  END IF;

You can’t call procedures inside an IF statement, but you can call the procedure and use a single OUT-mode (pass-by-reference) parameter from the procedure in a subsequent IF statement. You can implement a a wait procedure like that with the following example.

The example first creates two tables, the road_runner and coyote tables:

-- Drop road_runner table if exists.
DROP TABLE IF EXISTS road_runner;
 
-- Create roadrunner table.
CREATE TABLE road_runner
( road_runner_id    int unsigned  auto_increment primary key
, road_runner_text  varchar(20)
, CONSTRAINT road_runner_nk UNIQUE (road_runner_text));
 
-- Drop coyote table if exists.
DROP TABLE IF EXISTS coyote;
 
-- Create coyote table.
CREATE TABLE coyote
( coyote_id       int unsigned  auto_increment primary key
, coyote_text     varchar(20)
, road_runner_id  int unsigned
, CONSTRAINT coyote_nk UNIQUE (coyote_text));

The following creates a procedure that:

  • Writes data to two tables when the values are unique, returning a value of zero when it works.
  • Writes data to neither table when the values to either table are non-unique, returning a value of one when it fails.

The procedure uses a 0 as a false value and a 1 as a true value. The use of a 0 and 1 for truth is a consistent approach for languages where they don’t support a Boolean data type.

-- Change the delimiter to a "$$"
DELIMITER $$
 
-- Drop the paired procedure.
DROP PROCEDURE IF EXISTS two_table$$
 
-- Create the paired procedure.
CREATE PROCEDURE two_table
(IN    pv_road_runner_text  varchar(20)
,IN    pv_coyote_text       varchar(20)
,  OUT pv_confirm_it        int)
BEGIN
  /* Declare a variable to hold a sequence value for an 
     auto incrementing value. */
  DECLARE lv_road_runner_id  int unsigned;
 
  /* Declare a condition variable for attempting to write
     a non-unique record to a table. */
  DECLARE duplicate CONDITION FOR 1062;
 
  /* Declare an event handler for a duplicate condition 
     variable, rollback transaction, and set 1 as a false
     condition. */
  DECLARE EXIT HANDLER FOR duplicate
    BEGIN
      ROLLBACK to all_or_none;
      SET pv_confirm_it = 1;
    END;
 
  /* Start the transaction. */
  START TRANSACTION;
 
  /* Set the save point for a multiple table transaction. */
  SAVEPOINT all_or_none;
 
  /* Insert into road runner table. */
  INSERT INTO road_runner
  (road_runner_text)
  VALUES  
  (pv_road_runner_text);
 
  /* Capture the auto incrementing sequence value as a 
     local variable. */
  SET lv_road_runner_id := last_insert_id();
 
  /* Insert into the coyote table. */
  INSERT INTO coyote
  (coyote_text
  ,road_runner_id)
  VALUES  
  (pv_coyote_text
  ,lv_road_runner_id);
 
  /* Commit the record. */
  COMMIT;
 
  /* Set the control variable to a true value. */
  SET pv_confirm_it := 0;
 
END;
$$
 
DELIMITER ;

You can test the pass-by-reference procedure with the following code:

-- Set a control variable with a null value.
SET @sv_control := null;
 
-- Query the results from a join between the road_runner and coyote tables.
SELECT   *
FROM     road_runner r INNER JOIN coyote c
ON       r.road_runner_id = c.road_runner_id;
 
-- Call the two_table procedure with unique results.
CALL two_table('Road Runner 1','Coyote 1',@sv_control);
 
-- Query the control variable result from the second call to the
-- two_table procedure.
SELECT @sv_control AS "1st Insert";
 
-- Reset the value for a second test.
SET @sv_control := 0;
 
-- Call teh two_table procedure with unique results.  
CALL two_table('Road Runner 2','Coyote 1',@sv_control);  
 
-- Query the results from a join between the road_runner and coyote tables.  
SELECT   *
FROM     road_runner r INNER JOIN coyote c
ON       r.road_runner_id = c.road_runner_id;
 
-- Query the control variable result from the second call to the
-- two_table procedure.
SELECT   @sv_control AS "2nd Insert";

I hope this helps anybody trying to implement a pass-by-reference procedure with a control variable. You can find other examples in Chapter 14 of Oracle Database 11g and MySQL 5.6 Developer Handbook (pp. 446-449 and 450-451).

Written by maclochlainn

February 16th, 2014 at 5:22 pm

Cleanup a MySQL Schema

with 7 comments

My students required way to remove all their tables, views, and constraints from a MySQL database (or the alias schema). Since they’re using referential or foreign key constraints, I also wrote one procedure to drop all foreign key constraints from a database. There’s also another to drop views. The final stored procedure calls the procedure that drops foreign keys, then calls the procedure to drop views before dropping all the tables.

Here’s the dropForeignKeys stored procedure, but if you want to capture ALTER statements that add these back later please check my follow-up Capture MySQL Foreign Keys post.

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
-- Provide a log file debugging statement.
SELECT 'DROP PROCEDURE IF EXISTS dropForeignKeys';
 
-- Conditionally drop the procedure.
DROP PROCEDURE IF EXISTS dropForeignKeys;
 
-- Provide a log file debugging statement.
SELECT 'CREATE PROCEDURE dropForeignKeys';
 
-- Change delimiter to create procedure.
DELIMITER $$
 
-- Create procedure.
CREATE PROCEDURE dropForeignKeys
( pv_database          VARCHAR(64)
, pv_referenced_table  VARCHAR(64))
BEGIN
 
  /* Declare local statement variables. */
  DECLARE lv_stmt VARCHAR(1024);
 
  /* Declare local cursor variables. */
  DECLARE lv_table_name       VARCHAR(64);
  DECLARE lv_constraint_name  VARCHAR(64);
 
  /* Declare control variable for handler. */
  DECLARE fetched       INT DEFAULT 0;
 
  /* Declare local cursor. */
  DECLARE foreign_key_cursor CURSOR FOR
    SELECT   rc.table_name
    ,        rc.constraint_name
    FROM     information_schema.referential_constraints rc
    WHERE    constraint_schema = IFNULL(pv_database,database())
    AND      referenced_table_name = pv_referenced_table
    ORDER BY rc.table_name
    ,        rc.constraint_name;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Open a local cursor. */  
  OPEN foreign_key_cursor;
  cursor_foreign_key: LOOP
 
    FETCH foreign_key_cursor
    INTO  lv_table_name
    ,     lv_constraint_name;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_foreign_key; END IF;
 
    /* Set a SQL statement by using concatenation. */
    SET @SQL := CONCAT('ALTER TABLE',' ',lv_table_name,' ','DROP FOREIGN KEY',' ',lv_constraint_name);
 
    /* Prepare, run, and deallocate statement. */
    PREPARE lv_stmt FROM @SQL;
    EXECUTE lv_stmt;
    DEALLOCATE PREPARE lv_stmt;
 
  END LOOP cursor_foreign_key;
  CLOSE foreign_key_cursor;  
 
END;
$$
 
-- Reset delimiter to run SQL statements.
DELIMITER ;

Here’s the dropViews stored procedure:

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
-- Provide a log file debugging statement.
SELECT 'DROP PROCEDURE IF EXISTS dropViews';
 
-- Conditionally drop the procedure.
DROP PROCEDURE IF EXISTS dropViews;
 
-- Provide a log file debugging statement.
SELECT 'CREATE PROCEDURE dropViews';
 
-- Change delimiter to create procedure.
DELIMITER $$
 
-- Create procedure.
CREATE PROCEDURE dropViews
( pv_database  VARCHAR(64))
BEGIN
 
  /* Declare local statement variables. */
  DECLARE lv_stmt VARCHAR(1024);
 
  /* Declare local cursor variables. */
  DECLARE lv_view_name       VARCHAR(64);
 
  /* Declare control variable for handler. */
  DECLARE fetched       INT DEFAULT 0;
 
  /* Declare local cursor. */
  DECLARE view_cursor CURSOR FOR
    SELECT   v.table_name
    FROM     information_schema.views v
    WHERE    table_schema = IFNULL(pv_database, database())
    ORDER BY v.table_name;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Open a local cursor. */  
  OPEN view_cursor;
  cursor_view: LOOP
 
    FETCH view_cursor
    INTO  lv_view_name;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_view; END IF;
 
    /* Set a SQL statement by using concatenation. */
    SET @SQL := CONCAT('DROP VIEW',' ',lv_view_name);
 
    /* Prepare, run, and deallocate statement. */
    PREPARE lv_stmt FROM @SQL;
    EXECUTE lv_stmt;
    DEALLOCATE PREPARE lv_stmt;
 
  END LOOP cursor_view;
  CLOSE view_cursor;  
 
END;
$$
 
-- Reset delimiter to run SQL statements.
DELIMITER ;

Here’s the dropTables stored procedure:

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
CREATE PROCEDURE dropTables
( pv_database  VARCHAR(64))
BEGIN
 
  /* Declare local statement variables. */
  DECLARE lv_stmt  VARCHAR(1024);
 
  /* Declare local cursor variables. */
  DECLARE lv_table_name  VARCHAR(64);
 
  /* Declare control variable for handler. */
  DECLARE fetched       INT DEFAULT 0;
 
  /* Declare local cursor. */
  DECLARE table_cursor CURSOR FOR
    SELECT   t.table_name
    FROM     information_schema.tables t
    WHERE    table_schema = IFNULL(pv_database, database())
    ORDER BY t.table_name;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Drop the views. */
  CALL dropViews(null);
 
  /* Open a local cursor. */  
  OPEN table_cursor;
  cursor_table: LOOP
 
    FETCH table_cursor
    INTO  lv_table_name;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_table; END IF;
 
    /* Drop the tables. */
    CALL dropForeignKeys(null,lv_table_name);
 
    /* Set a SQL statement by using concatenation. */
    SET @SQL := CONCAT('DROP TABLE',' ',lv_table_name);
 
    /* Prepare, run, and deallocate statement. */
    PREPARE lv_stmt FROM @SQL;
    EXECUTE lv_stmt;
    DEALLOCATE PREPARE lv_stmt;
 
  END LOOP cursor_table;
  CLOSE table_cursor;  
 
END;
$$
 
-- Reset delimiter to run SQL statements.
DELIMITER ;

You put these in a rerunnable script, run it, and then call the dropTables stored procedure. You can pass a database (or schema) name or a null value. When you pass a null value, it uses the current database, like:

CALL dropTables(null);

As always, I hope this helps others.

Written by maclochlainn

February 7th, 2014 at 1:38 am

SQL Injection Risks

with 11 comments

While I tried to deflect how you perform SQL Injection attacks against a MySQL procedure, my students requested that I post examples of what to do to avoid SQL injection, and what not to do to invite attacks. The best practice to avoid SQL injection attacks is too always bind inputs to data types, and avoid providing completely dynamic WHERE clauses.

Here’s the correct way to dynamically generate a result from a MySQL Stored Procedure:

CREATE PROCEDURE hello (IN pv_input VARCHAR(50))
BEGIN
  SELECT sample_id
  ,      sample_name
  FROM   sample
  WHERE  sample_name = pv_input;
END;
$$

A call to this hello procedure will only return the row or rows where the pv_input value matches the sample_name column value. Any attempt to exploit it like the one below fails.

CALL hello('\'Harriet\' OR 1 = 1');

It fails because there’s no matching 'Harriet' OR 1 = 1 in the table’s sample_name column. However, it works well when we submit 'Harriet' by herself, without the intended SQL inject phrase “OR 1 = 1“, as you can see:

+-----------+-------------+
| sample_id | sample_name |
+-----------+-------------+
|         2 | Harriet     |
+-----------+-------------+
1 row in set (0.00 sec)

There are two approaches that you should never put in your code because they can be readily exploited unless you carefully parse the incoming string parameter. The problem in both cases is causes by concatenation rather than binding. The first example is extremely unlikely as an error but possible.

CREATE PROCEDURE hello (IN pv_input VARCHAR(50))
BEGIN
  SET @sql := CONCAT('SELECT sample_id, sample_name FROM sample WHERE sample_name = ',pv_input);
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
END;
$$

The last hello procedure using concatenation and a prepared statement is vulnerable to SQL injection. A call like the following would return all two rows in the sample table.

CALL hello('\'Juliette\' OR 1 = 1');

It would display:

+-----------+-------------+
| sample_id | sample_name |
+-----------+-------------+
|         1 | Hank        |
|         2 | Harriet     |
+-----------+-------------+
2 rows in set (0.00 sec)

While there’s no sample_name value of 'Juliette', the “OR 1 = 1” is true. Therefore, the SELECT statement filters out nothing and returns all the data from the table. It’s probably clear you’d never do this type of prepared statement inside a stored procedure, but most SQL Injection attacks exploit your scripting language implementation. Unfortunately, bad coding practices can infrequently expose this type of vulnerability; and they typically occur when a junior programmers is following a bad coding example.

A solution with the WHERE clause as part of the parameter would look like this:

CREATE PROCEDURE hello (IN pv_where VARCHAR(50))
BEGIN
  SELECT sample_id
  ,      sample_name
  FROM   sample
  pv_where;
END;
$$

The modified call:

CALL hello('WHERE sample_name = \'Juliette\' OR 1 = 1');

returns all rows from the table.

A solution without the WHERE clause as part of the parameter would look like the following but it fails during runtime and returns no rows [Updated in response to comment]. The failure has nothing to do with the comment’s dialog on the CONCAT function, which also added nothing to the example once I tested it.

CREATE PROCEDURE hello (IN pv_where VARCHAR(50))
BEGIN
  SELECT sample_id
  ,      sample_name
  FROM   sample
  WHERE  pv_where;
END;
$$

It’s simply works only when you provide a “1 = 1” or other comparison without embedded apostrophes (‘) but fails with embedded apostrophes. That means the following statement fails:

CALL hello('sample_name = \'Juliette\' OR 1 = 1');

but this SQL injection statement works:

CALL hello('1 = 1');

returns all rows from the table.

This example, when you omit the white space also works with embedded strings or numeric operands and an operator:

CREATE PROCEDURE hello (IN pv_where VARCHAR(50))
BEGIN
  SELECT sample_id
  ,      sample_name
  FROM   sample
  WHEREpv_where;
END;
$$

It returns all rows with a call like this:

CALL hello('sample_name = \'Juliette\' OR 1 = 1');

My take initially was that it might be a bug, and I logged one (Bug 68903). That’s was a dumb thing to do because WHEREpv_where simply becomes a table alias in the query.

In conclusion, the first example is a good practice. The other two should never exist! Well, they shouldn’t exist unless you’re parsing the web form inputs vigilantly.

Hope this helps those trying to understand how to avoid SQL injection attacks. Always try to solve dynamic SQL statement problems by binding variables into statements.

Written by maclochlainn

April 5th, 2013 at 8:35 pm

MySQL Database Triggers

with 5 comments

One of the students wanted an equivalent example to an Oracle DML trigger sample that replaces a white space in a last name with a dash for an INSERT statement. Apparently, the MySQL trigger example in the Oracle Database 11g and MySQL 5.6 Developer Handbook was a bit long. I have to agree with that because the MySQL DML trigger demonstrated cursors and loops in the trigger code.

Triggers can be statement- or row-level actions. Although some databases let you define statement-level triggers, MySQL doesn’t support them. MySQL only supports row-level triggers. Row-level triggers support critical or non-critical behaviors. Critical behavior means the trigger observes an insert, update, or delete that must be stopped, which means it raises an error. Non-critical behavior means the trigger observes a DML statement and logs it or implements a change during the context of the DML activity.

The first example shows you non-critical behavior. It observes an attempt to enter a two-part last name, and replaces the white space with a dash (you can find help on MySQL Regular Expressions in this other post). This means the trigger ensures compliance on how names are entered in the database, which should be protected in the web form (through JQuery or JavaScript) and the database.

DELIMITER $$
DROP TRIGGER IF EXISTS contact_insert$$
 
CREATE TRIGGER contact_insert
BEFORE INSERT ON contact
FOR EACH ROW
BEGIN
  IF new.last_name REGEXP '^.* .*$' THEN
    SET new.last_name := REPLACE(new.last_name,' ','-');
  END IF;
END;
$$
 
DELIMITER ;

The problem with implementing a non-critical trigger is that the database performs the work but clerks entering the data don’t learn the business rule. A critical trigger simply disallows non-conforming data entry. The next program shows a critical behavior with an UPDATE statement row-level trigger. After all, won’t a data entry clerk update the entry with a white space after the INSERT statement didn’t?

Yes, that was a rhetorical question. Spelling out the business rule in the UPDATE statement row-level trigger should educate the persistent errant behavior. While letting the INSERT statement row-level trigger manage the behavior probably saves time for most end-users who make a casual mistake.

DELIMITER $$
DROP TRIGGER IF EXISTS contact_update$$
 
CREATE TRIGGER contact_update
BEFORE UPDATE ON contact
FOR EACH ROW
BEGIN
  IF new.last_name REGEXP '^.* .*$' THEN
    SIGNAL SQLSTATE '42000';
  END IF;
END;
$$
 
DELIMITER ;

Somebody wanted to know why I choose SQLSTATE 42000. That’s because it signals an error in the SQL statement, and that’s the closest existing SQLSTATE to the actual behavior that exists. Moreover, the error identified by the critical trigger signals non-compliance with the application’s SQL standards that protects the data.

Hopefully, this helps somebody looking for a MySQL database trigger example that raises an exception. The example works with MySQL 5.5 forward because the critical trigger uses the SIGNAL feature, which was introduced in MySQL 5.5. Another article shows you how to leverage MyISAM tables to create a logging mechanism for critical event triggers, and you click this link to the MySQL Triggers with Logging blog entry.

Written by maclochlainn

November 22nd, 2012 at 11:32 pm

Reset sequence START WITH

with 3 comments

Two things contributed to this post. One was a student question about the difference between the MAX_VALUE of a sequence and the actual sequence value. The other was a comment on an earlier post addressing an NDS approach to resetting sequences.

The student wanted to understand why there were gaps in the sequence, since they created it with the default values, like this:

CREATE SEQUENCE sequence_name;

A default sequence in an Oracle database sets the cache value to 20, which means you get gaps every time a new set is allocated. This Ask Tom question and answer holds that information.

The blog comment was on an NDS statement post. I wasn’t quite sure what the comment wanted because there really wasn’t a question or a complete code example. Having demonstrated how to dynamically drop and recreate a new sequence without a gap in a comment reply, I thought that was adequate.

Having pointed the student to the Ask Tom column and my answer, he wanted to know how to create a stored procedure to reset sequences dynamically. It took me a couple weeks to get back to this but here’s the procedure. The tricky element of the procedure is the “no binding values allowed” restriction placed on NDS statements that process DDL statements.

The procedure uses two local procedures and two local functinons. The local find_sequence finds a sequence name in the schema, and find_next_sequence function returns the next value. The local create_sequence and drop_sequence procedures respectively isolate the logic for dynamic drops and creates of the sequence.

Warning: The restriction with this design assumes that the table name and sequence name are linked by using the table name and an _ID suffix.

-- Declare an autonomous procedure to drop sequences.
CREATE OR REPLACE PROCEDURE reset_sequence
( pv_table_name     VARCHAR2
, pv_sequence_name  VARCHAR2
, pv_cache_value    NUMBER DEFAULT 0 ) IS
 
  -- Declares stubs to remove forward reference limits.
  PROCEDURE create_sequence ( pv_sequence_name  VARCHAR2, pv_cache_value  NUMBER );
  PROCEDURE drop_sequence ( pv_sequence_name  VARCHAR2 );
  FUNCTION find_sequence ( pv_sequence_name  VARCHAR2 ) RETURN VARCHAR2;
  FUNCTION find_next_sequence ( pv_table_name  VARCHAR2 ) RETURN VARCHAR2;
 
  -- Drops sequence.
  PROCEDURE drop_sequence
  ( pv_sequence_name  VARCHAR2 ) IS
    -- Declare local variable(s).
    lv_statement      VARCHAR2(200);
    lv_sequence_name  VARCHAR2(30);
  BEGIN
 
    /*  Conditionally drop any sequence using a local function to find a valid
        sequence name before attempting to drop it. */  
    IF find_sequence(DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name)) > 0 THEN  
 
      /* Dynamically drop sequence, which requires concatenating the sanitized 
         sequence name because you can't bind values on a DDL statement. */
      lv_statement := 'DROP sequence '||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name);
 
      -- Execute statement immediately.
      EXECUTE IMMEDIATE lv_statement;
 
    END IF;      
  END drop_sequence;
 
    -- Drops sequence.
  PROCEDURE create_sequence
  ( pv_sequence_name  VARCHAR2
  , pv_cache_value    NUMBER ) IS
    -- Declare local variable(s).
    lv_statement      VARCHAR2(200);
    lv_next_sequence  NUMBER;
  BEGIN
 
    -- Assign the next sequence value if one exists.
    lv_next_sequence := find_next_sequence(pv_table_name);
 
    -- Check whether a cache value has been provided.
    IF pv_cache_value > 0 THEN
 
      /* Dynamically create a sequence statement with a new start value that is one
         greater than the highest value in the table that uses the sequence. */
      lv_statement := 'CREATE SEQUENCE '||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name)||CHR(10)
                   ||       'MINVALUE 1 NOMAXVALUE INCREMENT BY 1'||CHR(10)
                   ||       'START WITH '||lv_next_sequence||' CACHE '||pv_cache_value||' NOORDER NOCYCLE';
 
    ELSE
 
      /* Dynamically create a sequence statement with a new start value that is one
         greater than the highest value in the table that uses the sequence. */
      lv_statement := 'CREATE SEQUENCE '||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name)||CHR(10)
                   ||       ' MINVALUE 1 NOMAXVALUE'||CHR(10)
                   ||       'INCREMENT BY 1 START WITH '||lv_next_sequence||' NOCACHE NOORDER NOCYCLE';
 
    END IF;
 
    -- Execute create sequence statement.
    EXECUTE IMMEDIATE lv_statement;
 
  END create_sequence;
 
  -- Finds whether a sequence exists.
  FUNCTION find_sequence
  ( pv_sequence_name  VARCHAR2 ) RETURN VARCHAR2 IS
    -- Declare local return variable(s).
    lv_statement      VARCHAR2(200);
    lv_sequence_name  VARCHAR2(30);
    lv_return_value   NUMBER := 0;
 
    -- Declare local system reference cursor.
    lv_cursor  SYS_REFCURSOR;
 
  BEGIN
    -- Dynamically find sequence.
    lv_statement := 'SELECT   sequence_name'||CHR(10)
                 || 'FROM     user_sequences'||CHR(10)
                 || 'WHERE    sequence_name = :seq_name';
 
    OPEN lv_cursor FOR lv_statement USING DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name);
    FETCH lv_cursor INTO lv_sequence_name;
    CLOSE lv_cursor;
 
    -- Convert valid sequence name to positive integer, which represents truth.
    lv_return_value := LENGTH(lv_sequence_name);
 
    -- Return value.
    RETURN lv_return_value;
  EXCEPTION
    -- Reached when DBMS_ASSERT check fails.
    WHEN OTHERS THEN
      RETURN lv_return_value;
  END find_sequence;
 
  -- Finds the next sequence value.
  FUNCTION find_next_sequence
  ( pv_table_name  VARCHAR2 ) RETURN VARCHAR2 IS
    -- Declare local return variable(s).
    lv_statement      VARCHAR2(200);
    lv_sequence_value  NUMBER;
    lv_return_value    NUMBER := 1;
 
    -- Declare local system reference cursor.
    lv_cursor  SYS_REFCURSOR;
 
  BEGIN
    -- Dynamically find sequence.
    lv_statement := 'SELECT   MAX('||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_table_name)||'_ID) + 1'||CHR(10)
                 || 'FROM    '||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_table_name);
 
    OPEN lv_cursor FOR lv_statement;
    FETCH lv_cursor INTO lv_sequence_value;
    CLOSE lv_cursor;
 
    -- Assign the return value from the NDS statement to a local return variable.
    lv_return_value := lv_sequence_value;
 
    -- Return value.
    RETURN lv_return_value;
  EXCEPTION
    -- Reached when DBMS_ASSERT check fails.
    WHEN OTHERS THEN
      RETURN lv_return_value;
  END find_next_sequence;
 
  -- Set precompiler instruction to run in a discrete process.
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 
  -- Conditionally drop sequence.
  drop_sequence(DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name));
 
  -- Create sequence.
  create_sequence(DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name), pv_cache_value);
 
END;
/

You can test this procedure with the following script, which includes COMMIT statements. Those are requires because the stand alone procedure runs independently of the current session as an autonomous procedure.

-- Conditionally drop existing tables and sequences.
BEGIN
  FOR i IN (SELECT object_name, object_type FROM user_objects WHERE object_name IN ('SAMPLE','SAMPLE_S')) LOOP
    IF i.object_type = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS';
      dbms_output.put_line(i.object_name);
    ELSIF i.object_type = 'SEQUENCE' THEN
      EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name;   
      dbms_output.put_line(i.object_name);
    END IF;
  END LOOP;
END;
/
 
-- Create test table.
CREATE TABLE sample
(sample_id  NUMBER);
 
-- Create test table.
CREATE SEQUENCE sample_s;
 
-- Insert a seeding row.
INSERT INTO sample VALUES (10);
 
-- Commit the values because the procedure is autonomous.
COMMIT;
 
-- Reset the sequence value.
EXECUTE reset_sequence('SAMPLE','SAMPLE_S');
 
-- Insert the next sequence value.
INSERT INTO sample VALUES (sample_s.nextval);
 
-- Commit the values because the procedure is autonomous.
COMMIT;
 
-- Query the result to ensure the numbers are sequential.
SELECT * FROM sample;
 
EXECUTE reset_sequence('SAMPLE','SAMPLE_S',500);
 
-- Insert the next sequence value.
INSERT INTO sample VALUES (sample_s.nextval);
 
-- Query the result to ensure the numbers are sequential.
SELECT * FROM sample;

Hope this helps anybody interested in automating the process of resetting a START WITH value of an Oracle sequence.

Written by maclochlainn

November 22nd, 2012 at 2:03 pm