This was principally written for my SQL students but I thought it might be useful to others. SQL calculation are performed row-by-row in the SELECT-list. In its simplest form without even touching a table, you can add two literal numbers like this:

SELECT 2 + 2 AS result;

It will display the result of the addition to the column alias result as a derived table, or the following result:

+--------+
| result |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)

Unfortunately, the use of literal values as shown above doesn’t really let you see how the calculation is made row-by-row because it only returns one row. You can rewrite the two literal values into one variable by using a Common Table Expressions (CTEs). The CTE creates an struct tuple with only one x element. Another way to describe what the CTE does would say, it creates a derived table named struct with a single …

Working with my students to create an Ubuntu virtual environment for Python development with the MySQL database. After completing the general provisioning covered in this older post, I’d recommend you create a python symbolic link before installing the MySQL-Python driver.

sudo ln -s /usr/bin/python3 /usr/bin/python

You install the Python development driver with the following:

sudo apt-get -y install python3-mysql.connector

Create a python_connect.py file to test your Python deployment’s ability to connect to the MySQL database:

#!/usr/bin/python

# Import the library.
import mysql.connector
from mysql.connector import errorcode
 
try:
  # Open connection.
  cnx = mysql.connector.connect(user='student', password='student',
                                host='localhost', …

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:

-- 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; …

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 …

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 …

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. …

MySQL will be used for our online sections because the VMware instance and Docker configurations where too large to effectively download this term.

MySQL 8.0.21 Installation Steps

After you download the MySQL 8 MSI file, you will perform the following 24 steps to install MySQL on Windows 10. If you want a full developer install you must install Microsoft Excel and Visual Studio first.

1. The first thing you need to do is grant privileges to allow the MySQL Installer application to work in Windows 10. Click the Yes button to authorize the MySQL Installer to run.

1. The next thing you need to do is grant privileges to allow …

It’s the in-between term time and we’re all stuck at home. I decided to update the image for my Fedora 30 virtual machine. I had a work around to the update issue that I had encountered last October in Bug #96969 but it was not required with the current version. However, after updating from MySQL 8.0.17 to MySQL 8.0.19, I found that my Java connection example failed.

The $CLASSPATH value was correct:

/usr/share/java/mysql-connector-java.jar:.

The first error that I got was the my reference to MySQL JDBC driver was incorrect. The error message is quite clear:

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Cannot connect to database server:
The server time zone …

While building my student image on Fedora 30, I installed the MySQL PHP Connector (php-mysqlndrp) but neglected to install the Python Connector. This adds the installation and basic test of the Python Connector to the original blog post.

You use the following command with a wildcard as a privileged user. The wildcard is necessary because you need to load two libraries to support Python 2.7 and 3.7, which are installed on Fedora 30. You also need to be the root user or a user that is found in the sudoer’s list:

yum install -y mysql-connector-python*

Display detailed console log →

Last metadata expiration check: 0:35:46 ago on Tue 20 Aug 2019 05:36:29 PM MDT.
Dependencies resolved. …

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

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

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