MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘MySQL Connect/NET’ Category

Setting SQL_MODE

with one comment

In MySQL, the @@sql_mode parameter should generally use ONLY_FULL_GROUP_BY. If it doesn’t include it and you don’t have the ability to change the database parameters, you can use a MySQL PSM (Persistent Stored Module), like:

Create the set_full_group_by 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

-- Drop procedure conditionally on whether it exists already.
DROP PROCEDURE IF EXISTS set_full_group_by;
 
-- Reset delimter to allow semicolons to terminate statements.
DELIMITER $$
 
-- Create a procedure to verify and set connection parameter.
CREATE PROCEDURE set_full_group_by()
  LANGUAGE SQL
  NOT DETERMINISTIC
  SQL SECURITY DEFINER
  COMMENT 'Set connection parameter when not set.'
BEGIN
 
  /* Check whether full group by is set in the connection and
     if unset, set it in the scope of the connection. */
  IF NOT EXISTS
    (SELECT NULL
     WHERE  REGEXP_LIKE(@@SQL_MODE,'ONLY_FULL_GROUP_BY'))
  THEN
    SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));
  END IF;
END;
$$
 
-- Reset the default delimiter.
DELIMITER ;

Run the following SQL command before you attempt the exercises in the same session scope:

CALL set_full_group_by();

As always, I hope this helps those looking for a solution. Naturally, you can simply use the SET command on line #21 above.

Written by maclochlainn

March 23rd, 2022 at 12:18 pm

Posted in Database,DBA,Linux,MySQL,MySQL 8,MySQL Client,MySQL Connect,MySQL Connect/NET,MySQL Connect/ODBC,MySQL Connect/Python,Unix,Windows OS

Tagged with , ,

MySQL Connect Dialog

without comments

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.

Written by maclochlainn

May 21st, 2021 at 11:14 pm

Posted in Microsoft,Microsoft Windows 10,MySQL,MySQL 8,MySQL Client,MySQL Connect,MySQL Connect/NET,PowerShell,Windows 10

Tagged with ,

MySQL+Credentials

without comments

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.

Written by maclochlainn

April 28th, 2021 at 12:29 am

Posted in Microsoft Windows 10,MySQL,MySQL 8,MySQL Client,MySQL Connect,MySQL Connect/NET,Windows 10,Windows OS

Tagged with , ,