# MacLochlainns Weblog

Michael McLaughlin's Technical Blog

## Oracle 12c VARCHAR2?

The Oracle Database 12c documentation says you can set the maximum size of a VARCHAR2 to 32,767 bytes. That’s true except when you’re trying to map a large Java string to a VARCHAR2. It fails when the physical size of the Java string is greater than 4,000 bytes with an ORA-01002 or fetch out of sequence error.

SELECT read_text_file('C:\Data\loader\Hobbit1.txt') * ERROR at line 1: ORA-24345: A Truncation or null fetch error occurred   ERROR: ORA-01002: fetch out of sequence

You need to grant privileges before you can test this code. You can grant privileges by connecting as the SYS user of a CDB (or non-multitenant database) or as the ADMIN user of a PDB with the AS SYSDBA clause. Then, you run the following command to grant external file access to the JVM inside Oracle Database 12c:

BEGIN DBMS_JAVA.GRANT_PERMISSION('IMPORTER' ,'SYS:java.io.FilePermission' ,'C:\Data\Loader\Hobbit1.txt' ,'read'); END; /

The IMPORTER is a PDB user name, or a non-multitenant database user name. Please note that permissions must be granted on each physical file.

Here’s the code that raises the error when the external file is greater than 4,000 bytes:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ReadFile" AS // Java library imports. import java.io.File; import java.io.BufferedReader; import java.io.FileNotFoundException; import java.io.IOException; import java.io.FileReader; import java.security.AccessControlException;   // Class definition. public class ReadFile { // Define class variables. private static File file; private static FileReader inTextFile; private static BufferedReader inTextReader; private static StringBuffer output = new StringBuffer(); private static String outLine, outText;   // Define readText() method. public static String readText(String fromFile) throws AccessControlException, IOException { // Read file. try { // Initialize File. file = new File(fromFile);   // Check for valid file. if (file.exists()) {   // Assign file to a stream. inTextFile = new FileReader(file); inTextReader = new BufferedReader(inTextFile);   // Read character-by-character. while ((outLine = inTextReader.readLine()) != null) { output.append(outLine + "\n"); }   // Assing the StringBuffer to a String. outText = output.toString();   // Close File. inTextFile.close(); } else { outText = new String("Empty"); }} catch (IOException e) { outText = new String(""); return outText; } return outText; }} /

The PL/SQL wrapper should look like this:

1 2 3 4 5 CREATE OR REPLACE FUNCTION read_text_file (from_file VARCHAR2) RETURN VARCHAR2 IS LANGUAGE JAVA NAME 'ReadFile.readText(java.lang.String) return java.lang.String'; /

Then, you can query it like so:

SELECT read_text_file('C:\Data\loader\Hobbit1.txt') FROM dual;

You can avoid the error by returning the Java file size as a CLOB data type with the following Java source code.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ReadFile" AS // Java library imports. import java.io.File; import java.io.BufferedReader; import java.io.FileNotFoundException; import java.io.IOException; import java.io.FileReader; import java.security.AccessControlException; import java.sql.*; import oracle.sql.driver.*; import oracle.sql.*;   // Class definition. public class ReadFile { // Define class variables. private static int i; private static File file; private static FileReader inTextFile; private static BufferedReader inTextReader; private static StringBuffer output = new StringBuffer(); private static String outLine, outText; private static CLOB outCLOB;   // Define readText() method. public static oracle.sql.CLOB readText(String fromFile) throws AccessControlException, IOException, SQLException { // Read file. try { // Initialize File. file = new File(fromFile);   // Check for valid file. if (file.exists()) {   // Assign file to a stream. inTextFile = new FileReader(file); inTextReader = new BufferedReader(inTextFile);   // Read character-by-character. while ((outLine = inTextReader.readLine()) != null) { output.append(outLine + "\n"); }   // Assing the StringBuffer to a String. outText = output.toString();   // Declare an Oracle connection. Connection conn = DriverManager.getConnection("jdbc:default:connection:");   // Transfer the String to CLOB. outCLOB = CLOB.createTemporary((oracle.jdbc.OracleConnectionWrapper) conn, true, CLOB.DURATION_SESSION); i = outCLOB.setString(1,outText);   // Close File. inTextFile.close(); } else { i = outCLOB.setString(1,"Empty"); }} catch (IOException e) { i = outCLOB.setString(1,""); return outCLOB; } return outCLOB; }} /

The wrapper changes to return a CLOB and map a java.sql.CLOB as the return type of the Java library. This command works:

1 2 3 4 5 CREATE OR REPLACE FUNCTION read_clob_file (from_file VARCHAR2) RETURN CLOB IS LANGUAGE JAVA NAME 'ReadFile.readText(java.lang.String) return oracle.sql.CLOB'; /

You now query the PL/SQL wrapper with this type of effective SQL*Plus command-line command:

COLUMN atext FORMAT A60 HEADING "Text" COLUMN asize FORMAT 99,999 HEADING "Size"   SELECT read_clob_file('C:\Data\loader\Hobbit1.txt') AS AText , LENGTH(read_clob_file('C:\Data\loader\Hobbit1.txt')) AS ASize FROM dual;

As always, I hope this helps somebody.

Written by maclochlainn

May 7th, 2014 at 3:03 am

While I posted how to install Toad for MySQL Freeware five years ago, I’m always surprised how few people know about it there and consistently updated and improved. You can download Toad for MySQL Freeware or Toad Freeware for Oracle, SQL Server, Sybase, or IBM DB2 at this web site.

You can also download Toad Data Modeler Freeware Edition. Just two notes, while Toad for Oracle Freeware is an MSI file, Toad for MySQL Freeware is a zip file and limited to only a Windows install.

Written by maclochlainn

April 30th, 2014 at 1:46 am

## A/UX, NeXTSTEP, & OS X

One thing that gets tedious in the IT community and Oracle community is the penchant for Windows only solutions. While Microsoft does an excellent job in certain domains, I remain a loyal Apple customer. By the way, you can install Oracle Client software on Mac OS X and run SQL Developer against any Oracle Database server. You can even run MySQL Workbench and MySQL server natively on the Mac OS X platform, which creates a robust development platform and gives you more testing options with the MySQL monitor (the client software).

Notwithstanding, some Windows users appear to malign Apple and the Mac OS X on compatibility, but they don’t understand that it’s a derivative of the Research Unix, through BSD (Berkeley Software Distribution). This Unix lineage chart illustrates it well:

I’m probably loyal to Apple because in the early 1990’s I worked on Mac OS 6, Mac OS 7, A/UX, NeXTSTEP, and AIX/6000 (Version 3) while working at APL (American President Lines) in Oakland, California. Back then, my desktop was a pricey Macintosh Quadra 950 and today I work on a pricey Mac Pro desktop. The Mac Pro lets me use VMware virtualize development environments for Oracle Linux, Red Hat Enterprise Linux, Fedora, and as you might guess Windows 7/8. My question to those dyed in the wool Microsoft users is simple, why would you choose a single user OS like Windows over a multi-user OS like Mac OS X?

Written by maclochlainn

April 18th, 2014 at 4:28 pm

## Best UTL_FILE Practice

In a post a couple days ago, I promised to provide a best practice approach to reading external files with the UTL_FILE package. My first assumption is that you’re reading unstructured data because structured data is best read by external tables because external tables can read data much faster with the PARALLEL option.

My second assumption is that you’re you don’t know how to use or choose not to use the DBMS_LOB package; specifically, the loadclobfromfile and loadblobfromfile procedures. By the way, Chapter 10 of the Oracle Database 12c PL/SQL Programming has several complete examples using the DBMS_LOB package. My third assumption is that you’d like the external file packaged as a whole, which means you want it returned as a CLOB or BLOB data type.

Here’s a generic function that achieves that and avoids the nonsense with closing the file in the exception handler, or worse yet, wrapping it in another PL/SQL schema-level 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 84 85 86 87 88 89 90 CREATE OR REPLACE FUNCTION read_file_to_clob ( pv_location VARCHAR2 , pv_file_name VARCHAR2 ) RETURN CLOB IS   /* Declare local input variables. */ lv_location VARCHAR2(60); lv_file_name VARCHAR2(40);   /* Declare a file reference pointer and buffer. */ lv_file UTL_FILE.FILE_TYPE; -- File reference lv_line VARCHAR2(32767); -- Reading buffer   /* Declare local sizing variables. */ lv_file_size NUMBER; lv_line_size NUMBER; lv_read_size NUMBER :=0;   /* Declare local file attribute data. */ lv_file_exists BOOLEAN := FALSE; lv_block_size BINARY_INTEGER;   /* Declare a control variable and return CLOB variable. */ lv_enable BOOLEAN := FALSE; lv_return CLOB; BEGIN /* Declare local input variables. */ lv_location := pv_location; lv_file_name := pv_file_name;   /* Check for open file and close when open. */ IF utl_file.is_open(lv_file) THEN utl_file.fclose(lv_file); END IF;   /* Read the file attributes to get the physical size. */ utl_file.fgetattr( location => lv_location , filename => lv_file_name , fexists => lv_file_exists , file_length => lv_file_size , block_size => lv_block_size );   /* Open only files that exist. */ IF lv_file_exists THEN   /* Create a temporary CLOB in memory. */ dbms_lob.createtemporary(lv_return, FALSE, dbms_lob.CALL);   /* Open the file for read-only of 32,767 byte lines. */ lv_file := utl_file.fopen( location => lv_location , filename => lv_file_name , open_mode => 'R' , max_linesize => 32767);     /* Read all lines of a text file. */ WHILE (lv_read_size < lv_file_size) LOOP /* Read a line of text until the eof marker. */ utl_file.get_line( file => lv_file , buffer => lv_line );   /* Add the line terminator or 2 bytes to its length. */ lv_line := NVL(lv_line,'')||CHR(10); lv_read_size := lv_read_size + LENGTH(NVL(lv_line,CHR(10))) + 2;   /* Write to an empty CLOB or append to an existing CLOB. */ IF NOT lv_enable THEN /* Write to the temporary CLOB variable. */ dbms_lob.WRITE( lv_return, LENGTH(lv_line), 1, lv_line);   /* Set the control variable. */ lv_enable := TRUE; ELSE /* Append to the temporary CLOB variable. */ dbms_lob.writeappend( lv_return, LENGTH(lv_line),lv_line); END IF; END LOOP;   /* Close the file. */ utl_file.fclose(lv_file); END IF;   /* This line is never reached. */ RETURN lv_return; EXCEPTION WHEN OTHERS THEN utl_file.fclose(lv_file); RAISE NO_DATA_FOUND; END; /

You can test the function with the following:

SET LONG 100000 SET PAGESIZE 999   SELECT read_file_to_clob('SomeVirtualDirectory','TextFile.txt') AS "Output" FROM dual;

If anybody has suggestions for improvements, please pass them along. As always, I hope this helps other developers.

Written by maclochlainn

April 16th, 2014 at 3:07 am

## Using UTL_FILE Package

Sometimes I’m surprised. Today, the surprise came when somebody pointed to a potential error in another author’s book. The person who asked the question had to send me a screen shot before I believed it.

The author’s code encounters the following error because the code was designed to loop through a multiple line file, and the code called the UTL_FILE.FOPEN procedure with three instead of four parameters. While it works with only three parameters when the strings are less than or equal to 1,024 (thanks Gary), it throws read errors when a string exceeds the default. You use the fourth parameter when your string exceeds the default length of 1,024.

DECLARE * ERROR at line 1: ORA-29284: file READ error ORA-06512: at "SYS.UTL_FILE", line 106 ORA-06512: at "SYS.UTL_FILE", line 746 ORA-06512: at line 26

As mentioned, you fix the problem by using the fourth parameter like the call on lines 15 through 18 below:

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 DECLARE /* Declare local input variables. */ lv_location VARCHAR2(60) := 'C:\Data\Direct'; lv_file_name VARCHAR2(40) := 'TextFile.txt';   /* Declare a file reference pointer and buffer. */ lv_file UTL_FILE.FILE_TYPE; -- File reference lv_line VARCHAR2(32767); -- Reading buffer   BEGIN   /* Check for open file and close when open. */ IF utl_file.is_open(lv_file) THEN utl_file.fclose(lv_file); END IF;   /* Open the file for read-only of 32,767 lines of text. The fourth parameter is required when you want to use the GET_LINE procedure to read a file line-by-line. */ lv_file := utl_file.fopen( location => lv_location , filename => lv_file_name , open_mode => 'R' , max_linesize => 32767);   /* Read all lines of a text file. */ LOOP /* Read a line of text, when the eof marker is found the get_line procedure raises a NO_DATA_FOUND error, which is why there's no explicit loop. */ utl_file.get_line( file => lv_file , buffer => lv_line );   /* Print the line of text or a line return because UTL_FILE doesn't read line returns. */ dbms_output.put_line(NVL(lv_line,CHR(10)));   END LOOP;   EXCEPTION WHEN NO_DATA_FOUND THEN utl_file.fclose(lv_file); END; /

While the foregoing is traditional and uses a physical directory path from the local server’s operating system, you can use a virtual directory from Oracle Database 10g forward. This physical directory is also defined in the utl_file_dir parameter of the database server. A virtual directory hides the physical directory from the application software, which simplifies physical file maintenance when you need to move the files.

3 lv_location VARCHAR2(60) := 'DirectVirtualDirectory';

The preceding example works, and I’ll put one out converting the external to CLOB later in the week.

Written by maclochlainn

April 13th, 2014 at 11:46 pm

## DBMS_JAVA Privilege Error?

It’s possible to get an error after granting privileges to an external file system. One of those errors is tedious to resolve until you understand the rules governing Java NIO file permissions.

You grant privileges to external file systems as the sys user with the grant_permission procedure of the dbms_java package, like

SQL> BEGIN 2 DBMS_JAVA.GRANT_PERMISSION('IMPORTER' 3 ,'SYS:java.io.FilePermission' 4 ,'C:\Data\Upload' 5 ,'read'); 6 END; 7 /

After you grant privileges to read, write, and delete files with Oracle’s dbms_java package, you must disconnect any active session to enjoy the new privileges. If you forget to disconnect and re-connect an active session, you get the following error message:

FROM TABLE(list_files(get_directory_path('STAGE'))) * ERROR at line 2: ORA-29532: Java CALL TERMINATED BY uncaught Java exception: java.security.AccessControlException: Directory permissions restricted.

The problem and fix are simple. The permissions aren’t in effect until after you disconnect and reconnect your active sessions. I put this there because when I Google’d it, there wasn’t an answer already.

While I’m on the topic of privilege errors, that aren’t really errors, I thought it would be helpful to qualify how the delete permission works because I couldn’t find it anywhere in the Oracle documentation (if somebody finds it let make a comment that enriches the discussion). Unlike the read and write permissions, the delete permission requires permissions on specific files.

If you only grant permission on like:

SQL> BEGIN 2 DBMS_JAVA.GRANT_PERMISSION('IMPORTER' 3 ,'SYS:java.io.FilePermission' 4 ,'C:\Data\Upload' 5 ,'read,write,delete'); 6 END; 7 /

You would get this message when trying to delete an external file:

BEGIN * ERROR at line 1: ORA-29532: Java CALL TERMINATED BY uncaught Java exception: java.security.AccessControlException: the Permission (java.io.FilePermission c:\DATA\log\item_import.log READ) has NOT been GRANTED TO IMPORTER. The PL/SQL TO GRANT this IS dbms_java.grant_permission( 'IMPORTER', 'SYS:java.io.FilePermission', 'c:\data\log\item_import.log', 'read' ) ORA-06512: at "IMPORTER.DELETE_FILE", line 1 ORA-06512: at line 2

It requires that you grant permissions on individual files to have the privilege of deleting them from within Oracle. That grant would look like the following:

SQL> BEGIN 2 DBMS_JAVA.GRANT_PERMISSION('IMPORTER' 3 ,'SYS:java.io.FilePermission' 4 ,'C:\Data\Upload\item_import.log' 5 ,'read,write,delete'); 6 END; 7 /

As always, I hope this helps other developers.

Written by maclochlainn

March 29th, 2014 at 1:59 pm

## Hidden DBMS_JAVA Nuance

It always happens when I’m in a hurry. Yes, I ran into one of those pesky little features with Oracle’s DBMS_JAVA package. While I try to write entries with proper GeSHi case semantics, like everyone else I tend to write PL/SQL initially in lowercase. That led me to the discovery of this wonderful error message:

BEGIN * ERROR at line 1: ORA-29532: Java CALL TERMINATED BY uncaught Java exception: oracle.aurora.vm.IdNotFoundException: importer : USER OR ROLE id does NOT exist ORA-06512: at "SYS.DBMS_JAVA", line 705 ORA-06512: at line 2

The problem was simple, while not published, you must enter the user/schema name in uppercase text when calling DBMS_JAVA.GRANT_PERMISSION procedure. Here’s a proper example:

SQL> BEGIN 2 DBMS_JAVA.GRANT_PERMISSION('IMPORTER' 3 ,'SYS:java.io.FilePermission' 4 ,'C:\Data\Upload' 5 ,'read'); 6 END; 7 /

If you change the IMPORTER string to lower or mixed case, you raise the aforementioned error. Quite an interesting tidbit that I missed from Oracle Database 10g until Oracle Database 12c. Hope this helps you avoid wondering what’s happening when your line 2 looks like this:

 2 DBMS_JAVA.GRANT_PERMISSION('Importer'

As always, I hope this helps others. If you like more on writing Java inside the Oracle database you can check this older post.

Written by maclochlainn

March 21st, 2014 at 12:44 am

## Deprecated mysqli Functions

Having noticed the release of PHP 5.5.10 last week while I was speaking at UTOUG, I checked the deprecated mysqli functions web page. There weren’t any deprecated by 5.5. Unfortuantely, there were six mysqli functions deprecated in 5.3 and removed in 5.4. Unfortunately, many of my posted code examples use 5.2 or 5.3 where they would have worked. The deprecated mysqli functions are:

• mysqli_bind_param
• mysqli_bind_result
• mysqli_client_encoding
• mysqli_fetch
• mysqli_param_count
• mysqli_send_long_data

Unfortunately, that puts a lot of updates on my queue of things to do. I imagine it likewise imposes limits on those running commercially on PHP 5.3 or older that plan an upgrade.

It does remind me that I need to pay close attention to the deprecation of mysqli functions with each release. These are actually the largest change since PHP 5.0.

Written by maclochlainn

March 18th, 2014 at 12:28 pm

Posted in MySQL,mysqli,PHP

Tagged with , ,

## 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:

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;

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

## Hobbyist at Heart

Fifteen years ago when I dove into JavaScript friends called me a hobbyist and said I was wasting my time. They had some basis in what they said because most of my work is server side with the database or operating system. However, they haven’t said that I wasted my time with the advent of NoSQL solutions, like Cassandra and MongoDB; or frameworks like node.js. Now writing JavaScript is mainstream and an invaluable skill set, and they’re trying to catch up. While David Flanagan’s JavaScript: The Definitive Guide is comprehensive, I recommend my students start with David McFarland’s JavaScript & jQuery: The Missing Manual. I also strongly suggest they use jQuery first and write JavaScript when jQuery isn’t suitable.

I really liked David McFarland’s CSS: The Missing Manual when it came out in 2009, and I’ve recommended it for several years. However, he’s got a new version – CSS3: The Missing Manual that came out in late January 2014. It’s an improvement over his first volume and I’d recommend you upgrade if you’re writing, modifying, or maintaining Cascading Style Sheet or if you just want to learn more about CSS.

Fortunately for me, CSS3: The Missing Manual is available through iTunes for Apple users, Naturally, it’s also available on Safari and Kindle formats. As an Apple user, I opted for the iBook format for my iPad Air. Unfortunately, it’s $27.99 as an iBook compared to$15.49 on Kindle, and that almost makes me opt to use the Kindle App.

Written by maclochlainn

March 16th, 2014 at 4:20 pm

Posted in CSS,CSS3,JavaScript,jQuery,JSON

Tagged with , , , ,