MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘MySQL Developer’ tag

MySQL with CTEs

without comments

As an example for my class on the usefulness of Common Table Expressions (CTEs), I created three examples with Python. They extend an exercise in Chapter 9 on subqueries from Learning SQL by Alan Beaulieu. All of the examples work with the sakila sample database.

These bullets describe the examples:

  1. Uses local variables and a range for loop and if statement that uses the variables to evaluate and add an element to the derived table (or query result set) from MySQL.
  2. Uses a CTE with substitution variables from the Python program, which eliminates the need to evaluate and add an element to the query result set because the query does that.
  3. Uses a table to hold the variables necessary to evaluate and add the element to the query result set.

    4. This is the first Python program:

    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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

    # Import the library.
import sys
import mysql.connector
from mysql.connector import errorcode
 
# Declare a list of tuples.
dict = [{'level':'Hollywood Star','min_roles':30,'max_roles':99999}
       ,{'level':'Prolific Actor','min_roles':20,'max_roles':29}
       ,{'level':'Newcomer','min_roles':1,'max_roles':19}]
 
#  Attempt the query.
# ============================================================
#  Use a try-catch block to manage the connection.
# ============================================================
try:
  # Open connection.
  cnx = mysql.connector.connect(user='student', password='student',
                                host='127.0.0.1',
                                database='sakila')
  # Create cursor.
  cursor = cnx.cursor()
 
  # Set the query statement.
  query = ("SELECT   a.actor_id "
           ",        a.first_name       "
           ",        a.last_name "
           ",        COUNT(fa.actor_id) AS films "
           "FROM     actor a INNER JOIN film_actor fa "
           "ON       a.actor_id = fa.actor_id "
           "GROUP BY a.actor_id "
           ",        a.first_name "
           ",        a.last_name "
           "ORDER BY a.last_name "
           ",        a.first_name")
 
  # Execute cursor.
  cursor.execute(query)
 
  # Display the rows returned by the query.
  for (actor_id, first_name, last_name, films) in cursor:
    for i in range(len(dict)):
      if films >= dict[i]["min_roles"] and films <= dict[i]["max_roles"]:
        print('{0} {1} is a {2} with {3} films.'.format( first_name.title()
                                                       , last_name.title()
                                                       , dict[i]["level"]
                                                       , films))
 
  # Close cursor.
  cursor.close()
 
# ------------------------------------------------------------
# Handle exception and close connection.
except mysql.connector.Error as e:
  if e.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif e.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print("Error code:", e.errno)        # error number
    print("SQLSTATE value:", e.sqlstate) # SQLSTATE value
    print("Error message:", e.msg)       # error message
 
# Close the connection when the try block completes.
else:
  cnx.close()

    The Python dictionary on lines 7 thru 9 and range for loop and if statement on lines 41 and 42 can be eliminated by putting the literal values in a Common Table Expression (CTE). That’s because a CROSS JOIN matches all rows in the CTE against the base table before filtering them.

    The match of all rows in the CTE against the base table effectively replaces the range for loop in the original code. The WHERE clause replaces the if statement in the original code.

    Another optimization for readability of the final query puts the grouped query into a CTE as well. That way the final query simply demonstrates the filtering process.

    This is the second Python program, and it converts the Python dictionary to a list of lists and assigns the lists to param tuple:

    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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

    # Import the library.
import sys
import mysql.connector
from mysql.connector import errorcode
 
# Declare a list of lists.
list = [['Hollywood Star',30,99999]
       ,['Prolific Actor',20,29]
       ,['Newcomer',1,19]]
 
# Declare a tuple of the set of lists.
param = (list[0] + list[1] + list[2])
 
#  Attempt the query.
# ============================================================
#  Use a try-catch block to manage the connection.
# ============================================================
try:
  # Open connection.
  cnx = mysql.connector.connect(user='student', password='student',
                                host='127.0.0.1',
                                database='sakila')
  # Create cursor.
  cursor = cnx.cursor()
 
  # Set the query statement.
  query = ("WITH actors AS "
           "(SELECT   a.actor_id "
           " ,        a.first_name "
           " ,        a.last_name "
           " ,        COUNT(fa.actor_id) AS num_roles "
           " FROM     actor a INNER JOIN film_actor fa "
           " ON       a.actor_id = fa.actor_id "
           " GROUP BY a.actor_id "
           " ,        a.first_name "
           " ,        a.last_name ) "
           " , levels AS "
           "(SELECT  %s AS level "
           " ,       %s AS min_roles "
           " ,       %s AS max_roles "
           " UNION ALL "
           " SELECT  %s AS level "
           " ,       %s AS min_roles "
           " ,       %s AS max_roles "
           " UNION ALL "
           " SELECT  %s AS level "
           " ,       %s AS min_roles "
           " ,       %s AS max_roles) "
           " SELECT a.first_name "
           " ,      a.last_name "
           " ,      l.level "
           " ,      a.num_roles "
           " FROM   actors a CROSS JOIN levels l "
           " WHERE  a.num_roles BETWEEN l.min_roles AND l.max_roles "
           " ORDER BY a.last_name "
           " ,        a.first_name")
 
  # Execute cursor.
  cursor.execute(query, param)
 
  # Display the rows returned by the query.
  for (first_name, last_name, level, num_roles) in cursor:
    print('{0} {1} is a {2} with {3} films.'.format( first_name.title()
                                                   , last_name.title()
                                                   , level.title()
                                                   , num_roles))
 
  # Close cursor.
  cursor.close()
 
# ------------------------------------------------------------
# Handle exception and close connection.
except mysql.connector.Error as e:
  if e.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif e.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print("Error code:", e.errno)        # error number
    print("SQLSTATE value:", e.sqlstate) # SQLSTATE value
    print("Error message:", e.msg)       # error message
 
# Close the connection when the try block completes.
else:
  cnx.close()

    This is the third Python program requires some SQL setup. You should run this script inside the sakila database first. It basically takes the variables out of the code and stores them in a table. This is more likely what you would do to ensure maintainability of ever changing range values like these if you built a solution like this in a real application. It leaves the aggregation process inside a CTE and simplifies the final query.

    -- Conditionally drop the levels table.
DROP TABLE IF EXISTS levels;
 
-- Create the levels list.
CREATE TABLE levels
( level      VARCHAR(16)
, min_roles  INT
, max_roles  INT );
 
-- Insert values into the list table.
INSERT INTO levels
( level, min_roles, max_roles )
VALUES
 ('Hollywood Star', 30, 99999)
,('Prolific Actor', 20, 29)
,('Newcommer',1,19);

    After seeding the data in the levels table, you can test the query natively in MySQL, like this:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

    -- Query the data.
WITH actors AS
 (SELECT   a.actor_id
  ,        a.first_name
  ,        a.last_name
  ,        COUNT(*) AS num_roles
  FROM     actor a INNER JOIN film_actor fa
  ON       a.actor_id = fa.actor_id
  GROUP BY actor_id)
SELECT   a.first_name
,        a.last_name
,        l.level
,        a.num_roles
FROM     actors a CROSS JOIN levels l
WHERE    a.num_roles BETWEEN l.min_roles AND l.max_roles
ORDER BY a.last_name
,        a.first_name;

    There’s also a syntax that makes this type of query appear to be an INNER JOIN when it’s actually a filtered CROSS JOIN. If you adopt that syntax, you would rewrite lines 14 and 15:

    14
15

    FROM   actors a INNER JOIN levels l
WHERE  a.num_roles BETWEEN l.min_roles AND l.max_roles;

    Then, you can run this version without the second CTE element:

    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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

    # Import the library.
import sys
import mysql.connector
from mysql.connector import errorcode
 
#  Attempt the query.
# ============================================================
#  Use a try-catch block to manage the connection.
# ============================================================
try:
  # Open connection.
  cnx = mysql.connector.connect(user='student', password='student',
                                host='127.0.0.1',
                                database='sakila')
  # Create cursor.
  cursor = cnx.cursor()
 
  # Set the query statement.
  query = ("WITH actors AS "
           "(SELECT   a.actor_id "
           " ,        a.first_name "
           " ,        a.last_name "
           " ,        COUNT(fa.actor_id) AS num_roles "
           " FROM     actor a INNER JOIN film_actor fa "
           " ON       a.actor_id = fa.actor_id "
           " GROUP BY a.actor_id "
           " ,        a.first_name "
           " ,        a.last_name ) "
           " SELECT   a.first_name "
           " ,        a.last_name "
           " ,        l.level "
           " ,        a.num_roles "
           " FROM     actors a CROSS JOIN levels l "
           " WHERE    a.num_roles BETWEEN l.min_roles AND l.max_roles "
           " ORDER BY a.last_name "
           " ,        a.first_name")
 
  # Execute cursor.
  cursor.execute(query)
 
  # Display the rows returned by the query.
  for (first_name, last_name, level, num_roles) in cursor:
    print('{0} {1} is a {2} with {3} films.'.format( first_name.title()
                                                   , last_name.title()
                                                   , level.title()
                                                   , num_roles))
 
  # Close cursor.
  cursor.close()
 
# ------------------------------------------------------------
# Handle exception and close connection.
except mysql.connector.Error as e:
  if e.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif e.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print("Error code:", e.errno)        # error number
    print("SQLSTATE value:", e.sqlstate) # SQLSTATE value
    print("Error message:", e.msg)       # error message
 
# Close the connection when the try block completes.
else:
  cnx.close()

    As always, I hope this helps those trying to understand how CTEs can solve problems that would otherwise be coded in external imperative languages like Python.

Written by maclochlainn

November 3rd, 2021 at 10:01 am

Posted in Fedora,Linux,MySQL,MySQL 8,Python,Python 3.x,sql

Tagged with , ,

MySQL and JavaScript

without comments

Sometimes students truly amaze me with their curiosity. The question was simple from their perspective while we were discussing MySQL’s builtin string functions. How would you do something like this JavaScript logic without using literals or session variables?

// Declare a string and substring.
var myStr = 'Get me from the string.'
var mySubstr = 'me'
 
// Assign the substring to variable by rescuing it from the larger string.
var rescued = myStr.substring(myStr.indexOf(mySubstr),myStr.indexOf(mySubstr) + mySubstr.length)
 
// Print the result.
print(rescued)

tested with MongoDB, like

mongo --nodb --norc < parsing.js

returning:

MongoDB shell version v4.0.20
me
bye

They thought the question would show SQL’s limits as a problem solving and programming language because they didn’t see how MySQL could assign a variable for evaluation in the builtin functions.

They were surprised to see how I showed them that they could do it. Since they disallowed session variables, I built a sample table and inserted the string value in a text column before writing a query with a Common Table Expression (CTE).

The MySQL steps are:

-- Stable testing scenario with table values requires a test table.
DROP TABLE IF EXISTS sample;
CREATE TABLE sample
( text  VARCHAR(100) );
 
-- Insert the literal string into the testing table.
INSERT INTO sample
( text )
VALUES
('Get me from the string.');
 
-- Test using a WITH clause to place a variable in context for use
-- in the query, relying on the fact that a Cartesian set of one
-- column and row becomes a new column in all rows of the other
-- table's set.
WITH struct AS
(SELECT 'me' AS result)
 SELECT SUBSTR(text,INSTR(text,struct.result),LENGTH(struct.result)) AS rescued
 FROM   sample CROSS JOIN struct;

It returns the following:

+---------+
| rescued |
+---------+
| me      |
+---------+
1 row in set (0.00 sec)

Wow, SQL works like a programming language was the response of the class. It’s like anything else in technology, new stuff isn’t as cool as old stuff until you learn how to use it.

Written by maclochlainn

October 24th, 2021 at 10:48 am

Posted in JavaScript,Linux,MySQL,MySQL 8,MySQL Client,MySQL Shell,sql

Tagged with , ,

MySQL SQL Filters

without comments

An interesting outcome of teaching SQL is discovering what skills new users require. One that I continuously rediscover is how to build a test case for various elements of SQL. This is a small article on querying with filters in the WHERE clause.

There are several of the exercises in Alan Beaulieu’s Learning SQL, 3rd Edition that would benefit from example setup. For example, Chapter 4 provides a snapshot of the payment table but doesn’t provide any instructions.

You can create an exercise_4_2 table with the following SQL statement if you plan to change the data:

CREATE TABLE exercise_4_2 AS
SELECT payment_id
,      customer_id
,      amount
,      payment_date
FROM   payment
WHERE  payment_id BETWEEN 101 AND 120;

Alternatively, you can create an exercise_4_2 view with the following SQL statement if you plan to only query the data:

CREATE VIEW exercise_4_2 AS
SELECT payment_id
,      customer_id
,      amount
,      payment_date
FROM   payment
WHERE  payment_id BETWEEN 101 AND 120;

After creating the new exercise_4_2 table or view from the payment table it will hold a subset of data. You can query all the rows from the new exercise_4_2 table or view with this statement:

SELECT *
FROM   exercise_4_2;

It returns the following data set:

+------------+-------------+--------+---------------------+
| payment_id | customer_id | amount | payment_date        |
+------------+-------------+--------+---------------------+
|        101 |           4 |   8.99 | 2005-08-18 05:14:44 |
|        102 |           4 |   1.99 | 2005-08-19 02:19:13 |
|        103 |           4 |   2.99 | 2005-08-20 09:32:04 |
|        104 |           4 |   6.99 | 2005-08-20 12:55:40 |
|        105 |           4 |   4.99 | 2005-08-21 04:53:37 |
|        106 |           4 |   2.99 | 2005-08-22 13:58:23 |
|        107 |           4 |   1.99 | 2005-08-23 07:43:00 |
|        108 |           5 |   0.99 | 2005-05-29 07:25:16 |
|        109 |           5 |   6.99 | 2005-05-31 11:15:43 |
|        110 |           5 |   1.99 | 2005-05-31 19:46:38 |
|        111 |           5 |   3.99 | 2005-06-15 22:03:14 |
|        112 |           5 |   2.99 | 2005-06-16 08:01:02 |
|        113 |           5 |   4.99 | 2005-06-17 15:56:53 |
|        114 |           5 |   2.99 | 2005-06-19 04:20:13 |
|        115 |           5 |   4.99 | 2005-06-20 18:38:22 |
|        116 |           5 |   4.99 | 2005-07-06 09:11:58 |
|        117 |           5 |   2.99 | 2005-07-08 20:04:43 |
|        118 |           5 |   4.99 | 2005-07-09 01:57:57 |
|        119 |           5 |   5.99 | 2005-07-09 07:13:52 |
|        120 |           5 |   1.99 | 2005-07-09 08:51:42 |
+------------+-------------+--------+---------------------+
20 rows in set (0.26 sec)

With the exercise_4_2 table, you can test the exercises 4-1 and 4-2. Here are the two problems:

  • The first exercise checks for rows where the customer_id is not equal to 5 and whether the amount is greater than 8 or payment_date is equal to ‘2005-08-23’. You can structure that question as the following query: 

    mysql> SELECT   *
    -> FROM     exercise_4_2
    -> WHERE    customer_id <> 5
    -> AND     (amount > 8 OR DATE(payment_date) = '2005-08-23');

    It would return the following two rows from the exercise_4_2 table:

    +------------+-------------+--------+---------------------+
| payment_id | customer_id | amount | payment_date        |
+------------+-------------+--------+---------------------+
|        101 |           4 |   8.99 | 2005-08-18 05:14:44 |
|        107 |           4 |   1.99 | 2005-08-23 07:43:00 |
+------------+-------------+--------+---------------------+
2 rows in set (0.14 sec)
  • The second exercise checks for rows where the customer_id is equal to 5 and whether the amount is not greater than 6 or payment_date is not equal to ‘2005-06-19’. You can structure that question as the following query: 

    mysql> SELECT   *
    -> FROM     exercise_4_2
    -> WHERE    customer_id = 5
    -> AND NOT (amount > 6 OR DATE(payment_date) = '2005-06-19');

    It would return the following eleven rows from the exercise_4_2 table:

    +------------+-------------+--------+---------------------+
| payment_id | customer_id | amount | payment_date        |
+------------+-------------+--------+---------------------+
|        108 |           5 |   0.99 | 2005-05-29 07:25:16 |
|        110 |           5 |   1.99 | 2005-05-31 19:46:38 |
|        111 |           5 |   3.99 | 2005-06-15 22:03:14 |
|        112 |           5 |   2.99 | 2005-06-16 08:01:02 |
|        113 |           5 |   4.99 | 2005-06-17 15:56:53 |
|        115 |           5 |   4.99 | 2005-06-20 18:38:22 |
|        116 |           5 |   4.99 | 2005-07-06 09:11:58 |
|        117 |           5 |   2.99 | 2005-07-08 20:04:43 |
|        118 |           5 |   4.99 | 2005-07-09 01:57:57 |
|        119 |           5 |   5.99 | 2005-07-09 07:13:52 |
|        120 |           5 |   1.99 | 2005-07-09 08:51:42 |
+------------+-------------+--------+---------------------+
11 rows in set (0.00 sec)
  • The third exercise checks for payment_id and amount values where the amount is either 1.98, 7.98, or 9.98. You can structure that question as the following query: 

    mysql> SELECT payment_id
    -> ,      amount
    -> FROM   payment
    -> WHERE  amount IN (1.98,7.98,9.98);

    It would return the following seven rows from the payment table:

    +------------+--------+
| payment_id | amount |
+------------+--------+
|       1482 |   7.98 |
|       1670 |   9.98 |
|       2901 |   1.98 |
|       4234 |   7.98 |
|       4449 |   7.98 |
|       7243 |   7.98 |
|       9585 |   7.98 |
+------------+--------+
7 rows in set (0.00 sec)
  • The fourth exercise checks for the first_name and last_name of customers where the last_name contains an ‘A’ in the second position and a ‘W’ after the ‘A’ character. You can structure that question as the following query: 

    mysql> SELECT first_name
    -> ,      last_name
    -> FROM   customer
    -> WHERE  last_name LIKE '_A%W%';

    The trick to the WHERE clause is that the ‘%’ looks for zero to many characters in between two strings.

    It would return the following nine rows from the customer table:

    +------------+------------+
| first_name | last_name  |
+------------+------------+
| JILL       | HAWKINS    |
| ERICA      | MATTHEWS   |
| LAURIE     | LAWRENCE   |
| JEANNE     | LAWSON     |
| KAY        | CALDWELL   |
| JOHN       | FARNSWORTH |
| SAMUEL     | MARLOW     |
| LAWRENCE   | LAWTON     |
| LEE        | HAWKS      |
+------------+------------+
9 rows in set (0.10 sec)

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

Written by maclochlainn

October 4th, 2021 at 12:39 am

Posted in MySQL,MySQL 8,sql

Tagged with ,

Title Case Anyone?

without comments

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 proper name in the Pixar World.” It should be: Mr. Incredible. Let’s try that:

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

It returned:

Proper Name
--------------
Mr. incredible

Then, I said: “That’s not what we want at all. Should we rewrite our function or simply use the INITCAP built-in function?” Then, I wrote:

SELECT INITCAP('mr. incredible') AS "Proper Name" FROM dual;

It returned:

Proper Name
--------------
Mr. Incredible

Well, needless to say my acquaintance got it immediately and said “I should have Googled it or read the documentation.” I concurred with his observation.

Just sharing a cute day’s event that made me laugh and cry at the same time because there are too many who say SQL isn’t worth learning.

Written by maclochlainn

June 30th, 2021 at 11:49 am

Posted in Linux,MySQL,MySQL 8,Oracle,Oracle 18c,sql,Unix,Windows 10

Tagged with ,

MySQL PHP Transaction

without comments

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 SQL statement.
  • Disables auto commit.
  • Starts a transaction.
  • Defines a first SQL statement with placeholders.
  • Binds local variables to the first SQL statement’s placeholders.
  • Rolls back the transaction when the first statement fails and continues to the next SQL statement when first statement succeeds.
  • Defines a second SQL statement with placeholders.
  • Binds local variables to the second SQL statement’s placeholders.
  • Rolls back the transaction when the second statement fails and commits the work when the second statement succeeds.
  • Closes a SQL statement.

The code for the PHP function is:

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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129

/*
||  Function Name: update_membership
|| ----------------------------------------------------------------------
||  No return, treated as a procedure, or method returning a void.
|| ----------------------------------------------------------------------
*/
function update_membership(
    $h_account_number
  , $h_member_type
  , $h_credit_card_number
  , $h_credit_card_type
  , $account_number
  , $member_type
  , $credit_card_number
  , $credit_card_type
  , $h_first_name
  , $h_middle_name
  , $h_last_name
  , $h_contact_type
  , $first_name
  , $middle_name
  , $last_name
  , $contact_type) {
 
  // Include the credentials file if omitted.
  include_once("MySQLCredentials.inc");
 
  // Assign credentials to connection.
  $mysqli = new mysqli(HOSTNAME, USERNAME, PASSWORD, DATABASE);
 
  // Check for connection error and print message.
  if ($mysqli->connect_errno) {
    print $mysqli->connect_error."<br />";
    print "Connection not established ...<br />";
  }
  else {
    // Initial statement.
    $stmt = $mysqli->stmt_init();
 
    // Disable auto commit when you want two plus statements run.
    $mysqli->autocommit(FALSE);
 
    // Set the transaction guarantee.
    $mysqli->begin_transaction(MYSQLI_TRANS_START_READ_WRITE);
 
    // Declare a static query.
    $sql = "UPDATE   member\n"
         . "SET      member_type = ?\n"
         . ",        credit_card_number = ?\n"
         . ",        credit_card_type = ?\n"
         . "WHERE    account_number = ?\n"
         . "AND      member_type = ?\n"
         . "AND      credit_card_number = ?\n"
         . "AND      credit_card_type = ?\n";
 
    // Prepare statement.
    if ($stmt->prepare($sql)) {
      $stmt->bind_param(
          "sssssss"
         , $member_type
         , $credit_card_number
         , $credit_card_type
         , $account_number
         , $h_member_type
         , $h_credit_card_number
         , $h_credit_card_type); } 
 
    // Attempt query and exit with failure before processing.
    if (!$stmt->execute()) {
 
      // Rollback or undo the transaction.
      $mysqli->rollback();
 
      // Print failure to resolve query message.
      print $mysqli->error."<br />\n";
      print "Failed to resolve query ...<br />\n";
    }
 
    // Declare a static query.
    $sql = "UPDATE   contact\n"
         . "SET      first_name = ?\n"
         . ",        middle_name = ?\n"
         . ",        last_name = ?\n"
         . ",        contact_type = ?\n"
         . "WHERE    first_name = ?\n"
         . "AND      middle_name = ?\n"
         . "AND      last_name = ?\n"
         . "AND      contact_type = ?\n";
 
    // Prepare statement.
    if ($stmt->prepare($sql)) {
      $stmt->bind_param(
          "ssssssss"
        , $first_name
        , $middle_name
        , $last_name
        , $contact_type
        , $h_first_name
        , $h_middle_name
        , $h_last_name
        , $h_contact_type); } 
 
    // Attempt query and exit with failure before processing.
    if (!$stmt->execute()) {
 
      // Rollback or undo the transaction.
      $mysqli->rollback();
 
      // Print failure to resolve query message.
      print $mysqli->error."<br />";
      print "Failed to resolve query ...<br />";
    }
    else {   
      /*  Manually commiting writes when you have disabled the
      ||  default auto commit setting, explained above.
      || ------------------------------------------------------------
      ||  You would add the following command to commit the 
      ||  transaction.
      ||  ------------------------------
      ||   $mysqli->commit();
      || ------------------------------------------------------------
      */
      $mysqli->commit();
 
      // Close the transaction.
      $mysqli->close();
    }
  }
}

Line 41 disables auto commit and line 44 starts the transaction. Each statement is managed with the subsequent statement nested inside a block of code that is only reachable when the prior statement succeeds. While this only uses the member and contact table, it could use any number of tables. The natural alternative is building an updatable view.

As always, I hope this helps anybody looking for a code complete example.

Written by maclochlainn

June 29th, 2021 at 5:23 pm

Posted in LAMP,Linux,MySQL,MySQL 8,mysqli,sql

Tagged with ,

SQL Handling Nulls

without comments

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
             WHEN EXTRACT(MONTH FROM transaction_date) = 2 THEN transaction_amount
           END) AS "Feb"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 3 THEN transaction_amount
           END) AS "Mar"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) IN (1,2,3) THEN transaction_amount
           END) AS "1FQ"
FROM     transaction
WHERE    transaction_date BETWEEN '2020-01-15' AND '2021-03-15'
GROUP BY EXTRACT(YEAR FROM transaction_date)
ORDER BY 1;

It produces the following correct results (at least in response to the query above):

+------+-------+-------+-------+-------+
| Year | Jan   | Feb   | Mar   | 1FQ   |
+------+-------+-------+-------+-------+
| 2020 |  NULL | 22.02 | 32.06 | 54.08 |
| 2021 | 56.25 | 23.02 |  NULL | 79.27 |
+------+-------+-------+-------+-------+
2 rows in set (0.02 sec)

Why do you get null values for January 2020 and March 2021? That’s because the query returns null values when the conditions in the SELECT-list aren’t met for a row return. This happens:

  • When a row is returned for the month of February or March a null is returned in the January column.
  • When a row is returned for the month of January or March a null is returned in the February column.
  • When a row is returned for the month of January or February a null is returned in the March column.

That means there needs to be an ELSE clause in each of the CASE statements that sets the return value to zero. For example, the following query includes the ELSE clause and some nice formatting tricks:

SELECT   EXTRACT(YEAR FROM transaction_date) AS "Year"
,        CONCAT('$',LPAD(FORMAT(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN transaction_amount
             ELSE 0
            END),2),8,' ')) AS "Jan"
,        LPAD(FORMAT(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 2 THEN transaction_amount
             ELSE 0
           END),2),8,' ') AS "Feb"
,        LPAD(FORMAT(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 3 THEN transaction_amount
             ELSE 0
           END),2),8,' ') AS "Mar"
,        LPAD(FORMAT(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) IN (1,2,3) THEN transaction_amount
             ELSE 0
           END),2),8,' ') AS "1FQ"
FROM     transaction
WHERE    transaction_date BETWEEN '2020-01-15' AND '2021-03-15'
GROUP BY EXTRACT(YEAR FROM transaction_date)
ORDER BY 1;

It returns:

+------+-----------+----------+----------+----------+
| Year | Jan       | Feb      | Mar      | 1FQ      |
+------+-----------+----------+----------+----------+
| 2020 | $    0.00 |    22.02 |    32.06 |    54.08 |
| 2021 | $   56.25 |    23.02 |     0.00 |    79.27 |
+------+-----------+----------+----------+----------+
2 rows in set (0.01 sec)

As always, I hope this helps answer a question that somebody is trying to sort out.

Written by maclochlainn

June 23rd, 2021 at 12:23 pm

Posted in MySQL,MySQL 8,MySQL Client,MySQL Workbench

Tagged with ,

MySQL Outer Joins

without comments

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 script:

-- -----------------------------------------------------------------
-- Drop the demonstration tables.
-- -----------------------------------------------------------------
DROP TABLE IF EXISTS left_table, right_table;
 
-- -----------------------------------------------------------------
-- Create left_table.
-- -----------------------------------------------------------------
CREATE TABLE left_table
( left_id        int unsigned primary key auto_increment
, leftstring     varchar(10));
 
-- -----------------------------------------------------------------
-- Create left_table.
-- -----------------------------------------------------------------
CREATE TABLE right_table
( right_id       int unsigned primary key auto_increment
, left_id        int unsigned
, rightstring         varchar(10));
 
-- -----------------------------------------------------------------
-- Insert five rows to the left table, which holds a 
-- left_id primary key column.
-- -----------------------------------------------------------------
INSERT INTO left_table (leftstring) values ('One');
INSERT INTO left_table (leftstring) values ('Two');
INSERT INTO left_table (leftstring) values ('Three');
INSERT INTO left_table (leftstring) values ('Four');
INSERT INTO left_table (leftstring) values ('Five');
 
-- -----------------------------------------------------------------
-- Delete row four to create a gap.
-- -----------------------------------------------------------------
DELETE FROM left_table where left_id = 4;
 
-- -----------------------------------------------------------------
--  Insert four rows, skipping a foreign key value for the 
--  left_id primary key value of 2.
-- -----------------------------------------------------------------
 
INSERT INTO right_table (rightstring,left_id) values ('One',1);
INSERT INTO right_table (rightstring,left_id) values ('Three',3);
INSERT INTO right_table (rightstring,left_id) values ('Four',4);
INSERT INTO right_table (rightstring,left_id) values ('Five',5);

Here are the join statements:

INNER JOIN

The INNER JOIN only returns those rows that match between a primary and foreign key column or set of columns.

SELECT l.left_id
,      l.leftstring
,      r.left_id
,      r.right_id
,      r.rightstring
FROM   left_table l INNER JOIN right_table r
ON     l.left_id = r.left_id;

It produces the following result set:

+---------+------------+---------+----------+-------------+
| left_id | leftstring | left_id | right_id | rightstring |
+---------+------------+---------+----------+-------------+
|       1 | One        |       1 |        1 | One         |
|       3 | Three      |       3 |        2 | Three       |
|       5 | Five       |       5 |        4 | Five        |
+---------+------------+---------+----------+-------------+
3 rows in set (0.00 sec)

LEFT OUTER JOIN

The LEFT OUTER JOIN only returns those rows that match between a primary and foreign key column or set of columns and any rows in the table on the lefthand side of the join that fail to match with any row on the righthand side of the join. The non-matching rows are also known as the right complement of the join.

1
2
3
4
5
6
7

SELECT l.left_id
,      l.leftstring
,      r.left_id
,      r.right_id
,      r.rightstring
FROM   left_table l LEFT JOIN right_table r
ON     l.left_id = r.left_id;

It produces the following result set:

+---------+------------+---------+----------+-------------+
| left_id | leftstring | left_id | right_id | rightstring |
+---------+------------+---------+----------+-------------+
|       1 | One        |       1 |        1 | One         |
|       2 | Two        |    NULL |     NULL | NULL        |
|       3 | Three      |       3 |        2 | Three       |
|       5 | Five       |       5 |        4 | Five        |
+---------+------------+---------+----------+-------------+
4 rows in set (0.00 sec)

Add the following line 8 to the query and you get only those rows in the lefthand table that have no child-related rows in the righthand table. These rows are sometimes called childless parent rows. More or less, the use case for this type of query is to find order headers without order lines.

6
7
8

FROM   left_table l LEFT JOIN right_table r
ON     l.left_id = r.left_id
WHERE  r.left_id IS NULL;

It produces the following result set:

+---------+------------+---------+----------+-------------+
| left_id | leftstring | left_id | right_id | rightstring |
+---------+------------+---------+----------+-------------+
|       2 | Two        |    NULL |     NULL | NULL        |
+---------+------------+---------+----------+-------------+
1 row in set (0.00 sec)

RIGHT OUTER JOIN

1
2
3
4
5
6
7

SELECT l.left_id
,      l.leftstring
,      r.left_id
,      r.right_id
,      r.rightstring
FROM   left_table l RIGHT JOIN right_table r
ON     l.left_id = r.left_id;

It produces the following result set:

+---------+------------+---------+----------+-------------+
| left_id | leftstring | left_id | right_id | rightstring |
+---------+------------+---------+----------+-------------+
|       1 | One        |       1 |        1 | One         |
|       3 | Three      |       3 |        2 | Three       |
|    NULL | NULL       |       4 |        3 | Four        |
|       5 | Five       |       5 |        4 | Five        |
+---------+------------+---------+----------+-------------+
4 rows in set (0.00 sec)

Add the following line 8 to the query and you get only those rows in the righthand table that have no parent-related rows in the lefthand table. These rows are sometimes called orphans because they have no parent row. More or less, the use case for this type of query is to find latent order lines after deleting the order header.

6
7
8

FROM   left_table l LEFT JOIN right_table r
ON     l.left_id = r.left_id
WHERE  l.left_id IS NULL;

It produces the following result set:

+---------+------------+---------+----------+-------------+
| left_id | leftstring | left_id | right_id | rightstring |
+---------+------------+---------+----------+-------------+
|    NULL | NULL       |       4 |        3 | Four        |
+---------+------------+---------+----------+-------------+
1 row in set (0.00 sec)

FULL OUTER JOIN

The full outer join doesn’t exist in MySQL, so you combine a LEFT OUTER JOIN and RIGHT OUTER JOIN with the UNION operator. The UNION operator eliminates the duplicate row from the intersection of the joins.

Here’s the full query:

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

WITH cte AS
(SELECT l.left_id AS primary_left_id
,      l.leftstring
,      r.left_id  AS foreign_left_id
,      r.right_id
,      r.rightstring
FROM   left_table l LEFT JOIN right_table r
ON     l.left_id = r.left_id
UNION
SELECT l.left_id  AS primary_left_id
,      l.leftstring
,      r.left_id  AS foreign_left_id
,      r.right_id
,      r.rightstring
FROM   left_table l RIGHT JOIN right_table r
ON     l.left_id = r.left_id)
SELECT * FROM cte
ORDER BY
CASE
  WHEN 'One'   IN (leftstring, rightstring) THEN 1
  WHEN 'Two'   IN (leftstring, rightstring)  THEN 2
  WHEN 'Three' IN (leftstring, rightstring)  THEN 3
  WHEN 'Four'  IN (leftstring, rightstring)  THEN 4
  WHEN 'Five'  IN (leftstring, rightstring)  THEN 5
END;

It produces the following result set:

+-----------------+------------+-----------------+----------+-------------+
| primary_left_id | leftstring | foreign_left_id | right_id | rightstring |
+-----------------+------------+-----------------+----------+-------------+
|               1 | One        |               1 |        1 | One         |
|               2 | Two        |            NULL |     NULL | NULL        |
|               3 | Three      |               3 |        2 | Three       |
|            NULL | NULL       |               4 |        3 | Four        |
|               5 | Five       |               5 |        4 | Five        |
+-----------------+------------+-----------------+----------+-------------+
5 rows in set (0.00 sec)

Add the following lines 18 and 19 to the query and you get only those rows that are childless parent rows or orphaned child rows. More or less, the use case for this type of query is to find both order headers without order lines and order lines abandoned by deleted order headers.

17
18
19

SELECT * FROM cte
WHERE  primary_left_id IS NULL
OR     foreign_left_id IS NULL

It produces the following result set:

+-----------------+------------+-----------------+----------+-------------+
| primary_left_id | leftstring | foreign_left_id | right_id | rightstring |
+-----------------+------------+-----------------+----------+-------------+
|               2 | Two        |            NULL |     NULL | NULL        |
|            NULL | NULL       |               4 |        3 | Four        |
+-----------------+------------+-----------------+----------+-------------+
2 rows in set (0.00 sec)

The ORDER BY clause used is a variation on the more common choice of:

  WHEN leftstring = 'One'   OR rightstring = 'One'   THEN 1

The position of two string literals in any statement is a bad idea. Inverting the literal on the right and using a IN operator gives you a better and clearer WHEN statement:

  WHEN 'One'   IN (leftstring, rightstring) THEN 1

As always, I hope this helps those looking for syntax examples.

Written by maclochlainn

June 15th, 2021 at 10:36 pm

Posted in Database Design,MySQL,MySQL 8,sql

Tagged with ,

MySQL INSERT-SET

without comments

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.

Written by maclochlainn

May 24th, 2021 at 3:28 pm

Posted in MySQL,MySQL 8,MySQL Client,MySQL Connect,sql

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 Transaction Unit

without comments

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   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 ;
  3. 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');
  4. 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.

Written by maclochlainn

May 15th, 2021 at 2:18 pm

Posted in MySQL,MySQL 8,MySQL Client

Tagged with ,

« Older Entries
Newer Entries »