Archive for the ‘MySQL’ Category
MySQL INSERT-SET
I found myself explaining the nuances of INSERT statements and whether you should use named or positional notation. While the class was on Zoom, I could imagine the blank stares in the silence of my headphones. Then, I had to remind them about mandatory (NOT NULL constrained) and optional (nullable) columns in tables and how an INSERT statement requires an explicit NULL value for optional columns when the INSERT statement isn’t inserting a value into that column.
Then, I asked if somebody could qualify the different types of INSERT statements; and what would happen if a table with a first_name and last_name column order evolves when a new DBA decides to restructure the table and uses a last_name and first_name column order in the new table structure. Only a couple of the students recalled using a column-list between the table name and VALUES clause but none could remember how to write an INSERT-SET statement.
Below is a quick example of inserting data with column-lists and the SET clause. It builds an actor table with an actor_id column as a surrogate key and primary key column and a unique natural key composed of the first and last name columns (not a real world solution for uniqueness).
CREATE TABLE actor ( actor_id int unsigned primary key auto_increment , first_name varchar(30) not null , last_name varchar(30) not null , CONSTRAINT actor_uq UNIQUE (first_name, last_name)); |
Next, let’s insert a couple rows with a column-list approach. The column-list is a comma-delimited list of column values that must contain all mandatory columns and may contain optional columns.
INSERT INTO actor ( first_name , last_name ) VALUES ('Harrison','Ford') ,('Carrie','Fisher') ,('Mark','Hamill') ,('Alec','Guinness'); |
Now, let’s validate the unique constraint on the composite value of first and last name by trying to insert a second Harrison Ford into the actor table.
INSERT INTO actor (first_name, last_name) VALUES ('Harrison','Ford'); |
it fails and raises the following error:
ERROR 1062 (23000): Duplicate entry 'Harrison-Ford' for key 'actor.actor_uq' |
The following uses the INSERT-SET statement to add Tom Hanks to the actor table:
INSERT INTO actor SET first_name = 'Tom' , last_name = 'Hanks'; |
I believe the INSERT-SET is the best approach to a name-value model for INSERT statements. It’s a shame that only MySQL supports it. Query the actor table with the following:
SELECT * FROM actor ORDER BY actor_id; |
it returns:
+----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 1 | Harrison | Ford | | 2 | Carrie | Fisher | | 3 | Mark | Hamill | | 4 | Alec | Guinness | | 6 | Tom | Hanks | +----------+------------+-----------+ 5 rows in set (0.01 sec) |
There’s now a gap when you query the data because the second attempt at adding Harrison Ford consumed a sequence value from the internally managed list. That list is a property of the table when you create or alter a table to include an autoincrementing column, like actor_id. Anytime you attempt to insert a row that violates the table constraints, you consume a value from the internally managed sequence. While you can restore it and eliminate the gaps, I strongly recommend against it.
As always, I hope this helps those trying to learn and experiment with syntax.
MySQL Connect Dialog
About a month ago, I published how you can connect to MySQL with a small form. One suggestion, or lets promote it to a request, from that post was: “Nice, but how do you create a reusable library for the MySQL Connection Dialog box?”
That was a good question but I couldn’t get back until now to write a new blog post. This reusable MySQL connection dialog lets you remove MySQL connection data from the command-line history. This post also shows you how to create and test a Powershell Module.
The first step to create a module requires that you set the proper %PSModulePath% environment variable. If you fail to do that, you can put it into a default PowerShell module location but that’s not too effective for testing. You launch the System Properties dialog and click the Environment Variables button:
Then, you edit the PSModulePath environment variable in the bottom list of environment variables and add a new path to the PSModulePath. My development path in this example is:
C:\Data\cit225\mysql\ps\mod |
I named the file the same as the function Get-Credentials.psm1 consistent with the Microsoft instructions for creating a PowerShell module and their instructions for Pascal case name with an approved verb and singular noun.
Below is the code for the Get-Credentials.psm1 file:
function Get-Credentials { # 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 } else { $credentials = $null } return $credentials } |
You must create a Get-Connection directory in your C:\Data\cit225\mysql\ps\mod directory that you added to the PSModulePath. Then, you must put your module code in the Get-Connection subdirectory as the Get-Connection.psm1 module file.
The test.ps1 script imports the Get-Credentials.psm1 PowerShell module, launches the MySQL Connection Dialog form and returns the connection string. The test.ps1 code is:
# Import your custom module. Import-Module Get-Credentials # Test the Get-Credentials function. if (($credentials = Get-Credentials) -ne $undefinedVariable) { Write-Host($credentials) } |
You can test it from the local any directory with the following command-line:
powershell .\test.ps1 |
It should print something like this to the console:
server=localhost;port=3306;uid=student;pwd=student;database=studentdb |
If you got this far, that’s great! You’re ready to test a connection to the MySQL database. Before you do that, you should create the same avenger table I used in the initial post and insert the same or some additional data. Connect to the any of your test databases and rung the following code to create the avenger table and nine 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') ,('Peter', 'Parker', 'Spiderman') ,('Steven', 'Strange', 'Dr. Strange') ,('Scott', 'Lange', 'Ant-man'); |
Now, let’s promote our use-case test.ps1 script to a testQuery.ps1 script, like:
# Import your custom module. Import-Module Get-Credentials # Test the Get-Credentials function. if (($credentials = Get-Credentials) -ne $undefinedVariable) { # 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> # - port=<port#> or 3306 for default port # - 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() } |
It should give you the MySQL Connection Dialog and with the correct credentials print the following to your console:
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 Avenger: Spiderman is Peter Parker Avenger: Dr. Strange is Steven Strange Avenger: Ant-man is Scott Lange |
As always, I hope this helps those looking to exploit technology.
MySQL Transaction Unit
Many of my students wanted to know how to write a simple PSM (Persistent Stored Module) for MySQL that saved the writes to all table as a group. So, to that end here’s simple example.
- Create four sample tables in a re-runnable script file:
/* 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));
- Create a locking PSM across the four tables:
/* 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; /* 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 ;
- Test program for inserting the data:
/* Call locking procedure. */ CALL locking('Donald','Goofy','Mickey','Pluto'); CALL locking('Squirrel','Chipmunk','Monkey business','Raccoon'); CALL locking('Curly','Larry','Moe','Shemp');
- Verify the test results:
/* Select from tables, which should be empty. */ SELECT * FROM one; SELECT * FROM two; SELECT * FROM three; SELECT * FROM four;
As always, I hope this code complete example helps those trying to figure things out.
Customer ERD
Now that we’ve migrated to MySQL for our core database course, I’m building MySQL Workbench analysis problems. We start with a lecture trying to flush out a simple address, and then ask them to figure out how to link it to a customer table.
Designing it, I emphasized how it resolves the issue of a city occurring in multiple counties and states, like Fremont. Fremont occurs 17 times in the US and once in Haiti:
Naturally, I left two other design issues in the problem. I’m working through it for the first time with classes tomorrow. I hope it works well. Any comments?
MySQL+Credentials
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.
MySQL+PowerShell
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, 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.
Put MySQL in PATH
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:
- In Search, search for and then select: System (Control Panel)
- In Settings dialog enter “Environment” in the search box and it will display:
- Chose “Edit the system environment variables” option. You will see the following “System Properties” dialog:
- Click the “Environment Variable” button to display the “Environment Variables” dialog. Click on the Path system variable before clicking the Edit button beneath.
- 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.
MySQL Script Test
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:
- Assume you create a
task.sqltest file, like:SELECT user() AS "Current User"\G
- You can edit with
vioremacand test the script interactively from the present working directory.- You can edit the file with the following syntax:
vi task.sql -
then, you can test the
task.sqlfile:mysql -ustudent -p -Dstudentdb < task.sql
- It returns the following:
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.
- You can edit the file with the following syntax:
As always, I hope this helps those looking for a quick way to accomplish a task.
MySQL & macOS Silicon
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.
MySQL Transaction Scope
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
EXIThandler that undoes any writes before an error in a sequence of DDL commands, like theINSERT,UPDATE, orDELETEstatements. TheEXIThandler 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
INSERTstatements 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.
/* 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.
Subscribe via RSS