Waking up the Network
Interesting problems seem to plague me from time to time. The current problem isn’t yet solved but I’m working on it. After a sleep cycle, IPV6 networking isn’t starting.
Try:
- Checking the network cables, modem, and router
- Reconnecting to Wi-Fi
ERR_INTERNET_DISCONNECTED |
In the broken Fedora 30 VM, I checked the status with the nmcli tool:
sudo nmcli general status |
It returned:
STATE CONNECTIVITY WIFI-HW WIFI WWAN-HW WWAN asleep none enabled enabled enabled enabled |
The STATE should return connected and connectivity return full. Unfortunately, that’s not the case.
There was little surprise that the next check:
sudo nmcli device |
Returned the following:
DEVICE TYPE STATE CONNECTION virbr0 bridge unmanaged -- ens33 ethernet unmanaged -- lo loopback unmanaged -- virbr0-nic tun unmanaged -- |
In a working instance, it should return:
DEVICE TYPE STATE CONNECTION ens33 ethernet connected ens33 virbr0 bridge connected virbr0 lo loopback unmanaged -- virbr0-nic tun unmanaged -- |
I’m currently troubleshooting what failed by leveraging an article on How to Configure Network Connection Using ‘nmcli’ Tool and the Gnome nmcli documentation. Naturally, when I get it fixed, I’ll finish this article.
MySQL with CTEs
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:
- 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.
- 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.
- Uses a table to hold the variables necessary to evaluate and add the element to the query result set.
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.
MySQL and JavaScript
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.
Oracle’s Sparse Lists
Oracle’s PL/SQL Programming Language is really quite nice. I’ve written 8 books on it and still have fun coding in it. One nasty little detail about Oracle’s lists, introduced in Oracle 8 as PL/SQL Tables according their documentation, is they rely on sequential numeric indexes. Unfortunately, Oracle lists support a DELETE method, which can create gaps in the sequential indexes.
Oracle calls a sequence without gaps densely populated and a sequence with gaps sparsely populated. This can cause problems when PL/SQL code inadvertently removes elements at the beginning, end, or somewhere in the middle of the list. That’s because a program can then pass the sparsely populated list as a parameter to another stored function or procedure where the developer may traverse the list in a for-loop. That traversal may raise an exception in a for-loop, like this when it has gaps in the index sequence:
DECLARE * ERROR AT line 1: ORA-01403: no data found ORA-06512: AT line 20 |
Oracle’s myriad built-in libraries don’t offer a function to compact a sparsely populated list into a densely populated list. This post provides a compact stored procedure that converts a sparsely populated list to a densely populated list.
The first step to using the compact stored procedure requires that you create an object type in SQL, like this list of 20-character strings:
DROP TYPE list; CREATE OR REPLACE TYPE list IS TABLE OF VARCHAR2(20); / |
Now, you can implement the compact stored procedure by passing the User-Defined Type as it’s sole parameter.
CREATE OR REPLACE PROCEDURE compact ( sparse IN OUT LIST ) IS /* Declare local variables. */ iterator NUMBER; -- Leave iterator as null. /* Declare new list. */ dense LIST := list(); BEGIN /* Initialize the iterator with the starting value, which is necessary because the first element of the original list could have been deleted in earlier operations. Setting the initial iterator value to the first numeric index value ensures you start at the lowest available index value. */ iterator := sparse.FIRST; /* Convert sparsely populated list to densely populated. */ WHILE (iterator <= sparse.LAST) LOOP dense.EXTEND; dense(dense.COUNT) := sparse(iterator); iterator := sparse.NEXT(iterator); END LOOP; /* Replace the input parameter with the compacted list. */ sparse := dense; END; / |
Before we test the compact stored procedure, let’s create deleteElement stored procedure for our testing:
CREATE OR REPLACE PROCEDURE deleteElement ( sparse IN OUT LIST , element IN NUMBER ) IS BEGIN /* Delete a value. */ sparse.DELETE(element); END; / |
Now, let’s use an anonymous block to test compacting a sparsely populated list into a densely populated list. The test program will remove the first, last, and one element in the middle before printing the sparsely populated list’s index and string values. This test will show you gaps in the remaining non-sequential index values.
After you see the gaps, the test program compacts the remaining list values into a new densely populated list. It then prints the new index values with the data values.
DECLARE /* Declare a four item list. */ lv_strings LIST := list('one','two','three','four','five','six','seven'); BEGIN /* Check size of list. */ dbms_output.put_line('Print initial list size: ['||lv_strings.COUNT||']'); dbms_output.put_line('==================================='); /* Delete a value. */ deleteElement(lv_strings,lv_strings.FIRST); deleteElement(lv_strings,3); deleteElement(lv_strings,lv_strings.LAST); /* Check size of list. */ dbms_output.put_line('Print modified list size: ['||lv_strings.COUNT||']'); dbms_output.put_line('Print max index and size: ['||lv_strings.LAST||']['||lv_strings.COUNT||']'); dbms_output.put_line('==================================='); FOR i IN 1..lv_strings.LAST LOOP IF lv_strings.EXISTS(i) THEN dbms_output.put_line('List list index and item: ['||i||']['||lv_strings(i)||']'); END IF; END LOOP; /* Call a procedure by passing current sparse collection and the procedure returns dense collection. */ dbms_output.put_line('==================================='); dbms_output.put_line('Compacting list.'); compact(lv_strings); dbms_output.put_line('==================================='); /* Print the new maximum index value and list size. */ dbms_output.put_line('Print new index and size: ['||lv_strings.LAST||']['||lv_strings.COUNT||']'); dbms_output.put_line('==================================='); FOR i IN 1..lv_strings.COUNT LOOP dbms_output.put_line('List list index and item: ['||i||']['||lv_strings(i)||']'); END LOOP; dbms_output.put_line('==================================='); END; / |
It produces output, like:
Print initial list size: [7] =================================== Print modified list size: [4] Print max index and size: [6][4] =================================== List list index and item: [2][two] List list index and item: [4][four] List list index and item: [5][five] List list index and item: [6][six] =================================== Compacting list. =================================== Print new index and size: [4][4] =================================== List list index and item: [1][two] List list index and item: [2][four] List list index and item: [3][five] List list index and item: [4][six] =================================== |
You can extend this concept by creating User-Defined Types with multiple attributes, which are essentially lists of tuples (to draw on Pythonic lingo).
MySQL SQL Filters
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
WHEREclause 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.
Fun JavaScript Q?
I got sent a fun JavaScript question from a student. It was fun for two reasons. They were clearly engaged and trying to figure stuff out on their own and they were just beyond basic syntax issues.
The student wanted to know why this code worked:
// Populate an object with one property. var notempty = {test:"me"} // Test for and enumerate object properties. for (var property in notempty) { if (!notempty.hasOwnProperty(property)) { print("No properties") } else { print("[" + property + "]") } } print("done") |
and returned:
[test] done |
but the behavior changed when the initialized object didn’t have any properties. It failed to print the No properties message:
// Populate an object with one property. var empty = {} // Test for and enumerate object properties. for (var property in empty) { if (!empty.hasOwnProperty(property)) { print("No properties") } else { print("[" + property + "]") } } print("done") |
it printed:
done |
The answer was easy, there were no properties to enumerate, which meant the loop never executes. However, that’s only part of the answer. The complete answer is that the empty object and the default Object.prototype have no properties to enumerate. You should perform a different test before the loop, like:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | // Populate an object without properties. var empty = {} // Test for and enumerate object properties. if (Object.keys(empty).length !== 0) { for (var property in empty) { if (empty.hasOwnProperty(property)) { print("[" + property + "]") } } } else { print("No properties") } // Print complete message. print("done") |
If you run this from the mongo shell, like this
mongo --nodb --norc < object_properties.js |
It prints:
MongoDB shell version v4.0.19 No properties done bye |
If you run this inside the mongo shell with the load function, like
load("object_properties.js") |
It prints:
No properties done true |
However, you can generate a parsing error when you rewrite line 11 in the if-else statement, like so
if (Object.keys(empty).length !== 0) { ... } else { ... } |
The reason for the error is that JavaScript appends a semicolon (;) at the end of each line. Therefore, the if-else-statement appears as an if-only-statement. which raises the syntax error.
It prints:
MongoDB shell version v4.0.19 2021-08-02T13:38:34.672-0600 E QUERY [js] SyntaxError: expected expression, got keyword 'else' @(shell):1:0 done bye |
It appears the parsing error is a warning message because the code completes. Personally, I would prefer that they not raise a warning message because it actually implies a style preference.
There’s a next level problem you may run into while trying to implement reflection, or the discovery of existing properties and their value types, of JavaScript objects. Here’s that code, which seems to be missing from many tutorials and articles:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | // Create an object with functions. // Populate an object without properties. var notEmpty = { first_name: "Clark" , last_name: "Kent" , getName: function() { return this.first_name + " " + this.last_name } } // Test for and enumerate object properties. if (Object.keys(notEmpty).length !== 0) { for (var property in notEmpty) { if (notEmpty.hasOwnProperty(property)) { print("[" + property + "] value is a [" + typeof(notEmpty[property]) + "]") } } } else { print("No properties") } print("done") |
This code on line 12 lets you view a property’s value type:
1 | typeof(notEmpty[property]) |
Only the object[property] subscript notation method works when the property’s value is a variable. The other dot notation syntax to access a property’s value in JavaScript, object.property, doesn’t work without a literal value. Actually, it would work if you embed it inside an eval() function call, but the eval() function is bad juju (black magic) in JavaScript coding.
It prints the following:
MongoDB shell version v4.0.19 [first_name] value is a [string] [last_name] value is a [string] [getName] value is a [function] done bye |
While this shows the basic functionality, it would be more effective to create a function and extend it while adding some output formatting. The next example wouldn’t be found in a JSON (JavaScript Object Notation) collection element but would be found in a NodeJS application managing JSON structures. You would define the constructor to match the data properties of the JSON structure.
The nice thing about a JavaScript function is that “It can specify a list of parameters that will act as variables initialized by the invocation arguments. The body of the function includes variable definitions and statement. (JavaScript: The Good Parts, 2008)” Newer approaches allow you to pass an object as a parameter with named notation inside the object.
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 | // Create an object with functions. function Person(first_name, last_name) { this.first_name = first_name , this.last_name = last_name , this.setFirstName = function(first_name) { this.first_name = first_name } , this.getName = function() { return this.first_name + " " + this.last_name } } // Create an instance of a variable and print results. var myPerson = new Person("Clark","Kent") print("Initial Name: " + myPerson.getName()) // Call the added function and print the new value. myPerson.setFirstName("Lois") print("Reset Name: " + myPerson.getName()) print() // Test for and enumerate object properties. print("Variable Property Names & Values") print("====================================") if (Object.keys(myPerson).length !== 0) { for (var property in myPerson) { if (myPerson.hasOwnProperty(property)) { print("[" + property + "] value is a [" + typeof(myPerson[property]) + "]") } } } else { print("No properties") } print("====================================") print("done") |
It prints the following:
MongoDB shell version v4.0.19 Initial Name: Clark Kent Reset Name: Lois Kent Variable Property Names & Values ==================================== [first_name] value is a [string] [last_name] value is a [string] [setFirstName] value is a [function] [getName] value is a [function] ==================================== done bye |
While the prior example shows object construction, this one introduces one variable inheriting the Person function’s properties and another inheriting the same properties before adding a new one to only the variable. The inherited properties, which are functions, are methods of the variable by delegation.
Below is the modified code. It changes the variable from myPerson to myPerson1 and lines 22 through 28 add the new myPerson2 variable and setLastName function. Lines 47 to 58 enumerate across the new second variable, myPerson2 to show you that it has a new setLastName function.
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 | // Create an object with functions. function Person(first_name, last_name) { this.first_name = first_name , this.last_name = last_name , this.setFirstName = function(first_name) { this.first_name = first_name } , this.getName = function() { return this.first_name + " " + this.last_name } } // Create an instance of a variable and print results. var myPerson1 = new Person("Clark","Kent") print("Initial Name: " + myPerson1.getName()) // Call the added function and print the new value. myPerson1.setFirstName("Lois") print("Reset Name: " + myPerson1.getName()) print() // Create an instance of a variable and print results. var myPerson2 = new Person("Bruce","Wayne") print("Initial Name: " + myPerson2.getName()) // Add a function to the local variable. function setLastName(last_name) { this.last_name = last_name } myPerson2.setLastName = setLastName print() // Test for and enumerate object properties. print("Variable Property Names & Values") print("====================================") if (Object.keys(myPerson1).length !== 0) { for (var property in myPerson1) { if (myPerson1.hasOwnProperty(property)) { print("[" + property + "] value is a [" + typeof(myPerson1[property]) + "]") } } } else { print("No properties") } print("====================================") print() // Test for and enumerate object properties. print("Variable Property Names & Values") print("====================================") if (Object.keys(myPerson2).length !== 0) { for (var property in myPerson2) { if (myPerson2.hasOwnProperty(property)) { print("[" + property + "] value is a [" + typeof(myPerson2[property]) + "]") } } } else { print("No properties") } print("====================================") print("done") |
The program shows you the following output:
MongoDB shell version v4.0.19 Initial Name: Clark Kent Reset Name: Lois Kent Initial Name: Bruce Wayne Variable Property Names & Values ==================================== [first_name] value is a [string] [last_name] value is a [string] [setFirstName] value is a [function] [getName] value is a [function] ==================================== Variable Property Names & Values ==================================== [first_name] value is a [string] [last_name] value is a [string] [setFirstName] value is a [function] [getName] value is a [function] [setLastName] value is a [function] ==================================== done bye |
As always, I hope this helps those working through a similar issue.
Design Database Triggers
Designing and implementing database triggers is always interesting and sometimes not easy. I believe most of the difficulty comes from not implementing the triggers in a way that lets you perform single use case testing. For example, a trigger typically fires as a result of an INSERT, UPDATE, or DELETE statement. That means you can’t test the trigger’s logic independently from the SQL statement.
This post shows you how to implement an Oracle Database trigger that ensures a last_name field always has a hyphen when it is composed of two surnames. It also shows you how to build debugging directly into the trigger with Oracle’s conditional compilation logic (covered in my Oracle Database 12c PL/SQL Programming book on pages 170-171) while writing the debug comments to a debug logging table.
The example works through the design in stages. To begin the process, you need to define a zeta table and zeta_s sequence (no magic in the table or sequence names).
-- Create the zeta demo table. CREATE TABLE zeta ( zeta_id NUMBER , last_name VARCHAR2(30)); -- Create the zeta_s demo sequence. CREATE SEQUENCE zeta_s; |
Next, you write a basic on insert row-level (or, row-by-row) trigger. The following white_space trigger only fires when the last_name column value contains a whitespace between two components of a last name.
The code follows below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | /* || Create an on insert trigger to implement the desired || logic, which replaces a whitespace between two portions || of a last_name column. */ CREATE OR REPLACE TRIGGER white_space BEFORE INSERT ON zeta FOR EACH ROW WHEN (REGEXP_LIKE(NEW.last_name,' ')) BEGIN :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1); END white_space; / |
You can now test the white_space trigger with these two INSERT statements:
-- Two test insert statements. INSERT INTO zeta ( zeta_id, last_name ) VALUES ( zeta_s.NEXTVAL, 'Baron-Schwartz' ); INSERT INTO zeta ( zeta_id, last_name ) VALUES ( zeta_s.NEXTVAL, 'Zeta Jones' ); |
After running the two INSERT statements, you can query the last_name from the zeta table and verify that there’s always a hyphen between the two components of the last name, like:
SELECT * FROM zeta; |
It should display:
ZETA_ID LAST_NAME
---------- ------------------------------
1 Baron-Schwartz
2 Zeta-Jones |
However, the business logic is violated when you run an UPDATE statement, like:
-- Update data and break the business rule. UPDATE zeta SET last_name = 'Zeta Jones' WHERE last_name = 'Zeta-Jones'; |
A fresh query like
SELECT * FROM zeta; |
Should display the following, which allowed an UPDATE statement to put in a non-conforming last name value:
ZETA_ID LAST_NAME
---------- ------------------------------
1 Baron-Schwartz
2 Zeta Jones |
You need to expand the role of your white_space trigger to prevent this undesired outcome by enabling it to fire on an insert or update event. You do that by adding ON UPDATE to line 8 below. The modified white_space trigger for both SQL events is:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | /* || Create an on insert or update trigger to implement the || desired logic, which replaces a whitespace between two || portions of a last_name column. */ CREATE OR REPLACE TRIGGER white_space BEFORE INSERT OR UPDATE ON zeta FOR EACH ROW WHEN (REGEXP_LIKE(NEW.last_name,' ')) BEGIN :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1); END white_space; / |
Having made the change on line 8 above, you can now retest the white_space trigger with the following UPDATE statement. You should note that the WHERE clause uses a whitespace because the last UPDATE statement with the INSERT-only white_space trigger allowed its change.
UPDATE zeta SET last_name = 'Zeta-Jones' WHERE last_name = 'Zeta Jones'; |
Re-query the zeta table:
SELECT * FROM zeta; |
It should display the following values that meet the business rule:
ZETA_ID LAST_NAME
---------- ------------------------------
1 Baron-Schwartz
2 Zeta Jones |
The modified white_space trigger doesn’t let us capture debug information and it doesn’t let us see whether the SQL event is an INSERT or UPDATE statement. It also fails to differentiate between outcomes from an INSERT and UPDATE event.
You can fix this by:
- Creating a debug_log table that captures debugging information.
- Creating a debug_procedure to format diagnostic strings.
- Using the Data Manipulation Language (DML) Event Functions (covered in my Oracle Database 12c PL/SQL Programming book’s Table 12-3 on page 533) to track whether the event is an
INSERTorUPDATEstatement.
The three steps to make the trigger capable of different outcomes and debugging are:
- The following creates a debug_log table:
-- Create the debug_log table. CREATE TABLE debug_log ( message VARCHAR2(78));
- The following creates an a debug procedure:
-- Create a debug logging procedure. CREATE OR REPLACE PROCEDURE debug ( event VARCHAR2 := 'Unknown' , location VARCHAR2 , COLUMN VARCHAR2 ) IS /* Local message variable. */ lv_message VARCHAR2(78); /* Set procedure as an autonomous transaction. */ PRAGMA AUTONOMOUS_TRANSACTION; BEGIN /* Build, insert, and commit message in log. */ lv_message := event || ' event at ' || location || ' on column [' || COLUMN || ']'; INSERT INTO debug_log ( message ) VALUES ( lv_message ); COMMIT; END; /
- The following creates an a replacement white_space trigger equipped with event tracking and conditional compilation debug calls to the debug_log table:
You actually need to change the session before compiling this trigger with the following command so that the conditional compilation instructions work:
ALTER SESSION SET PLSQL_CCFLAGS = 'DEBUG:1';
Then, create the white_space trigger from the following code:
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
-- Create a debug logging procedure. CREATE OR REPLACE TRIGGER white_space BEFORE INSERT OR UPDATE ON zeta FOR EACH ROW WHEN (REGEXP_LIKE(NEW.last_name,' ')) DECLARE lv_event VARCHAR2(9); BEGIN /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( location => 'before IF statement' , column_value => ':new.last_name' ); $END IF INSERTING THEN lv_event := 'Inserting'; /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( event => lv_event , location => 'after IF statement' , column_value => ':new.last_name' ); $END :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1); ELSIF UPDATING THEN lv_event := 'Updating'; /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( event => lv_event , location => 'after ELSIF statement' , column_value => ':new.last_name' ); $END RAISE_APPLICATION_ERROR(-20001,'Whitespace replaced with hyphen.'); END IF; /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( location => 'after END IF statement' , column_value => ':new.last_name' ); $END END white_space; /
A new test case for the modified white_space trigger uses an INSERT and UPDATE statement, like:
INSERT INTO zeta ( zeta_id, last_name ) VALUES ( zeta_s.NEXTVAL, 'Pinkett Smith' ); UPDATE zeta SET last_name = 'Pinkett Smith' WHERE last_name = 'Pinkett-Smith'; |
The UPDATE statement violates the business rule and the new white_space trigger throws an error when an attempt is made to update the last_name with two names separated by a whitespace. The UPDATE statement raises the following error stack:
UPDATE zeta * ERROR AT line 1: ORA-20001: Whitespace replaced WITH hyphen. ORA-06512: AT "STUDENT.WHITE_SPACE", line 31 ORA-04088: error during execution OF TRIGGER 'STUDENT.WHITE_SPACE' |
Re-query the zeta table:
SELECT * FROM zeta; |
It should display the following values that meet the business rule. The new third row in the table came from the INSERT statement in the test case.
ZETA_ID LAST_NAME
---------- ------------------------------
1 Baron-Schwartz
2 Zeta-Jones
3 Pinkett-Smith |
Unfortunately, there’s a lot of debugging clutter in the white_space trigger. The other downside is it requires testing from INSERT and UPDATE statements rather than simple anonymous block. You can fix that by doing two things:
- Remove the body of the trigger to an autonomous zeta_function.
- Put a logic router in the trigger with a call to the autonomous zeta_function.
Here’s the script to create the zeta_function:
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 | CREATE OR REPLACE FUNCTION zeta_function ( column_value VARCHAR2 , event VARCHAR2 ) RETURN VARCHAR2 IS /* Return value. */ lv_retval VARCHAR2(30) := column_value; /* Set function as an autonomous transaction. */ PRAGMA AUTONOMOUS_TRANSACTION; BEGIN /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( location => 'before IF statement' , column_value => ':new.column_value' ); $END /* Check if event is INSERT statement. */ IF event = 'INSERTING' THEN /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( event => INITCAP(event) , location => 'after IF statement' , column_value => ':new.column_value' ); $END /* Replace a whitespace with a hyphen. */ lv_retval := REGEXP_REPLACE(column_value,' ','-',1,1); /* Check if event is UPDATE statement. */ ELSIF event = 'UPDATING' THEN /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( event => INITCAP(event) , location => 'after ELSIF statement' , column_value => ':new.column_value' ); $END /* Raise error to state policy allows no changes. */ RAISE_APPLICATION_ERROR(-20001,'Whitespace replaced with hyphen.'); END IF; /* Conditional debugging. */ $IF $$DEBUG = 1 $THEN debug( location => 'after END IF statement' , column_value => ':new.column_value' ); $END /* Return modified column for insert or original column for update. */ RETURN lv_retval; END zeta_function; / |
The refactored white_space trigger follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | CREATE OR REPLACE TRIGGER white_space BEFORE INSERT OR UPDATE ON zeta FOR EACH ROW WHEN (REGEXP_LIKE(NEW.last_name,' ')) DECLARE lv_event VARCHAR2(9); BEGIN /* Set evaluation event. */ IF INSERTING THEN lv_event := 'INSERTING'; ELSIF UPDATING THEN lv_event := 'UPDATING'; END IF; /* || Assign the result of the formatted string to the || new last_name value. */ :NEW.last_name := zeta_function( event => lv_event , column_value => :NEW.last_name); END white_space; / |
A new test case for the modified white_space trigger uses an INSERT and UPDATE statement with some new values.
INSERT INTO zeta ( zeta_id, last_name ) VALUES ( zeta_s.NEXTVAL, 'Day Lewis' ); UPDATE zeta SET last_name = 'Day Lewis' WHERE last_name = 'Day-Lewis'; |
The UPDATE statement continues to violate the business rule and the modified white_space trigger throws a different error stack. The new error stack includes the zeta_function because that’s where you throw the error. It is caught and re-thrown by the white_space trigger.
UPDATE zeta * ERROR AT line 1: ORA-20001: Whitespace replaced WITH hyphen. ORA-06512: AT "STUDENT.ZETA_FUNCTION", line 47 ORA-06512: AT "STUDENT.WHITE_SPACE", line 13 ORA-04088: error during execution OF TRIGGER 'STUDENT.WHITE_SPACE' |
Re-query the zeta table:
SELECT * FROM zeta; |
It should display the following values that meet the business rule. The new third row in the table came from the INSERT statement in the test case.
ZETA_ID LAST_NAME
---------- ------------------------------
1 Baron-Schwartz
2 Zeta-Jones
3 Pinkett-Smith
4 Day-Lewis |
Now, you can query the debug_log table and see the debug messages that you captured from testing the INSERT and UPDATE statements. You get three messages from the INSERT statement test and only two from the UPDATE statement test.
MESSAGE ------------------------------------------------------------------ Unknown event at before IF statement on column [:new.last_name] Inserting event at after IF statement on column [:new.last_name] Unknown event at after END IF statement on column [:new.last_name] Unknown event at before IF statement on column [:new.last_name] Updating event at after ELSIF statement on column [:new.last_name] |
As always, I hope this helps people see new ways to solve problems.
Title Case Anyone?
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.
MySQL PHP Transaction
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.
SQL Handling Nulls
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.
Subscribe via RSS