Sql | MacLochlainns Weblog

Sometimes I give parts of a solution to increase the play time to solve a problem. I didn’t anticipate a problem when showing how to perform a sort operation with a CASE statement. It’s a sweet solution when you need to sort something differently than a traditional ascending or descending sort.

I gave my students this ORDER BY clause as an example:

  CASE
    WHEN filter = 'Debit' THEN 1
    WHEN filter = 'Credit' THEN 2
    WHEN filter = 'Total' THEN 3
  END;

It raises the following error in MySQL for students:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY
  CASE
    WHEN filter = 'Debit' THEN 1
    WHEN filter = 'Credit' THEN' at line 6

It raises the following error in Oracle for some students:

  CASE
  *
ERROR AT line 7:
ORA-01785: ORDER BY item must be the NUMBER OF a SELECT-list expression

So, I built a little test case to replicate the problem and error message they encountered:

SQL> SELECT 'Debit' AS filter FROM dual
  2  UNION ALL
  3  SELECT 'Credit' AS filter FROM dual
  4  UNION ALL
  5  SELECT 'Total' AS filter FROM dual
  6  ORDER BY
  7    CASE
  8      WHEN filter = 'Debit' THEN 1
  9      WHEN filter = 'Credit' THEN 2
 10      WHEN filter = 'Total' THEN 3
 11    END;

They said, great but how can you fix it? That’s simple, with a Common Table Expression (CTE) in Oracle or with an inline view in MySQL. The Oracle CTE solution is:

  1  WITH results AS
  2  (SELECT 'Debit' AS filter FROM dual
  3   UNION ALL
  4   SELECT 'Credit' AS filter FROM dual
  5   UNION ALL
  6   SELECT 'Total' AS filter FROM dual)
  7  SELECT filter
  8  FROM   results
  9  ORDER BY
 10    CASE
 11	 WHEN filter = 'Debit'  THEN 1
 12	 WHEN filter = 'Credit' THEN 2
 13	 WHEN filter = 'Total'  THEN 3
 14    END;

There are two MySQL solutions. One simply removes the FROM dual clauses from the query components and the other uses an inline view in the FROM clause. This is the inline view:

SELECT filter
FROM  (SELECT 'Debit' AS filter FROM dual
       UNION ALL
       SELECT 'Credit' AS filter FROM dual
       UNION ALL
       SELECT 'Total' AS filter FROM dual) resultset
ORDER BY
  CASE
    WHEN filter = 'Debit' THEN 1
    WHEN filter = 'Credit' THEN 2
    WHEN filter = 'Total' THEN 3
  END;

This is the solution without the FROM dual clauses:

SELECT 'Debit' AS filter
UNION ALL
SELECT 'Credit' AS filter
UNION ALL
SELECT 'Total' AS filter
ORDER BY
  CASE
    WHEN filter = 'Debit' THEN 1
    WHEN filter = 'Credit' THEN 2
    WHEN filter = 'Total' THEN 3
  END;

Both MySQL solutions yield the following:

+--------+
| filter |
+--------+
| Debit  |
| Credit |
| Total  |
+--------+
3 rows in set (0.00 sec)

It puts the fabricating query inside a result set, and then lets you use the column alias to filter the set. If you have a better approach, please share it.

This how you install SQL Developer on Mac OS Yosemite. The first thing you need to do is download and install Java 8, not Java 7 on your Mac OS Yosemite as suggested on some web sites. You can determine whether or not Java is installed by running the following command:

Mac-Pro-3:~ username$ java -version
No Java runtime present, requesting install.

You must accept the Java license to install Java 8 on the Mac OS X operating system:

You have the option of installing the Java SDK or JDK. I’ve opted to install Netbeans 8 with JDK 8u45, as you can tell from the screen capture after you launched the file:

It is a standard Mac OS installation, which is why I didn’t bother showing any dialog messages. After installing the Java JDK or SDK, you should download SQL Developer 4.1 from Oracle’s web site. Below is a screen shot of the Oracle download web page where I’ve accepted the license agreement:

If you attempt to launch the installation and you’ve set your Mac Security to the “Mac App Store and identified developers” setting, you should raise the following exception:

If you reset the Mac Security to an “Anywhere” setting, you can install Oracle SQL Developer on Yosemite. Just make sure you reset it to the “Mac App Store and identified developers” setting after you install SQL Developer.

If you launch SQL Developer with the Security “Anywhere” setting, it displays the following dialog:

After you launch the program, you will see the following progress dialog:

The last step of the installation launches SQL Developer, as shown below:

Click the Connections icon to create an initial connection, like the following:

After connecting to the database, you can write and execute a query as shown in the next screen capture:

As always, I hope that this helps those who require an example to install SQL Server on a Mac OS.

Last week, I wrote about how to use bash arrays and the MySQL database to create unit and integration test scripts. While the MySQL example was nice for some users, there were some others who wanted me to show how to write bash shell scripts for Oracle unit and integration testing. That’s what this blog post does.

If you don’t know much about bash shell, you should start with the prior post to learn about bash arrays, if-statements, and for-loops. In this blog post I only cover how to implement a bash shell script that runs SQL scripts in silent mode and then queries the database in silent mode and writes the output to an external file.

I’ve copied the basic ERD for the example because of a request from a reader. In their opinion it makes cross referencing the two posts unnecessary.

To run the bash shell script, you’ll need the following SQL files, which you can see by clicking not he title below. There are several differences. For example, Oracle doesn’t support a DROP IF EXISTS syntax and requires you to write anonymous blocks in their PL/SQL language; and you must explicitly issue a QUIT; statement even when running in silent mode unlike MySQL, which implicitly issues an exit.

Setup SQL Files ↓

The actor.sql file:

-- Drop actor table and actor_s sequence.
BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_name IN ('ACTOR','ACTOR_S')) LOOP
    IF    i.object_type = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP TABLE ' || i.object_name || ' CASCADE CONSTRAINTS';
    ELSIF i.object_type = 'SEQUENCE' THEN
      EXECUTE IMMEDIATE 'DROP SEQUENCE ' || i.object_name;
    END IF;
  END LOOP;
END;
/
 
-- Create an actor table.
CREATE TABLE actor
( actor_id    NUMBER CONSTRAINT actor_pk PRIMARY KEY
, actor_name  VARCHAR(30)  NOT NULL );
 
-- Create an actor_s sequence.
CREATE SEQUENCE actor_s;
 
-- Insert two rows.
INSERT INTO actor VALUES (actor_s.NEXTVAL,'Chris Hemsworth');
INSERT INTO actor VALUES (actor_s.NEXTVAL,'Chris Pine');
INSERT INTO actor VALUES (actor_s.NEXTVAL,'Chris Pratt');
 
-- Quit session.
QUIT;

The film.sql file:

-- Drop film table and film_s sequence.
BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_name IN ('FILM','FILM_S')) LOOP
    IF    i.object_type = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP TABLE ' || i.object_name || ' CASCADE CONSTRAINTS';
    ELSIF i.object_type = 'SEQUENCE' THEN
      EXECUTE IMMEDIATE 'DROP SEQUENCE ' || i.object_name;
    END IF;
  END LOOP;
END;
/
 
-- Create a film table.
CREATE TABLE film
( film_id    NUMBER CONSTRAINT film_pk PRIMARY KEY
, film_name  VARCHAR(30)  NOT NULL );
 
-- Create an actor_s sequence.
CREATE SEQUENCE film_s;
 
-- Insert four rows.
INSERT INTO film VALUES (film_s.NEXTVAL,'Thor');
INSERT INTO film VALUES (film_s.NEXTVAL,'Thor: The Dark World');
INSERT INTO film VALUES (film_s.NEXTVAL,'Star Trek');
INSERT INTO film VALUES (film_s.NEXTVAL,'Star Trek into Darkness');
INSERT INTO film VALUES (film_s.NEXTVAL,'Guardians of the Galaxy');
 
-- Quit session.
QUIT;

The movie.sql file:

-- Drop movie table and movie_s sequence.
BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_name IN ('MOVIE','MOVIE_S')) LOOP
    IF    i.object_type = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP TABLE ' || i.object_name || ' CASCADE CONSTRAINTS';
    ELSIF i.object_type = 'SEQUENCE' THEN
      EXECUTE IMMEDIATE 'DROP SEQUENCE ' || i.object_name;
    END IF;
  END LOOP;
END;
/
 
-- Create an movie table.
CREATE TABLE movie
( movie_id   NUMBER  CONSTRAINT movie_pk   PRIMARY KEY
, actor_id   NUMBER  CONSTRAINT movie_nn1  NOT NULL
, film_id    NUMBER  CONSTRAINT movie_nn2  NOT NULL
, CONSTRAINT actor_fk FOREIGN KEY (actor_id)
  REFERENCES actor (actor_id)
, CONSTRAINT film_fk  FOREIGN KEY (film_id)
  REFERENCES film(film_id));
 
-- Create table constraint.
CREATE SEQUENCE movie_s;
 
-- Insert translation rows.
INSERT INTO movie
VALUES
( movie_s.NEXTVAL
,(SELECT   actor_id
  FROM     actor
  WHERE    actor_name = 'Chris Hemsworth')
,(SELECT   film_id
  FROM     film
  WHERE    film_name = 'Thor'));
 
INSERT INTO movie
VALUES
( movie_s.NEXTVAL
,(SELECT   actor_id
  FROM     actor
  WHERE    actor_name = 'Chris Hemsworth')
,(SELECT   film_id
  FROM     film
  WHERE    film_name = 'Thor: The Dark World'));
 
INSERT INTO movie
VALUES
( movie_s.NEXTVAL
,(SELECT   actor_id
  FROM     actor
  WHERE    actor_name = 'Chris Pine')
,(SELECT   film_id
  FROM     film
  WHERE    film_name = 'Star Trek'));
 
INSERT INTO movie
VALUES
( movie_s.NEXTVAL
,(SELECT   actor_id
  FROM     actor
  WHERE    actor_name = 'Chris Pine')
,(SELECT   film_id
  FROM     film
  WHERE    film_name = 'Star Trek into Darkness'));
 
INSERT INTO movie
VALUES
( movie_s.NEXTVAL
,(SELECT   actor_id
  FROM     actor
  WHERE    actor_name = 'Chris Pratt')
,(SELECT   film_id
  FROM     film
  WHERE    film_name = 'Guardians of the Galaxy'));
 
-- Quit session.
QUIT;

The tables.sql file, lets you verify the creation of the actor, film, and movie tables:

-- Set Oracle column width.
COL table_name FORMAT A30 HEADING "Table Name"
 
-- Query the tables.
SELECT   table_name
FROM     user_tables;
 
-- Exit SQL*Plus.
QUIT;

The results.sql file, lets you see join results from actor, film, and movie tables:

-- Format query.
COL film_actors FORMAT A40 HEADING "Actors in Films"
 
-- Diagnostic query.
SELECT   a.actor_name || ', ' || f.film_name AS film_actors
FROM     actor a INNER JOIN movie m
ON       a.actor_id = m.actor_id INNER JOIN film f
ON       m.film_id = f.film_id;
 
-- Quit the session.
QUIT;

If you don’t have a sample test schema to use to test this script, you can create a sample schema with the following create_user.sql file. The file depends on the existence of a users and temp tablespace.

Click the link below to see the source code for a script that let’s you create a sample user account as the system user:

Create sample User SQL File ↓

You can use the dbms_metadata.get_ddl function to discover the existence of the tablespaces. The following SQL syntax returns the SQL DDL statement that created a users or temp tablespace:

1 2	SET LONG 200000 SELECT dbms_metadata.get_ddl('TABLESPACE','USERS') FROM dual;

You create the sample database with the following SQL statements:

-- Drop the sample user table.
BEGIN
  FOR i IN (SELECT   username
            FROM     dba_users
            WHERE    username = 'SAMPLE') LOOP
    EXECUTE IMMEDIATE 'DROP USER ' || i.username || ' CASCADE';
  END LOOP;
END;
/
 
-- Create the sample user table.
CREATE USER sample IDENTIFIED BY sample
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA 50M ON users;
 
-- Grant privileges to sample user.
GRANT CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR
,     CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION
,     CREATE TABLE, CREATE TRIGGER, CREATE TYPE
,     CREATE VIEW TO sample;

The following list_oracle.sh shell script expects to receive the username, password, and fully qualified path in that specific order. The script names are entered manually in the array because this should be a unit test script.

This is an insecure version of the list_oracle.sh script because you provide the password on the command line. It’s better to provide the password as you run the script.

#!/usr/bin/bash
 
# Assign user and password
username="${1}"
password="${2}"
directory="${3}"
 
echo "User name:" ${username}
echo "Password: " ${password}
echo "Directory:" ${directory}
 
# Define an array.
declare -a cmd
 
# Assign elements to an array.
cmd[0]="actor.sql"
cmd[1]="film.sql"
cmd[2]="movie.sql"
 
# Call the array elements.
for i in ${cmd[*]}; do
  sqlplus -s ${username}/${password} @${directory}/${i} > /dev/null
done
 
# Connect and pipe the query result minus errors and warnings to the while loop.
sqlplus -s ${username}/${password} @${directory}/tables.sql 2>/dev/null |
 
# Read through the piped result until it's empty.
while IFS='\n' read actor_name; do
  echo $actor_name
done
 
# Connect and pipe the query result minus errors and warnings to the while loop.
sqlplus -s ${username}/${password} @${directory}/result.sql 2>/dev/null |
 
# Read through the piped result until it's empty.
while IFS='\n' read actor_name; do
  echo $actor_name
done

The IFS (Internal Field Separator) works with whitespace by default. The IFS on lines 29 and 37 sets the IFS to a line return ('\n'). That’s the trick to display the data, and you can read more about the IFS in this question and answer post.

You can run the shell script with the following syntax:

./list_oracle.sh sample sample /home/student/Code/bash/oracle > output.txt

You can then display the results from the output.txt file with the following command:

cat output.txt command:

It will display the following output:

User name: sample
Password:  sample
Directory: /home/student/Code/bash/oracle
 
Table Name
------------------------------
MOVIE
FILM
ACTOR
 
Actors in Films
----------------------------------------
Chris Hemsworth, Thor
Chris Hemsworth, Thor: The Dark World
Chris Pine, Star Trek
Chris Pine, Star Trek into Darkness
Chris Pratt, Guardians of the Galaxy

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

Student questions are always interesting! They get me to think and to write. The question this time is: “How do I write a Bash Shell script to process multiple MySQL script files?” This post builds the following model (courtesy of MySQL Workbench) by using a bash shell script and MySQL script files, but there’s a disclaimer on this post. It shows both insecure and secure approaches and you should avoid the insecure ones.

It seems a quick refresher on how to use arrays in bash shell may be helpful. While it’s essential in a Linux environment, it’s seems not everyone masters the bash shell.

Especially, since I checked my Learning the Bash Shell (2^nd Edition) and found a typo on how you handle arrays in the bash shell, and it’s a mistake that could hang newbies up (on page 161). Perhaps I should update my copy because I bought it in 1998. 😉 It was good then, and the new edition is probably better. The error is probably corrected in the current Learning the Bash Shell, but if not, the following examples show you how to use arrays in loops.

Naturally, these do presume some knowledge of working with bash shell, like the first line always is the same in any bash shell script. That you open an if-statement with an if and close it with a fi, and that you else-if is elif; and that a semicolon between a for-statement and the do statement is required when they’re on the same line because they’re two statements.

If you’re new to bash shell arrays, click on the link below to expand a brief tutorial. It takes you through three progressive examples of working with bash arrays.

Working with bash Arrays ↓

A basic example of working with an array in bash shell is the following list1.sh script:

#!/usr/bin/bash
 
# Print script name.
echo $0
#!/usr/bin/bash
 
# Define an array.
declare -a cmd=("one" "two" "three")
 
# Call the array elements.
for i in ${cmd[*]}; do
  echo ${i}
done

Line 8 declares the cmd array by assigning three strings. Line 12 returns the elements of the array to the ${i} variable, which lets you manage them one at a time. You use the chmod command to make the list1.sh executable, like this:

chmod 755 list1.sh

Then, you can run it like this from the present working directory (pwd):

./list1.sh

It should print:

one
two
three

The list2.sh example changes the cmd array declaration from list1.sh. It declares the cmd array as an empty array, and then it assigns elements by index numbers (using a zero-based index), as shown below:

#!/usr/bin/bash
 
# Define an array.
declare -a cmd
 
# Assign elements to an array.
cmd[0]="one"
cmd[1]="two"
cmd[2]="three"
 
# Call the array elements.
for i in ${cmd[*]}; do
  echo ${i}
done

Lines 7 through 9 assign values to the elements of the cmd array. You would chmod the file, and run the file as qualified above for the list1.sh script.

The last pre-implementation example requires that you create three demonstration scripts, the one.sh, two.sh, and three.sh scripts. You should put them in the same directory as the list3.sh script.

The demonstration scripts should all have the same code, like this:

#!/usr/bin/bash
 
# Print script name.
echo $0

Line 4 returns command line parameter $0 or ${0}, which is always the command line program’s file name. The file name may be provided as a relative or absolute file name, and if that’s new to you please check out The Linux Command Line: A Complete Introduction (also downloadable as a PDF for free).

The list3.sh script should contain the following:

#!/usr/bin/bash
<code>
# Define an array.
declare -a cmd
 
# Assign elements to an array.
cmd[0]="one.sh"
cmd[1]="two.sh"
cmd[2]="three.sh"
 
# Call the array elements.
for i in ${cmd[*]}; do
  `pwd`/${i}
done

When you run the list3.sh script from the /home/student/Code/bash directory with a local syntax, the script should return the fully qualified file names of the subshell programs. The output should look like this:

/home/student/Code/bash/one.sh
/home/student/Code/bash/two.sh
/home/student/Code/bash/three.sh

The list3.sh script provides the present working directory (pwd) and the one.sh, two.sh, and three.sh scripts return only their executable name. For example, if you ran one.sh with the following syntax:

./one.sh

It returns

./one.sh

Only one more trick needs to be qualified before our main MySQL examples. That trick is how you pass parameters to a bash shell script. For reference, this is the part that’s insecure because user command histories are available inside the Linux OS.

Here’s a hello_whom.sh script to demonstrates the concept of parameter passing:

#!/usr/bin/bash
 
# This says hello to the argument while managing no argument.
if [[ ${#} = 1 ]]; then
  echo 'The '${0}' program says: "Hello '${1}'!"'
elif [[ ${#} > 1 ]]; then
  echo 'The '${0}' program wants to know if you have more than one name?'
else
  echo 'The '${0}' program wants to know if you have a name?'
fi

If you need more on how parameters are passed and managed, you can check a prior blob post on Handling bash Parameters, or check the bash help pages. The following leverages bash arrays to run scripts and query the MySQL database from the command line.

You will need the three batch SQL files first, so here they are:

Setup SQL Files ↓

The actor.sql file:

-- Use the sampledb database.
USE sampledb;
 
-- Disable foreign key checking.
SET foreign_key_checks = 0;
 
-- Drop an actor table. 
DROP TABLE IF EXISTS actor;
 
-- Create an actor table.
CREATE TABLE actor
( actor_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, actor_name  VARCHAR(30)  NOT NULL );
 
-- Insert two rows.
INSERT INTO actor (actor_name) VALUES ('Chris Hemsworth');
INSERT INTO actor (actor_name) VALUES ('Chris Pine');
INSERT INTO actor (actor_name) VALUES ('Chris Pratt');

The film.sql file:

-- Use the sampledb database.
USE sampledb;
 
-- Disable foreign key checking.
SET foreign_key_checks = 0;
 
-- Drop a film table. 
DROP TABLE IF EXISTS film;
 
-- Create a film table.
CREATE TABLE film
( film_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, film_name  VARCHAR(30)  NOT NULL );
 
-- Insert rows.
INSERT INTO film (film_name) VALUES ('Thor');
INSERT INTO film (film_name) VALUES ('Thor: The Dark World');
INSERT INTO film (film_name) VALUES ('Star Trek');
INSERT INTO film (film_name) VALUES ('Star Trek into Darkness');
INSERT INTO film (film_name) VALUES ('Guardians of the Galaxy');

The movie.sql file:

-- Use the sampledb database.
USE sampledb;
 
-- Disable foreign key checking.
SET foreign_key_checks = 0;
 
-- Drop an movie table. 
DROP TABLE IF EXISTS movie;
 
-- Create an movie table.
CREATE TABLE movie
( movie_id   int unsigned PRIMARY KEY AUTO_INCREMENT
, actor_id   int unsigned NOT NULL
, film_id    int unsigned NOT NULL
, CONSTRAINT actor_fk FOREIGN KEY (actor_id)
  REFERENCES actor (actor_id)
, CONSTRAINT film_fk  FOREIGN KEY (film_id)
  REFERENCES film(film_id));
 
-- Use scalar subqueries to discover surrogate keys by using the faux natural key.
INSERT INTO movie
( actor_id
, film_id )
VALUES
((SELECT   actor_id
  FROM     actor
  WHERE    actor_name = 'Chris Hemsworth')
,(SELECT   film_id
  FROM     film
  WHERE    film_name = 'Thor'));
 
-- Use scalar subqueries to discover surrogate keys by using the faux natural key.
INSERT INTO movie
( actor_id
, film_id )
VALUES
((SELECT   actor_id
  FROM     actor
  WHERE    actor_name = 'Chris Hemsworth')
,(SELECT   film_id
  FROM     film
  WHERE    film_name = 'Thor: The Dark World'));
 
-- Use scalar subqueries to discover surrogate keys by using the faux natural key.
INSERT INTO movie
( actor_id
, film_id )
VALUES
((SELECT   actor_id
  FROM     actor
  WHERE    actor_name = 'Chris Pine')
,(SELECT   film_id
  FROM     film
  WHERE    film_name = 'Star Trek'));
 
-- Use scalar subqueries to discover surrogate keys by using the faux natural key.
INSERT INTO movie
( actor_id
, film_id )
VALUES
((SELECT   actor_id
  FROM     actor
  WHERE    actor_name = 'Chris Pine')
,(SELECT   film_id
  FROM     film
  WHERE    film_name = 'Star Trek into Darkness'));
 
-- Use scalar subqueries to discover surrogate keys by using the faux natural key.
INSERT INTO movie
( actor_id
, film_id )
VALUES
((SELECT   actor_id
  FROM     actor
  WHERE    actor_name = 'Chris Pratt')
,(SELECT   film_id
  FROM     film
  WHERE    film_name = 'Guardians of the Galaxy'));

The following list_mysql.sh shell script expects to receive the username, password, database and fully qualified path in that specific order. The script names are entered manually because this should be a unit test script. Naturally, you can extend the script to manage those parameters but as mentioned I see this type of solution as a developer machine only script to simplify unit testing. Anything beyond that is risky!

#!/usr/bin/bash
 
# Assign user and password
username="${1}"
password="${2}"
database="${3}"
directory="${4}"
 
# List the parameter values passed.
echo "Username:  " ${username}
echo "Password:  " ${password}
echo "Database:  " ${database}
echo "Directory: " ${directory}
echo ""
 
# Define an array.
declare -a cmd
 
# Assign elements to an array.
cmd[0]="actor.sql"
cmd[1]="film.sql"
cmd[2]="movie.sql"
 
# Call the array elements.
for i in ${cmd[*]}; do
  mysql -s -u${username} -p${password} -D${database} < ${directory}/${i} > /dev/null 2>/dev/null
done
 
# Connect and pipe the query result minus errors and warnings to the while loop.
mysql -u${username} -p${password} -D${database} <<<'show tables' 2>/dev/null |
 
# Read through the piped result until it's empty but format the title.
while IFS='\n' read list; do
  if [[ ${list} = "Tables_in_sampledb" ]]; then
    echo $list
    echo "----------------------------------------"
  else
    echo $list
  fi
done
echo ""
 
# Connect and pipe the query result minus errors and warnings to the while loop.
mysql -u${username} -p${password} -D${database} <<<'SELECT CONCAT(a.actor_name," in ",f.film_name) AS "Actors in Films" FROM actor a INNER JOIN movie m ON a.actor_id = m.actor_id INNER JOIN film f ON m.film_id = f.film_id' 2>/dev/null |
 
# Read through the piped result until it's empty but format the title.
while IFS='\n' read actor_name; do
  if [[ ${actor_name} = "Actors in Films" ]]; then
    echo $actor_name
    echo "----------------------------------------"
  else
    echo $actor_name
  fi
done

The IFS (Internal Field Separator) works with whitespace by default. The IFS on lines 33 and 47 sets the IFS to a line return ('\n'). That’s the trick to display the data, and you can read more about the IFS in this question and answer post.

You can run this script with the following input parameters from the local directory where you deploy it. The a parameters are: (1) username, (2) password, (3) database, and (4) a fully qualified path to the SQL setup files.

./list_mysql.sh student student sampledb "/home/student/Code/bash/mysql"

With valid input values, the list_mysql.sh bash script generates the following output, which confirms inputs and verifies actions taken by the scripts with queries:

Username:   student
Password:   student
Database:   sampledb
Directory:  /home/student/Code/bash/mysql
 
Tables_in_sampledb
----------------------------------------
actor
film
movie
 
Actors in Films
----------------------------------------
Chris Hemsworth in Thor
Chris Hemsworth in Thor: The Dark World
Chris Pine in Star Trek
Chris Pine in Star Trek into Darkness
Chris Pine in Guardians of the Galaxy

If you forgot to provide the required inputs to the list_mysql.sh bash script, it alternatively returns the following output:

Username:  
Password:  
Database:  
Directory: 
 
./list_mysql.sh: line 25: /actor.sql: No such file or directory
./list_mysql.sh: line 25: /film.sql: No such file or directory
./list_mysql.sh: line 25: /movie.sql: No such file or directory

The secure way removes the password at a minimum! The refactored program will require you to manually enter the password for all elements of the array (three in this sample), and twice for the two queries. Here’s the refactored code:

#!/usr/bin/bash
 
# Assign user and password
username="${1}"
database="${2}"
directory="${3}"
 
# List the parameter values passed.
echo "Username:  " ${username}
echo "Database:  " ${database}
echo "Directory: " ${directory}
echo ""
 
# Define an array.
declare -a cmd
 
# Assign elements to an array.
cmd[0]="actor.sql"
cmd[1]="film.sql"
cmd[2]="movie.sql"
 
# Call the array elements.
for i in ${cmd[*]}; do
  mysql -s -u${username} -p -D${database} < ${directory}/${i} > /dev/null 2>/dev/null
done
 
# Connect and pipe the query result minus errors and warnings to the while loop.
mysql -u${username} -p -D${database} <<<'show tables' 2>/dev/null |
 
# Read through the piped result until it's empty.
while IFS='\n' read list; do
  if [[ ${list} = "Tables_in_sampledb" ]]; then
    echo $list
    echo "----------------------------------------"
  else
    echo $list
  fi
done
echo ""
 
# Connect and pipe the query result minus errors and warnings to the while loop.
mysql -u${username} -p -D${database} <<<'SELECT CONCAT(a.actor_name," in ",f.film_name) AS "Actors in Films" FROM actor a INNER JOIN movie m ON a.actor_id = m.actor_id INNER JOIN film f ON m.film_id = f.film_id' 2>/dev/null |
 
# Read through the piped result until it's empty.
while IFS='\n' read actor_name; do
  if [[ ${actor_name} = "Actors in Films" ]]; then
    echo $actor_name
    echo "----------------------------------------"
  else
    echo $actor_name
  fi
done

Please let me know if you think there should be any more scaffolding for newbies in this post. As always, I hope this helps those looking for this type of solution.

A reader posted A dynamic level limiting hierarchical query about Oracle’s hierarchical queries. They wanted to know how to capture only the hierarchy above the level where the first leaf node occurs. They gave me the following hierarchy map as an example:

               1                                    2
        +-------------+                       +-----------+
        |             |                       |           |      
        3             5                       4           6
    +---------+    +-----------+           +-----+    +------+
    |         |    |           |           |     |    |      |
    7         9    11          13          8     10   12     14
+-----+   +-----+  +--+    +-------+                       +-----+ 
|     |   |     |     |    |       |                       |     |
15    17  19    21    23   27      29                     16     18
                                                                 +---+
                                                                     |
                                                                     20

You can find the node values and hierarchical level with the following query:

SELECT   tt.child_id
,        LEVEL
FROM     test_temp tt
WHERE    CONNECT_BY_ISLEAF  = 1
START
WITH     tt.parent_id IS NULL 
CONNECT
BY PRIOR tt.child_id = tt.parent_id
ORDER BY 2;

We really don’t need the node values to solve the problem. We only need the lowest LEVEL value returned by the query, which is 3. The combination of the MIN and CONNECT_BY_ISLEAF functions let us solve this problem without writing a PL/SQL solution. The subquery returns the lowest level value, which is the first level where a leaf node occurs.

SELECT   LPAD(' ', 2*(LEVEL - 1)) || tt.child_id AS child_id
FROM     test_temp tt
WHERE    LEVEL <= (SELECT   MIN(LEVEL)
                   FROM     test_temp tt
                   WHERE    CONNECT_BY_ISLEAF  = 1
                   START
                   WITH     tt.parent_id IS NULL 
                   CONNECT
                   BY PRIOR tt.child_id = tt.parent_id)
START
WITH     tt.parent_id IS NULL
CONNECT
BY PRIOR tt.child_id = tt.parent_id;

It returns:

               1                                    2
        +-------------+                       +-----------+
        |             |                       |           |      
        3             5                       4           6
    +---------+    +-----------+           +-----+    +------+
    |         |    |           |           |     |    |      |
    7         9    11          13          8     10   12     14

While I answered the question in a comment originally, it seemed an important trick that should be shared in its own post.

Planning out my year, I decided to take the Oracle OCP and MySQL OCP exams. I checked for review books and was pleasantly surprised to find the soon to be released OCP MySQL Database Administrator Exam Guide (Exam 1Z0-883). However, I noticed that the book was actually prepared for the obsolete and discountinued Exams 1Z0-870, 1Z0-873, and 1Z0-874. As it turns out, Steve O’Hearn has informed me that there isn’t a book and that the posting in Amazon.com is in error.

There isn’t an alternative review book for the OCP MySQL 5.6 Developer or Database Administrator Exams. The question that I have is simple: “How relevant is this book because it was prepared for the older exams?” There isn’t a table of content published on the Amazon.com site. If there was a table of contents it could help me determine how close the book’s content is to the new exam.

As a preparation to figure out the value of the book as a study guide, I’ve reviewed the current Oracle MySQL Training Objectives (listed below). The new MySQL OCP Developer and Administrator exams have the following descriptions and objectives:

MySQL 5.6 Developer 1Z0-882. Oracle provides the following outline for their MySQL for Developer (Ed 3) training course:

Course Objectives
- Describe the MySQL client/server architecture
- Use MySQL client programs and common options
- Program MySQL applications with Java and PHP connectors
- Use a “NoSQL” approach to store and retrieve data
- Design efficient tables
- Create and delete database objects
- Use expressions in SQL statements
- Examine database metadata
- Use SQL statements to modify table data
- Maintain database integrity with transactions
- Write multiple table queries
- Create “virtual tables” containing specific data
- Create user-defined variables, prepared statements, and stored routines
- Create and manage triggers
- Identify and deal with errors and exceptions in client programs
- Write queries that are fast and effective, even under heavy loads
MySQL 5.6 Database Administrator 1Z0-883. Oracle provides the following outline for their MySQL for Database Administrators (Ed 3.1) training course:

Course Objectives
- Describe the MySQL Architecture
- Install and Upgrade MySQL
- Use the INFORMATION_SCHEMA database to access metadata
- Perform the MySQL start and shutdown operations
- Configure MySQL server options at runtime
- Use available tools including MySQL Workbench
- Evaluate data types and character sets for performance issues
- Understand data locking in MySQL
- Understand the use of the InnoDB storage engine with MySQL
- Maintain integrity of a MySQL installation
- Use triggers for administration tasks
- Use Enterprise Audit and Pluggable Authentication
- Configure advanced replication topologies for high availability
- Describe introductory performance tuning techniques
- Perform backup and restore operations
- Automate administrative tasks with scheduled events
As always, I hope this helps those who read it; and, in this case I hope it helps you make an effective decision on preparation resources for the MySQL 5.6 OCP exams.

Back in January 2014, I wrote a script to cleanup an Oracle student schema. It worked well until I started using APEX 4 in my student schema. You create the following 75 objects when you create an APEX 4 schema.

OBJECT TYPE    TOTAL
------------ -------
TABLE		  17
INDEX		  28
SEQUENCE	   5
TRIGGER 	  14
LOB		   9
FUNCTION	   2

Here’s the modified script that ignores the objects created automatically by Oracle APEX when you create a student workspace:

BEGIN
  FOR i IN (SELECT    object_name
            ,         object_type
            ,         last_ddl_time
            FROM      user_objects
            WHERE     object_name NOT IN
                       ('APEX$_WS_WEBPG_SECTION_HISTORY','APEX$_WS_WEBPG_SECTIONS_T1'
                       ,'APEX$_WS_WEBPG_SECTIONS_PK','APEX$_WS_WEBPG_SECTIONS'
                       ,'APEX$_WS_WEBPG_SECHIST_IDX1','APEX$_WS_TAGS_T1'
                       ,'APEX$_WS_TAGS_PK','APEX$_WS_TAGS_IDX2','APEX$_WS_TAGS_IDX1'
                       ,'APEX$_WS_TAGS','APEX$_WS_ROWS_T1','APEX$_WS_ROWS_PK'
                       ,'APEX$_WS_ROWS_IDX','APEX$_WS_ROWS','APEX$_WS_NOTES_T1'
                       ,'APEX$_WS_NOTES_PK','APEX$_WS_NOTES_IDX2','APEX$_WS_NOTES_IDX1'
                       ,'APEX$_WS_NOTES','APEX$_WS_LINKS_T1','APEX$_WS_LINKS_PK'
                       ,'APEX$_WS_LINKS_IDX2','APEX$_WS_LINKS_IDX1','APEX$_WS_LINKS'
                       ,'APEX$_WS_HISTORY_IDX','APEX$_WS_HISTORY','APEX$_WS_FILES_T1'
                       ,'APEX$_WS_FILES_PK','APEX$_WS_FILES_IDX2','APEX$_WS_FILES_IDX1'
                       ,'APEX$_WS_FILES','APEX$_ACL_T1','APEX$_ACL_PK','APEX$_ACL_IDX1'
                       ,'APEX$_ACL','CUSTOM_AUTH','CUSTOM_HASH','DEPT','EMP'
                       ,'UPDATE_ORDER_TOTAL')
            AND NOT ((object_name LIKE 'DEMO%' OR
                      object_name LIKE 'INSERT_DEMO%' OR
                      object_name LIKE 'BI_DEMO%') AND
                      object_type IN ('TABLE','INDEX','SEQUENCE','TRIGGER'))
            AND NOT (object_name LIKE 'SYS_LOB%' AND object_type = 'LOB')
            AND NOT (object_name LIKE 'SYS_C%' AND object_type = 'INDEX')
            ORDER BY object_type DESC) LOOP
 
    /* Drop types in descending order. */
    IF i.object_type = 'TYPE' THEN
 
      /* Drop type and force operation because dependencies may exist. Oracle 12c
         also fails to remove object types with dependents in pluggable databases
         (at least in release 12.1). Type evolution works in container database
         schemas. */
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' FORCE';
 
    /* Drop table tables in descending order. */
    ELSIF i.object_type = 'TABLE' THEN
 
      /* Drop table with cascading constraints to ensure foreign key constraints
         don't prevent the action. */
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS PURGE';
 
      /* Oracle 12c ONLY: Purge the recyclebin to dispose of system-generated
         sequence values because dropping the table doesn't automatically 
         remove them from the active session.
         CRITICAL: Remark out the following when working in Oracle Database 11g. */
      EXECUTE IMMEDIATE 'PURGE RECYCLEBIN';
 
    ELSIF i.object_type = 'LOB' OR i.object_type = 'INDEX' THEN
 
      /* A system generated LOB column or INDEX will cause a failure in a
         generic drop of a table because it is listed in the cursor but removed
         by the drop of its table. This NULL block ensures there is no attempt
         to drop an implicit LOB data type or index because the dropping the
         table takes care of it. */
      NULL;
 
    ELSE
 
      dbms_output.put_line('DROP '||i.object_type||' '||i.object_name||';');
      /* Drop any other objects, like sequences, functions, procedures, and packages. */
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name;
 
    END IF;
  END LOOP;
END;
/

BEGIN FOR i IN (SELECT object_name , object_type , last_ddl_time FROM user_objects WHERE object_name NOT IN ('APEX$_WS_WEBPG_SECTION_HISTORY','APEX$_WS_WEBPG_SECTIONS_T1' ,'APEX$_WS_WEBPG_SECTIONS_PK','APEX$_WS_WEBPG_SECTIONS' ,'APEX$_WS_WEBPG_SECHIST_IDX1','APEX$_WS_TAGS_T1' ,'APEX$_WS_TAGS_PK','APEX$_WS_TAGS_IDX2','APEX$_WS_TAGS_IDX1' ,'APEX$_WS_TAGS','APEX$_WS_ROWS_T1','APEX$_WS_ROWS_PK' ,'APEX$_WS_ROWS_IDX','APEX$_WS_ROWS','APEX$_WS_NOTES_T1' ,'APEX$_WS_NOTES_PK','APEX$_WS_NOTES_IDX2','APEX$_WS_NOTES_IDX1' ,'APEX$_WS_NOTES','APEX$_WS_LINKS_T1','APEX$_WS_LINKS_PK' ,'APEX$_WS_LINKS_IDX2','APEX$_WS_LINKS_IDX1','APEX$_WS_LINKS' ,'APEX$_WS_HISTORY_IDX','APEX$_WS_HISTORY','APEX$_WS_FILES_T1' ,'APEX$_WS_FILES_PK','APEX$_WS_FILES_IDX2','APEX$_WS_FILES_IDX1' ,'APEX$_WS_FILES','APEX$_ACL_T1','APEX$_ACL_PK','APEX$_ACL_IDX1' ,'APEX$_ACL','CUSTOM_AUTH','CUSTOM_HASH','DEPT','EMP' ,'UPDATE_ORDER_TOTAL') AND NOT ((object_name LIKE 'DEMO%' OR object_name LIKE 'INSERT_DEMO%' OR object_name LIKE 'BI_DEMO%') AND object_type IN ('TABLE','INDEX','SEQUENCE','TRIGGER')) AND NOT (object_name LIKE 'SYS_LOB%' AND object_type = 'LOB') AND NOT (object_name LIKE 'SYS_C%' AND object_type = 'INDEX') ORDER BY object_type DESC) LOOP /* Drop types in descending order. */ IF i.object_type = 'TYPE' THEN /* Drop type and force operation because dependencies may exist. Oracle 12c also fails to remove object types with dependents in pluggable databases (at least in release 12.1). Type evolution works in container database schemas. */ EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' FORCE'; /* Drop table tables in descending order. */ ELSIF i.object_type = 'TABLE' THEN /* Drop table with cascading constraints to ensure foreign key constraints don't prevent the action. */ EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS PURGE'; /* Oracle 12c ONLY: Purge the recyclebin to dispose of system-generated sequence values because dropping the table doesn't automatically remove them from the active session. CRITICAL: Remark out the following when working in Oracle Database 11g. */ EXECUTE IMMEDIATE 'PURGE RECYCLEBIN'; ELSIF i.object_type = 'LOB' OR i.object_type = 'INDEX' THEN /* A system generated LOB column or INDEX will cause a failure in a generic drop of a table because it is listed in the cursor but removed by the drop of its table. This NULL block ensures there is no attempt to drop an implicit LOB data type or index because the dropping the table takes care of it. */ NULL; ELSE dbms_output.put_line('DROP '||i.object_type||' '||i.object_name||';'); /* Drop any other objects, like sequences, functions, procedures, and packages. */ EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name; END IF; END LOOP; END; /

As always, I hope this helps others.

Sometimes trying to keep a post short and to the point raises other questions. Clearly, my Python-MySQL Program post over the weekend did raise a question. They were extending the query example and encountered this error:

TypeError: range() integer end argument expected, got tuple.

That should be a straight forward error message because of two things. First, the Python built-in range() function manages a range of numbers. Second, the row returned from a cursor is actually a tuple (from relational algebra), and it may contain non-numeric data like strings and dates.

The reader was trying to dynamically navigate the number of columns in a row by using the range() function like this (where row was a row from the cursor or result set):

    for j in range(row):

Naturally, it threw the type mismatch error noted above. As promised, the following Python program fixes that problem. It also builds on the prior example by navigatung an unknown list of columns. Lines 16 through 31 contain the verbose comments and programming logic to dynamically navigate the columns of a row.

#!/usr/bin/python
 
# Import sys library.
import MySQLdb
import sys
 
try:
  # Create new database connection.
  db = MySQLdb.connect('localhost','student','student','studentdb')
  # Create a result set cursor.
  rs = db.cursor()
  rs.execute("SELECT item_title, item_subtitle, item_rating FROM item")
  # Assign the query results to a local variable.
  for i in range(rs.rowcount):
    row = rs.fetchone()
    # Initialize variable for printing row as a string.
    data = ""
    # Address an indefinite number of columns.
    count = 0
    for j in range(len(row)):
      # Initialize column value as an empty string.
      datum = ""
      # Replace column values when they exist.
      if str(row[count]) != 'None':
        datum = str(row[count])
      # Append a comma when another column follows.
      if count == len(row) - 1:
        data += datum
      else:
        data += datum + ", "
      count += 1
    # Print the formatted row as a string.
    print data
except MySQLdb.Error, e:
  # Print the error.
  print "ERROR %d: %s" % (e.args[0], e.args[1])
  sys.exit(1)
finally:
  # Close the connection when it is open.
  if db:
    db.close()

There are a couple Python programming techniques that could be perceived as tricks. Line 24 checks for a not null value by explicitly casting the column’s value to a string and then comparing its value against the string equivalent for a null. The MySQLdb returns a 'None' string for null values by default. The if-block on lines 27 through 30 ensure commas aren’t appended at the end of a row.

While the for-loop with a range works, I’d recommend you write it as a while-loop because its easier to read for most new Python programmers. You only need to replace line 20 with the following to make the change:

20	while (count < len(row)):

Either approach generates output like:

The Hunt for Red October, Special Collectornulls Edition, PG
Star Wars I, Phantom Menace, PG
Star Wars II, Attack of the Clones, PG
Star Wars II, Attack of the Clones, PG
Star Wars III, Revenge of the Sith, PG-13
The Chronicles of Narnia, The Lion, the Witch and the Wardrobe, PG
RoboCop, , Mature
Pirates of the Caribbean, , Teen
The Chronicles of Narnia, The Lion, the Witch and the Wardrobe, Everyone
MarioKart, Double Dash, Everyone
Splinter Cell, Chaos Theory, Teen
Need for Speed, Most Wanted, Everyone
The DaVinci Code, , Teen
Cars, , Everyone
Beau Geste, , PG
I Remember Mama, , NR
Tora! Tora! Tora!, The Attack on Pearl Harbor, G
A Man for All Seasons, , G
Hook, , PG
Around the World in 80 Days, , G
Harry Potter and the Sorcerer's Stone, , PG
Camelot, , G

As always, I hope this helps those looking for clarity.

Configuring Perl to work with MySQL is the last part creating a complete Fedora Linux LAMP stack for my students. Perl is already installed on Fedora Linux.

I’ve also shown how to use PHP, Python, and Ruby languages to query a MySQL database on Linux. After installing this additional Perl DBI library, my students will have the opportunity to choose how they implement their LAMP solution.

You can find the Perl version with the following version.pl program:

#!/usr/bin/perl -w
 
# Print the version.
print "Perl ".$]."\n";

The first line lets you call the program without prefacing the perl program because it invokes a subshell of perl by default. You just need to ensure the file has read and execute privileges to run. It prints:

Perl 5.018004

You need to install the perl-DBD-MySQL library to enable Perl to work with MySQL. The following command loads the library:

yum install -y perl-DBD-MySQL

It prints the following log file:

Loaded plugins: langpacks, refresh-packagekit
Resolving Dependencies
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:4.024-1.fc20 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package               Arch          Version                Repository     Size
================================================================================
Installing:
 perl-DBD-MySQL        x86_64        4.024-1.fc20           fedora        142 k
 
Transaction Summary
================================================================================
Install  1 Package
 
Total download size: 142 k
Installed size: 332 k
Downloading packages:
perl-DBD-MySQL-4.024-1.fc20.x86_64.rpm                      | 142 kB  00:00     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction (shutdown inhibited)
  Installing : perl-DBD-MySQL-4.024-1.fc20.x86_64                           1/1 
  Verifying  : perl-DBD-MySQL-4.024-1.fc20.x86_64                           1/1 
 
Installed:
  perl-DBD-MySQL.x86_64 0:4.024-1.fc20                                          
 
Complete!

The following item_query.pl Perl program is consistent with the PHP, Python, and Ruby examples provided in other blog posts. It shows you how to use the Perl DBI library to query and return a data set.

#!/usr/bin/perl -w
 
# Use the DBI library.
use DBI;
use strict;
use warnings;
 
# Create a connection.
my $dbh = DBI->connect("DBI:mysql:database=studentdb;host=localhost:3306","student","student",{'RaiseError' => 1});
 
# Create SQL statement.
my $sql = "SELECT item_title FROM item";
 
# Prepare SQL statement.
my $sth = $dbh->prepare($sql);
 
# Execute statement and read result set.
$sth->execute() or die $DBI::errstr;
while (my @row = $sth->fetchrow_array()) {
  my $item_title = $row[0];
  print "$item_title\n";
}
 
# Close resources.
$sth->finish();

You call it like this from the present working directory:

./mysql_query.pl

It returns:

The Hunt for Red October
Star Wars I
Star Wars II
Star Wars II
Star Wars III
The Chronicles of Narnia
RoboCop
Pirates of the Caribbean
The Chronicles of Narnia
MarioKart
Splinter Cell
Need for Speed
The DaVinci Code
Cars
Beau Geste
I Remember Mama
Tora! Tora! Tora!
A Man for All Seasons
Hook
Around the World in 80 Days
Harry Potter and the Sorcerer's Stone
Camelot

Alternatively, there’s a different syntax for lines 20 and 21 that you can use when you’re returning multiple columns. It replaces the two statements inside the while loop as follows:

20
21

  my ($item_title, $item_rating) = @row;
  print "$item_title, $item_rating\n";

It returns:

The Hunt for Red October, PG
Star Wars I, PG
Star Wars II, PG
Star Wars II, PG
Star Wars III, PG13
The Chronicles of Narnia, PG
RoboCop, Mature
Pirates of the Caribbean, Teen
The Chronicles of Narnia, Everyone
MarioKart, Everyone
Splinter Cell, Teen
Need for Speed, Everyone
The DaVinci Code, Teen
Cars, Everyone
Beau Geste, PG
I Remember Mama, NR
Tora! Tora! Tora!, G
A Man for All Seasons, G
Hook, PG
Around the World in 80 Days, G
Harry Potter and the Sorcerer's Stone, PG
Camelot, G

As always, I hope this helps those learning how to use Perl and Linux against the MySQL Database. If you want a nice tutorial on Perl and MySQL, check The tutorialspoint.com web site.

What the MySQL team is doing with JSON (JavaScript Object Notation) in MySQL 5.7 is great! The MySQL Server Blog (Rick Hillegas and Dag Wanvik) published two key articles about new JSON functions. If you don’t follow these, let me highlight them as a set:

Most folks know how important JSON is to web development. I like the following visual that highlights it. It was provided as a comment to this earlier Popular Programming Language post by Michael Farmer. Clearly, JavaScript is popular because it’s critical to effective web development. If you’re new to JSON, check out Adam Khoury’s JSON tutorial set on YouTube.

If you want the original graphic, you can find it here. It’s always to hard to keep up with the technology, isn’t it? 🙂

MacLochlainns Weblog

Archive for the ‘sql’ Category

ORDER BY CASE

Mac SQL Developer Install

Bash Arrays & Oracle

Bash Arrays & MySQL

Leaf node queries

MySQL OCP Exams

Oracle Cleanup a Schema

MySQLdb Manage Columns

Perl-MySQL Program

MySQL JSON Functions

Recent Posts

Things Written About

Pages

Blogroll

Archives