MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for March, 2021

MySQL Transaction Scope

without comments

The idea of ACID transactions are a basic feature of SQL’s individual Data Manipulation Language (DML) commands, like the INSERT, UPDATE, and DELETE statements. Transactions across two or more tables are a natural extension of ACID compliance features provided by DML commands. However, they require a structured programming approach, like a store procedure or like API implemented in an imperative language.

Surprisingly, transaction management wasn’t covered well in Alan Beaulieu’s Learning SQL because he only provided pseudo code logic. While I thought troubleshoot some broken MySQL SQL/PSM logic would be a good learning experience for students, it wasn’t. So, I wrote this sample code to show how to achieve an all or nothing transaction across four tables.

The code for this example on transaction management lets you perform the important tasks necessary to effect transaction management:

  • You must disable autocommit in the scope of the session.
  • You must use an imperative programming language like SQL/PSM or Python, et cetera.
  • You must identify an error thrown in a series of DML transactions, rollback all completed work, and exit the scope of the program unit.

This SQL defines the four tables:

/* Drop and create four tables. */
DROP TABLE IF EXISTS one, two, three, four;
CREATE TABLE one   ( id int primary key auto_increment, msg varchar(10));
CREATE TABLE two   ( id int primary key auto_increment, msg varchar(10));
CREATE TABLE three ( id int primary key auto_increment, msg varchar(10));
CREATE TABLE four  ( id int primary key auto_increment, msg varchar(10));

Unfortunately, there’s no way to simply transaction management from the MySQL Command-Line Interface (CLI) because you need to build the logic that manages success and failure. It requires that you create a procedure using MySQL’s SQL/PSM (Persistent Stored Module) or another imperative programming language. You might think why can’t you just write an anonymous block program, like you can do in other stored procedural languages. The answer is simple. You can’t write anonymous blocks in MySQL’s SQL/PSM because they adhere to ANSI SQL-2003 (or more accurately ISO/IEC 9075-4:2003).

The following code block does:

  • Conditionally drops the locking() procedure.
  • Sets the default semicolon (;) delimiter to a double-dollar ($$), which lets you use the semicolon as statement and block terminators.
  • Declares a locking() procedure with the following:
    • Sets a parameter list with four IN-mode parameters.
    • Declares an EXIT handler that undoes any writes before an error in a sequence of DDL commands, like the INSERT, UPDATE, or DELETE statements. The EXIT handler then aborts completion of the rest of the procedure. (MySQL 13.6.7.2 Declare … Handler Statement)
    • Disables autocommit in the scope of the session.
    • Starts a transaction context and inserts data into four tables as a transaction. The continue handler picks up processing when one of the INSERT statements fails with a 1406 error code. The 1406 error code represents an error that occurs because the data is too long for a column’s width.
    • When all elements of the procedure complete, you commit the work.
  • Sets the double-dollar delimiter back to the default semicolon.
/* Conditionally drop procedure. */
DROP PROCEDURE IF EXISTS locking;
 
/* Set delimiter to $$ to allow ; inside the procedure. */
DELIMITER $$
 
/* Create a transaction procedure. */
CREATE PROCEDURE locking(IN pv_one   varchar(10)
                        ,IN pv_two   varchar(10)
                        ,IN pv_three varchar(10)
                        ,IN pv_four  varchar(10))
  BEGIN
    /* Declare an EXIT Handler when a string is too long
       for a column. Undo all prior writes with a ROLLBACK
       statement. */
    DECLARE EXIT HANDLER FOR 1406 
      BEGIN
        ROLLBACK;
      END;
 
    /* Disable autocommit. */
    SET AUTOCOMMIT=0;
 
    /* Start transaction scope. */	   
    START TRANSACTION;
 
    /* A series of INSERT statement. */
    INSERT INTO one   (msg) VALUES (pv_one);
    INSERT INTO two   (msg) VALUES (pv_two);
    INSERT INTO three (msg) VALUES (pv_three);
    INSERT INTO four  (msg) VALUES (pv_four);
 
    /* Commit transaction set. */
    COMMIT;
  END;
$$ 
 
/* Reset delimiter to ; for SQL statements. */
DELIMITER ;

The next block tests the locking() procedure. The first and third calls are successful but the second one fails because the third parameter is too long for the msg column in the three table. The error triggers the EXIT handler in the locking() procedure.

/* Call locking procedure. */
CALL locking('Donald','Goofy','Mickey','Pluto');
CALL locking('Squirrel','Chipmunk','Monkey business','Raccoon');
CALL locking('Curly','Larry','Moe','Shemp');

The query block below:

/* Select from tables, which should be empty. */
SELECT * FROM one;
SELECT * FROM two;
SELECT * FROM three;
SELECT * FROM four;

Returns the following, which shows only the first and third test cases succeed:

+----+--------+
| id | msg    |
+----+--------+
|  1 | Donald |
|  2 | Curly  |
+----+--------+
2 rows in set (0.01 sec)
 
+----+-------+
| id | msg   |
+----+-------+
|  1 | Goofy |
|  2 | Larry |
+----+-------+
2 rows in set (0.00 sec)
 
+----+--------+
| id | msg    |
+----+--------+
|  1 | Mickey |
|  2 | Moe    |
+----+--------+
2 rows in set (0.00 sec)
 
+----+-------+
| id | msg   |
+----+-------+
|  1 | Pluto |
|  2 | Shemp |
+----+-------+
2 rows in set (0.00 sec)

As always, I hope this helps those trying to write transactions across multiple tables.

Written by maclochlainn

March 16th, 2021 at 10:01 am

Posted in LAMP,Linux,Mac,MySQL,MySQL 8,MySQL Client,MySQL Shell,sql,Unix,Windows 10

Tagged with ,

Oxygen XML Editor

without comments

Somebody asked me about how they could convert an XML file to a CSV file to upload into MySQL. They were asking the question based on an old Convert XML to CSV blog post from 2008. Amazing though that is, I had to explain the process no longer requires manual tasks, like calling Java files from the Apache XML Project. All they needed to do was use the Oxygen XML Editor, which is why I wrote this blog post.

For example, I had them use the same sample XML file from the old blog post (shown below) with one change. The encoding value needs to change from latin1 (ISO-8859-1) to unicode (UTF-8). Then, they should put it into a local Windows directory (mine went into the C:\Data directory).

<?xml version="1.0" encoding="UTF-8"?>
<character>
  <name>
    <role>Indiana Jones</role>
    <actor>Harrison Ford</actor>
    <part>protagonist</part>
    <film>Indiana Jones and Raiders of the Lost Ark</film>
    <film>Indiana Jones and the Temple of Doom</film>
    <film>Indiana Jones and the Last Crusade</film>
    <film>Indiana Jones and the Kingdom of the Crystal Skull</film>
  </name>
  <name>
    <role>Wilhelmina Scott</role>
    <actor>Kate Capshaw</actor>
    <part>support</part>
    <film>Indiana Jones and the Temple of Doom</film>
  </name>
  <name>
    <role>Marion Ravenwood</role>
    <actor>Karen Allen</actor>
    <part>support</part>
    <film>Indiana Jones and Raiders of the Lost Ark</film>
    <film>Indiana Jones and the Kingdom of the Crystal Skull</film>
  </name>
  <name>
    <role>Elsa Schneider</role>
    <actor>Alison Doody</actor>
    <part>support</part>
    <film>Indiana Jones and the Last Crusade</film>
  </name>
  <name>
    <role>Short Round</role>
    <actor>Jonathan Ke Quan</actor>
    <part>support</part>
    <film>Indiana Jones and the Temple of Doom</film>
  </name>
  <name>
    <role>Sallah</role>
    <actor>Jonn Rhys-Davies</actor>
    <part>support</part>
    <film>Indiana Jones and Raiders of the Lost Ark</film>
    <film>Indiana Jones and the Last Crusade</film>
  </name>
  <name>
    <role>Professor Henry Jones</role>
    <actor>Sean Connery</actor>
    <part>support</part>
    <film>Indiana Jones and the Last Crusade</film>
  </name>
  <name>
    <role>Henry "Mutt" Williams</role>
    <actor>Shia LaBeouf</actor>
    <part>support</part>
    <film>Indiana Jones and the Kingdom of the Crystal Skull</film>
  </name>
  <name>
    <role>Marcus Brody</role>
    <actor>Denholm Elliott</actor>
    <part>support</part>
    <film>Indiana Jones and Raiders of the Lost Ark</film>
    <film>Indiana Jones and the Last Crusade</film>
  </name>
  <name>
    <role>Amrish Puri</role>
    <actor>Mola Ram</actor>
    <part>antagonist</part>
    <film>Indiana Jones and the Temple of Doom</film>
  </name>
  <name>
    <role>Rene Belloq</role>
    <actor>Belloq</actor>
    <part>antagonist</part>
    <film>Indiana Jones and Raiders of the Lost Ark</film>
  </name>
  <name>
    <role>Walter Donovan</role>
    <actor>Julian Glover</actor>
    <part>antagonist</part>
    <film>Indiana Jones and the Last Crusade</film>
  </name>
  <name>
    <role>Colonel Vogel</role>
    <actor>Michael Bryne</actor>
    <part>antagonist</part>
    <film>Indiana Jones and the Last Crusade</film>
  </name>
  <name>
    <role>Irina Spalko</role>
    <actor>Cate Blanchett</actor>
    <part>antagonist</part>
    <film>Indiana Jones and the Kingdom of the Crystal Skull</film>
  </name>
</character>

Then, I had them copy the following XML Style Language Transformation (XSLT) file into the same C:\Data directory with the encoding value change from latin1 to unicode:

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

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
  <!-- This loops through the branch when a sibling meets a condition. -->
  <xsl:for-each select="character/name[film='Indiana Jones and the Last Crusade']">
  <!-- Sorts based on the value in the "role" element. -->
  <xsl:sort select="role" />
  <!-- Eliminates anyone that has a "part" element value of "antagonist". -->
    <xsl:if test="part != 'antagonist'">
      <!-- An apostrophe before and after with a line return. -->
      <xsl:text>&#39;</xsl:text>
      <xsl:value-of select="role"/>
      <!-- An apostrophe followed by a comma -->
      <xsl:text>&#39;&#44;</xsl:text>
      <xsl:text>&#39;</xsl:text>
      <xsl:value-of select="actor"/>
      <xsl:text>&#39;&#44;</xsl:text>
      <xsl:text>&#39;</xsl:text>
      <xsl:value-of select="film"/>
      <!-- An apostrophe followed by a line return -->
      <xsl:text>&#39;&#10;</xsl:text>
    </xsl:if>
  </xsl:for-each>
</xsl:template>
</xsl:stylesheet>

Open or launch the Oxygen XML Editor and do these steps:

  • Create a new Project called character.
  • Create the C:\Data\test.xml and C:\Data\convert.xsl files in a C:\Data directory.
  • Open the C:\Data\test.xml and C:\Data\convert.xsl files inside the Oxygen XML Editor.
  • Click on convert.xsl file tab before clicking on the Configure Transformation Scenario(s) button. The button looks like a red wrench with a small red arrow to the bottom right.
  • After launching the Configure Transformation Scenario(s) dialog, click the Edit button in the dialog box and launch the Edit Scenario dialog.
  • Enter file:/C:/Data/test.xml (use the file chooser if don’t want to type it) in the XML URL field in the Edit Scenario dialog.
  • Click the OK button to close the Edit Scenario dialog and the Apply associated button to close the Configure Transformation Scenario(s) dialog.
  • Click the Apply Transformation Scenario button, which is red arrow button. It will transform the XML document into a result pane at the bottom.
  • Select All (or Ctrl+A) in the result panel and right click on that selected area to launch a context sensitive menu. In that menu, click the Save button to launch a file chooser that will let you save your results.

If you know how to write XSLT this is simple and if you don’t it might take a little time to find a working example on the Internet. Better yet, check out the w3schools for the XSLT documentation or tutorials point’s Learn XSLT website. You can see how to Upload the CSV file into MySQL on this older blog post.

In Linux, you can make this conversion using the Command-Line Interface (CLI) by using the xsltproc program. Assume the XML file is test.xml and the XSLT file is tocsv.xsl, then you can generate the Comma-Separated Values file with this syntax:

xsltproc tocsv.xsl test.xml > text.csv

As always, I hope this helps those looking for a simple solution.

Written by maclochlainn

March 11th, 2021 at 1:15 pm

Posted in Apache,MySQL,MySQL 8,Oxygen XML Editor,xml,xslt

Tagged with , ,