MacLochlainns Weblog

The first tutorial supplementing the MySQL Connector/NET Developer Guide showed you how to connect and run static INSERT statement. It was a barebones PowerShell script with the MySQL Connector. This post shows you how to run a PowerShell script that uses a dynamic form to gather the MySQL credentials and then run a static query. Below is the MySQL Credentials form.

You enter the correct user name, password, hostname (or IP address), port, and database, like this:

Here’s the complete code for this staticQuery.ps1 PowerShell script:

# Add libraries for form components.
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
 
# Define a user credential form.
$form = New-Object System.Windows.Forms.Form
$form.Text = 'User Credential Form'
$form.Size = New-Object System.Drawing.Size(300,240)
$form.StartPosition = 'CenterScreen'
 
# Define a button and assign it and its controls to a form.
$loginButton = New-Object System.Windows.Forms.Button
$loginButton.Location = New-Object System.Drawing.Point(60,160)
$loginButton.Size = New-Object System.Drawing.Size(75,23)
$loginButton.Text = 'Login'
$loginButton.DialogResult = [System.Windows.Forms.DialogResult]::OK
$form.AcceptButton = $loginButton
$form.Controls.Add($loginButton)
 
# Define a button and assign it and its controls to a form.
$cancelButton = New-Object System.Windows.Forms.Button
$cancelButton.Location = New-Object System.Drawing.Point(155,160)
$cancelButton.Size = New-Object System.Drawing.Size(75,23)
$cancelButton.Text = 'Cancel'
$cancelButton.DialogResult = [System.Windows.Forms.DialogResult]::Cancel
$form.CancelButton = $cancelButton
$form.Controls.Add($cancelButton)
 
# Define a label and assign it and its controls to a form.
$userLabel = New-Object System.Windows.Forms.Label
$userLabel.Location = New-Object System.Drawing.Point(30,15)
$userLabel.Size = New-Object System.Drawing.Size(100,20)
$userLabel.Text = 'Enter User Name:'
$form.Controls.Add($userLabel)
 
# Define a TextBox and assign it and its controls to a form.
$userTextBox = New-Object System.Windows.Forms.TextBox
$userTextBox.Location = New-Object System.Drawing.Point(140,15)
$userTextBox.Size = New-Object System.Drawing.Size(100,20)
$form.Controls.Add($userTextBox)
 
# Define a label and assign it and its controls to a form.
$pwdLabel = New-Object System.Windows.Forms.Label
$pwdLabel.Location = New-Object System.Drawing.Point(30,40)
$pwdLabel.Size = New-Object System.Drawing.Size(100,20)
$pwdLabel.Text = 'Enter Password:'
$form.Controls.Add($pwdLabel)
 
# Define a TextBox and assign it and its controls to a form.
$pwdTextBox = New-Object System.Windows.Forms.TextBox
$pwdTextBox.Location = New-Object System.Drawing.Point(140,40)
$pwdTextBox.Size = New-Object System.Drawing.Size(100,20)
$pwdTextBox.PasswordChar = "*"
$form.Controls.Add($pwdTextBox)
 
# Define a label and assign it and its controls to a form.
$hostLabel = New-Object System.Windows.Forms.Label
$hostLabel.Location = New-Object System.Drawing.Point(30,65)
$hostLabel.Size = New-Object System.Drawing.Size(100,20)
$hostLabel.Text = 'Enter Hostname:'
$form.Controls.Add($hostLabel)
 
# Define a TextBox and assign it and its controls to a form.
$hostTextBox = New-Object System.Windows.Forms.TextBox
$hostTextBox.Location = New-Object System.Drawing.Point(140,65)
$hostTextBox.Size = New-Object System.Drawing.Size(100,20)
$form.Controls.Add($hostTextBox)
 
# Define a label and assign it and its controls to a form.
$portLabel = New-Object System.Windows.Forms.Label
$portLabel.Location = New-Object System.Drawing.Point(30,90)
$portLabel.Size = New-Object System.Drawing.Size(100,20)
$portLabel.Text = 'Enter Port #:'
$form.Controls.Add($portLabel)
 
# Define a TextBox and assign it and its controls to a form.
$portTextBox = New-Object System.Windows.Forms.TextBox
$portTextBox.Location = New-Object System.Drawing.Point(140,90)
$portTextBox.Size = New-Object System.Drawing.Size(100,20)
$form.Controls.Add($portTextBox)
 
# Define a label and assign it and its controls to a form.
$dbLabel = New-Object System.Windows.Forms.Label
$dbLabel.Location = New-Object System.Drawing.Point(30,115)
$dbLabel.Size = New-Object System.Drawing.Size(100,20)
$dbLabel.Text = 'Enter DB Name:'
$form.Controls.Add($dbLabel)
 
# Define a TextBox and assign it and its controls to a form.
$dbTextBox = New-Object System.Windows.Forms.TextBox
$dbTextBox.Location = New-Object System.Drawing.Point(140,115)
$dbTextBox.Size = New-Object System.Drawing.Size(100,20)
$form.Controls.Add($dbTextBox)
 
$form.Topmost = $true
 
$form.Add_Shown({$userTextBox.Select()})
$result = $form.ShowDialog()
 
if ($result -eq [System.Windows.Forms.DialogResult]::OK) {
 
  # Assign inputs to connection variables.
  $uid = $userTextBox.Text
  $pwd = $pwdTextBox.Text
  $server = $hostTextBox.Text
  $port= $portTextBox.Text
  $dbName = $dbTextBox.Text
 
  # Declare connection string.
  $credentials = 'server=' + $server +
                 ';port=' + $port +
		 ';uid=' + $uid + 
		 ';pwd=' + $pwd + 
		 ';database=' + $dbName
 
  # Connect to the libaray MySQL.Data.dll
  Add-Type -Path 'C:\Program Files (x86)\MySQL\Connector NET 8.0\Assemblies\v4.5.2\MySql.Data.dll'
 
  # Create a MySQL Database connection variable that qualifies:
  # [Driver]@ConnectionString
  # ============================================================
  #  You can assign the connection string before using it or
  #  while using it, which is what we do below by assigning
  #  literal values for the following names:
  #   - server=<ip_address> or 127.0.0.1 for localhost
  #   - port=<port #>
  #   - uid=<user_name>
  #   - pwd=<password>
  #   - database=<database_name>
  # ============================================================
  $Connection = [MySql.Data.MySqlClient.MySqlConnection]@{ConnectionString=$credentials}
  $Connection.Open()
 
  # Define a MySQL Command Object for a non-query.
  $sqlCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
  $sqlDataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter
  $sqlDataSet = New-Object System.Data.DataSet
 
  # Assign the connection and command text to the MySQL command object.
  $sqlCommand.Connection = $Connection
  $sqlCommand.CommandText = 'SELECT CONCAT(first_name," ",last_name) AS full_name ' +
                            ',      avenger ' +
                            'FROM   avenger'
 
  # Assign the connection and command text to the query method of
  # the data adapter object.
  $sqlDataAdapter.SelectCommand=$sqlCommand
 
  # Assign the tuples of data to a data set and return the number of rows fetched.
  $rowsFetched=$sqlDataAdapter.Fill($sqlDataSet, "data")
 
  # Print to console the data returned from the query.
  foreach($row in $sqlDataSet.tables[0]) {
    write-host "Avenger:" $row.avenger "is" $row.full_name }
 
  # Close the MySQL connection.
  $Connection.Close()
}

I created an avenger table and populated it with six rows of data:

-- Create the avenger table.
CREATE TABLE db_connect
( db_connect_id  INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, version        VARCHAR(10)
, user           VARCHAR(24)
, db_name        VARCHAR(10));
 
-- Seed the avenger table with data.
INSERT INTO avenger
( first_name, last_name, avenger )
VALUES
 ('Anthony', 'Stark', 'Iron Man')
,('Thor', 'Odinson', 'God of Thunder')
,('Steven', 'Rogers', 'Captain America')
,('Bruce', 'Banner', 'Hulk')
,('Clinton', 'Barton', 'Hawkeye')
,('Natasha', 'Romanoff', 'Black Widow');

You run the staticQuery.ps1 PowerShell script from the Windows command shell with the following syntax:

powershell .\staticQuery.ps1

After running the staticQuery.ps1 PowerShell script, it writes the following to the local console but with minimal effort you can redirect it to a file:

Avenger: Iron Man is Anthony Stark
Avenger: God of Thunder is Thor Odinson
Avenger: Captain America is Steven Rogers
Avenger: Hulk is Bruce Banner
Avenger: Hawkeye is Clinton Barton
Avenger: Black Widow is Natasha Romanoff

As always, I hope this helps those looking to use this technology. My guess is the principal uses will be DevOps and Data Engineers.

It was interesting to note that the MySQL Connector/NET Developer Guide doesn’t have any instructions for connecting to the MySQL database from Microsoft Powershell. I thought it would be helpful to write a couple demonstrations scripts, especially when a quick search didn’t find a set of easy to follow samples.

The connection process to MySQL with Powershell is easiest with a non-query, so I created a db_connect table into which I could write a row of data:

CREATE TABLE db_connect
( db_connect_id  INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, version        VARCHAR(10)
, user           VARCHAR(24)
, db_name        VARCHAR(10));

The following insert.ps1 PowerShell script connects to the MySQL database with the NET 8.0 Connector (check here for the newer DSN ODBC approach), and inserts one row into the db_connect table:

# Connect to the libaray MySQL.Data.dll
Add-Type -Path 'C:\Program Files (x86)\MySQL\Connector NET 8.0\Assemblies\v4.5.2\MySql.Data.dll'
 
# Create a MySQL Database connection variable that qualifies:
# [Driver]@ConnectionString
# ============================================================
#  You can assign the connection string before using it or
#  while using it, which is what we do below by assigning
#  literal values for the following names:
#   - server=<ip_address> or 127.0.0.1 for localhost
#   - uid=<user_name>
#   - pwd=<password>
#   - database=<database_name>
# ============================================================
$Connection = [MySql.Data.MySqlClient.MySqlConnection]@{ConnectionString='server=127.0.0.1;uid=student;pwd=student;database=studentdb'}
$Connection.Open()
 
# Define a MySQL Command Object for a non-query.
$sql = New-Object MySql.Data.MySqlClient.MySqlCommand
$sql.Connection = $Connection
$sql.CommandText = 'INSERT INTO db_connect (version, user, db_name)(SELECT version(), user(), database())'
$sql.ExecuteNonQuery()
 
# Close the MySQL connection.
$Connection.Close()

The ConnectionString above uses the standard local 127.0.0.1 IP address as the server location. You could just as easily use localhost as the server location if you’re testing on your own machine.

You run the insert.ps1 PowerShell script from the Windows command shell:

powershell .\insert.ps1

After running the insert.ps1 PowerShell script, you can connect to the studentdb database. Then, run the following query:

SELECT version AS "Version"
,      user AS "User"
,      db_name AS "Database"
FROM   db_connect;

It displays:

+---------+-------------------+-----------+
| Version | User              | Database  |
+---------+-------------------+-----------+
| 8.0.21  | student@localhost | studentdb |
+---------+-------------------+-----------+
1 row in set (0.01 sec)

If you’re interested in writing a Connection Prompt dialog for PowerShell, the complete code is in this other blog post of mine. It also provides the instructions to put the code into a reusable PowerShell library. Also, if you’re interested in how to pass option flags and parameters I put that in this new blog post.

As always, I hope this helps those trying to use PowerShell as a scripting tool to insert, update, or delete data.

It’s always interesting to see students find the little nuances that SQL*Plus can generate. One of the first things we cover is the concept of calling PL/SQL interactively versus through an embedded call. The easiest and first exercise simply uses an insecure call like:

sqlplus -s student/student @call.sql

to the call.sql program:

SQL> DECLARE
  2    lv_input  VARCHAR2(20);
  3  BEGIN
  4    lv_input := '&1';
  5    dbms_output.put_line('['||lv_input||']');
  6  END;
  7  /

It prints the following to console:

Enter value for 1: machine
old   4:   lv_input := '&1';
new   4:   lv_input := 'machine';
[machine]
 
PL/SQL procedure successfully completed.

Then, we change the '&1' parameter variable to '&mystery' and retest the program, which prints the following to the console:

Enter value for mystery: machine
old   4:   lv_input := '&mystery';
new   4:   lv_input := 'machine';
[machine]
 
PL/SQL procedure successfully completed.

After showing a numeric and string input parameter, we remove the quotation from the lv_input input parameter and raise the following error:

Enter value for mystery: machine
old   4:   lv_input := &mystery;
new   4:   lv_input := machine;
  lv_input := machine;
              *
ERROR at line 4:
ORA-06550: line 4, column 15:
PLS-00201: identifier 'MACHINE' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

The point of the exercise is to spell out that the default input value is numeric and that if you pass a string it becomes an identifier in the scope of the program. So, we rewrite the call.sql program file by adding a machine variable, like:

SQL> DECLARE
  2    lv_input  VARCHAR2(20);
  3    machine   VARCHAR2(20) := 'Mystery Machine';
  4  BEGIN
  5    lv_input := &mystery;
  6    dbms_output.put_line('['||lv_input||']');
  7  END;
  8  /

It prints the following:

Enter value for mystery: machine
old   5:   lv_input := &mystery;
new   5:   lv_input := machine;
[Mystery Machine]
 
PL/SQL procedure successfully completed.

The parameter name becomes an identifier and maps to the variable machine. That mapping means it prints the value of the machine variable.

While this is what we’d call a terminal use case, it is a fun way to illustrate an odd PL/SQL behavior. As always, I hope its interesting for those who read it.

After downloading and installing MySQL 8.0.24 yesterday, I opened a command shell. In the command shell, I could access the MySQL Shell (mysqlsh.exe) but not the MySQL Client (mysql.exe). Typing in the following:

C:\WINDOWS\system32>mysql

It returned:

'mysql' is not recognized as an internal or external command,
operable program or batch file.

The MySQL Client (mysql.exe) was installed because MySQL Workbench relies on it. However, the MySQL Microsoft Software Installer (MSI) does not put the mysql.exe file’s directory in the common Windows %PATH% environment variable. You can find the required %PATH% directory variable by opening the File Manager and searching for the mysql.exe file.

You should return several directories and programs but the directory you want is:

C:\Program Files\MySQL\MySQL Server 8.0\bin

You can test it by using the SET command in the Microsoft Operating System, like this:

SET PATH=C:\Program Files\MySQL\MySQL Server 8.0\bin;%PATH%

You can now call the mysql.exe program in your current shell session with the following syntax:

mysql -uroot -p

You will be prompted for the password and then connected to the database as follows:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.24 MySQL Community Server - GPL
 
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>

Unfortunately, the SET command only sets the %PATH% environment variable in the current session. You can set the system %PATH% environment variable globally by following these steps:

1. In Search, search for and then select: System (Control Panel)
2. In Settings dialog enter “Environment” in the search box and it will display:

   
   

3. Chose “Edit the system environment variables” option. You will see the following “System Properties” dialog:



4. Click the “Environment Variable” button to display the “Environment Variables” dialog. Click on the Path system variable before clicking the Edit button beneath.

   

5. Click the “New” button and enter “C:\Program Files\MySQL\MySQL Server 8.0\bin, and click the “OK” button. The next time you query the computer system’s %PATH% environment variable, it will show you the list of path locations that the operating system looks at for command files. It’s actually stored as a semicolon-delimited list in Windows 10 (and, as a colon-delimited list in Linux or Unix).

   

The next time you open a command shell, the %PATH% environment variable will find the mysql.exe program. As always, I hope these instructions help the reader.

Sometimes a bit of humor helps with a new topic. Creating a database in MongoDB is a two-step process, like the Texas Two-Step (a nick name for a country/western two-step danced in common time). A Texas Two Step is a one-two, one-two shuffle which is like the two-step process for how you create a MongoDB database.

While databases in MongoDB are a multiuser sandbox like a relational database, you can’t simply create them and grant them privileges. You must first USE the database and then put a collection (a.k.a., equivalent to a table in MySQL) in it. This blog post shows you how to create a MongoDB play database with an actor collection. It shows you the commands to create the a database with one collection in it and how to verify its existence.

You connect through the mongo shell, like:

mongo

The first-step requires you to connect to the play database even though it doesn’t exist. You do that by typing:

> use play

You can verify you’re in the play database by typing the db command, which the mongo shell treats as an expression. It returns play, as the name of the current database.

However, when you call the shell show dbs helper:

> show dbs

It displays:

admin   0.000GB
config  0.000GB
local   0.000GB

You also should note that the play database still doesn’t exist when you run the JavaScript equivalent to the shell show dbs helper:

> db.getMongo().getDBs()

It returns the following list of databases, which excludes the as yet not created play database:

{
  "databases" : [
    {
      "name" : "admin",
      "sizeOnDisk" : 32768,
      "empty" : false
    },
    {
      "name" : "config",
      "sizeOnDisk" : 61440,
      "empty" : false
    },
    {
      "name" : "local",
      "sizeOnDisk" : 81920,
      "empty" : false
    }
  ],
  "totalSize" : 176128,
  "ok" : 1
}

You create an actor collection (a.k.a. the equivalent of a relational table) with the following syntax, where db maps to the play database that you’re using. Note that you must create or insert one document to begin a document collection. While you can use the insert method, you should use either the newer insertOne() or insertMany() methods.

> db.actors.insertOne({"actor" : {"first_name" : "Chris", "last_name" : "Pine"}, "age" : 40 })

A new call to the shell show dbs helper:

> show dbs

displays your new play database:

admin   0.000GB
config  0.000GB
local   0.000GB
play    0.000GB

You can add two more documents (a.k.a. for rows in a relational database) with the insertMany() method:

> db.actors.insertMany([{"actor" : {"first_name" : "Chris", "last_name" : "Evans"}, "age" : 39 }
                       ,{"actor" : {"first_name" : "Chris", "last_name" : "Pratt"}, "age" : 41 }])

A quick word to the JavaScript novices out there. Don’t forget the square brackets ([{},{},...]) in the insertMany() method call or you’ll get an error like this:

2021-04-12T16:20:13.237-0600 E QUERY    [js] TypeError: documents.map is not a function :
DBCollection.prototype.insertMany@src/mongo/shell/crud_api.js:295:1
@(shell):1:1

You call the shell show collections helper or db.getMongo().getCollectionNames() JavaScript function to display the collections in the play database. The show collections displays a list of collections, and the db.getMongo().getCollectionNames() displays an JavaScript array of collections.

If you’re like me, Mongo’s db convention is a bit risky that I could do something in the wrong database. So, I put the following function into my .mongorc.js (a.k.a., MongoDB Resource file):

prompt = function() { var dbName = db; return dbName + "> " }

It ensures you will see the current database name to the left of the prompt (“>”), like:

play>

You can query the documents from the actors collection with the following:

play> db.actors.find().pretty()

It returns:

{
  "_id" : ObjectId("6074c692813c5a85db9cc9df"),
  "actor" : {
    "first_name" : "Chris",
    "last_name" : "Pine"
  },
  "age" : 40
}
{
  "_id" : ObjectId("6074c7ea813c5a85db9cc9e0"),
  "actor" : {
    "first_name" : "Chris",
    "last_name" : "Evans"
  },
  "age" : 39
}
{
  "_id" : ObjectId("6074c7ea813c5a85db9cc9e1"),
  "actor" : {
    "first_name" : "Chris",
    "last_name" : "Pratt"
  },
  "age" : 41
}

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

There are many ways to test and edit files. A lot of developers only use their favorite Integrated Developer Environment (IDE) but I find testing script files within the scope of a pipelined set of scripts much faster.

The ability to edit a JavaScript file from within the mongo Shell would be nice but unfortunately, it doesn’t exist. You are able to edit a complex variable with a mechanism quite like the Oracle Database. Rather than leave you hanging on how to edit a complex variable in the mongo shell, here are the steps before launching into how to test your JavaScript files:

1. You can enter this manually during any connection to the mongo shell or put it in your .mongorc.js configuration file, like this in Fedora with the fully qualified filename:

   EDITOR="/usr/bin/vim"

   EDITOR="/usr/bin/vim"

2. Let’s say you have a stooges array variable that contains “Moe”, “Curly”, and “Larry” and you want to add “Shemp” to the variable. You can edit the stooges array variable with vi and add “Shemp” to it by typing edit and the stooges variable name.

   edit stooges

   edit stooges

3. Then, you can test the stooges array variable’s new values:

   stooges

   stooges

4. It returns the following:

   [ "Moe", "Curly", "Shemp", "Larry" ]

   [ "Moe", "Curly", "Shemp", "Larry" ]

Unfortunately, these changes to the demo array variable will be lost after you break the connection. While it does afford a quick test case, you should make the changes in the external JavaScript file. Then, the change is there the the next time you access the resource file.

Here’s my quick edit and test script technique for MongoDB from your present working directory:

1. Assume you create a compliment.js test file, like:

   /* Declare an array variable and random index value. */ var compliment = ["Bashful","Doc","Dopey","Grumpy","Happy","Sleepy","Sneezy"] var index = Math.floor(Math.random()*7)   /* Print a welcome message. */ print("Hello, " + compliment[index] + ".")

   /* Declare an array variable and random index value. */ var compliment = ["Bashful","Doc","Dopey","Grumpy","Happy","Sleepy","Sneezy"] var index = Math.floor(Math.random()*7) /* Print a welcome message. */ print("Hello, " + compliment[index] + ".")

2. You can edit the compliment.js file with vi and test the script interactively from the present working directory.
   • You can edit the file with the following syntax:

     vi compliment.js

     vi compliment.js

   • then, you can test the task.sql file:

     mongo --nodb --norc < compliment.js

     mongo --nodb --norc < compliment.js

   • The --nodb option instructs MongoDB to not connect to a database and the --norc option instructs MongoDB to not load your .mongorc.js file. Effectively, disabling the database connection and loading of the MongoDB resource file (.mongorc.js) lets you test your code in MongoDB’s Javascript shell unencumbered by any overhead from the MongoDB server.

     The foregoing script returns the following:

     MongoDB shell version v4.0.19 Hello, Sneezy. bye

     MongoDB shell version v4.0.19 Hello, Sneezy. bye

   • If you have the desired outcome, you’re done. However, if you need further change you repeat the process.

As always, I hope this helps those looking for a quick way to accomplish a task.

There are many ways to test and edit files. A lot of developers only use their favorite Integrated Developer Environment (IDE) but I find testing script files within the scope of a pipelined set of scripts much faster.

The ability to edit a script from within the MySQL Command-Line Interface (CLI) or MySQL Shell would be nice but unfortunately, doesn’t exist. You can always subshell to edit a file or list files in the present working directory, like:

mysql> \! vi task.sql

I prefer to test at the OS level while leveraging the up-arrow key for command history. Here’s my quick edit and test script technique from your present working directory:

1. Assume you create a task.sql test file, like:

   SELECT user() AS "Current User"\G

   SELECT user() AS "Current User"\G

2. You can edit with vi or emac and test the script interactively from the present working directory.
   • You can edit the file with the following syntax:

     vi task.sql

     vi task.sql

   • then, you can test the task.sql file:

     mysql -ustudent -p -Dstudentdb < task.sql

     mysql -ustudent -p -Dstudentdb < task.sql

   • It returns the following:

     Enter password: *************************** 1. row *************************** Current User: student@localhost

     Enter password: *************************** 1. row *************************** Current User: student@localhost

   • If you have the desired outcome, you’re done. However, if you need further change you repeat the process.

As always, I hope this helps those looking for a quick way to accomplish a task.

It’s a problem for my students who purchased the new Apple hardware that uses Apple Silicon because they can’t install a Docker MySQL instance. However, there is Homebrew formula that works on macOS Big Sur and the new Apple silicon. It supports:

• Intel Silicon: macOS Big Sur, Catalina, and Mojave
• Apple Silicon: macOS Big Sur

The Homebrew Formula does have conflicts that you may need to avoid. It is a solution for those with the new Apple silicon.

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

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.

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.