Sometimes life is too surreal. Like when somebody says, “How do you get title case in an Oracle database?” That’s when you know three things about the individual, while suppressing laughter. They’re not very experienced with SQL, likely lazy, and don’t read the documentation.

I had a little fun with somebody today by taking them down a small rat-hole. “Oh, gosh … ” I said, “… let’s write a function for that.” Here’s the joke function, like:

CREATE OR REPLACE
FUNCTION title_case
( string VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN
  /* Change upper case to title case. */
  RETURN UPPER(SUBSTR(string,1,1)) || LOWER(SUBSTR(string,2,LENGTH(string)));
END title_case;
/

Then, we tested it with a query from the pseudo dual table:

SELECT title_case('incredible') AS "Proper Name" FROM dual;

It returned:

Proper Name
----------
Incredible

Then, I said “Oh, that’s not his …

My students liked the MySQL Transaction post but wanted one that showed how an external web application would interact with MySQL in the scope of a transaction. So, I put a little PHP function together that write across two related tables in the context of a transaction. It uses mysqli (MySQL Improved Extension) to connect PHP to the MySQL database.

The function is barebones and uses the oldest approach of hidden inputs to maintain context between rendered forms using an HTML POST method. The hidden inputs are preceded with “h_” and snake case is used for variable names.

The function only writes to two tables. It writes to the member table and when that completes successfully to the contact table. The function:

• Submits credentials from a file and raises an error when they don’t work.
• Initializes a …

Interesting questions always come via my students. For example, “Why does the selective aggregation sample return null values as totals from the SUM() function in MySQL?”

First, here’s the code to build the sample table for the problem:

DROP TABLE IF EXISTS transaction;
CREATE TABLE transaction
( transaction_id  int unsigned primary key auto_increment
, transaction_date  date
, transaction_amount  double );

INSERT INTO transaction
( transaction_date, transaction_amount )
VALUES
 ('2021-01-10', 56)
,('2021-02-14',23.02)
,('2021-03-31',31.06)
,('2021-01-01',.25)
,('2020-01-02', 52)
,('2020-02-08',22.02)
,('2020-03-26',32.06)
,('2020-01-12',.75);;

Now, here’s the selective aggregation query:

SELECT   EXTRACT(YEAR FROM transaction_date) AS "Year"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN transaction_amount
            END) AS "Jan"
,        SUM(
           CASE …

The students needed yet another example of LEFT JOIN, RIGHT JOIN, and FULL JOIN syntax (by combining a left and right join with the UNION set operator). To that end, I put this set of examples together.

The example also shows how to order the result set from a derived table with the UNION operator. It uses the WITH clause to build a Common Table Expression (CTE), which allows the query to order the UNION set operator’s product based on the left and right join queries. It uses a CASE statement to order the result sets. The left_table is the parent table and the right_table is the child table in the relationship, which means the right_table holds a left_id foreign key column that lets you connect matching rows in the left_table.

You build the little model with the following …

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 …

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.

1. 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));
   

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

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?

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

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 …