MacLochlainns Weblog

As I’ve mentioned before, I try to support Oracle, MySQL, and SQL Server in my database classes. When I downloaded SQL Server 2008 R2 Express Edition, I just downloaded the Database with Management Tools. That’s the one on the first page and shown on the left.

It appears what I really wanted was the Database with Advanced Services because I found basic features weren’t available in the Database with Management Tools version. So, you should click the Other Installation Options link. On that page you can choose between the various options.

For example, you can’t use an IF statement or local variable assignment. Also, the available data types are a subset of the mainline product. You can’t use a VARCHAR but must use the NVARCHAR. Many features of the Microsoft SQL Server Management Studio are removed too. A brief forum discussion shows that these types of problem exist in other versions too.

I thought a couple quick examples of raised exceptions would be helpful. They’re listed below.

Missing IF Syntax:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CORE_SYSTEM_USER')
  DROP TABLE CORE_SYSTEM_USER
GO

Generated error message:

Major Error 0x80040E14, Minor Error 25501
> IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CORE_SYSTEM_USER')
  DROP TABLE CORE_SYSTEM_USER
There was an error parsing the query. [ Token line NUMBER = 1,Token line offset = 1,Token IN error = IF ]

Local variable assignment Syntax:

DECLARE @TABLE_NAME nvarchar(30)
 
@TABLE_NAME = SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CORE_SYSTEM_USER'
GO

Generated error message:

Major Error 0x80040E14, Minor Error 25501
> DECLARE @TABLE_NAME nvarchar(30)
 
@TABLE_NAME = SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CORE_SYSTEM_USER'
There was an error parsing the query. [ Token line NUMBER = 1,Token line offset = 1,Token IN error = DECLARE ]

After I clear out the install and retry it with the other, I’ll update this as to whether or not these are available in the Database with Advanced Services SQL Server 2008 R2 Express Edition.

I popped for QuickTime Pro for Windows 7 ($29.99). The reason for doing so, was to create native screen capture that would integrate with Mac Adobe Premiere Pro, CS4. That’s because my Camtasia output didn’t work due to a compression mismatch.

Unfortunately, QuickTime Pro on Windows 7 doesn’t support screen capture. Quite a difference between what the product does on a Mac OS X versus a Windows 7 OS. I thought only Microsoft shorted users on the other platform, like Microsoft Excel 2007 versus Microsoft Excel 2008 (a far inferior product). Oops, I was wrong! Apple does it too. 🙁

I overlooked MySQL’s real equivalent to a MERGE statement, which is the REPLACE INTO statement. A previous example uses the INSERT statement with the ON DUPLICATE KEY clause. The following demonstrates how to perform a left join from and exernal source. These related posts all started with this one.

Demonstration

Here are the steps to accomplish an import/upload with the REPLACE INTO statement. In this example, you upload data from a flat file, or Comma Separated Value (CSV) file to a denormalized table (actually in unnormalized form). This type of file upload transfers information that doesn’t have surrogate key values. You have to create those in the scope of the transformation to the normalized tables.

Step #1 : Position your CSV file in the physical directory

After creating the virtual directory, copy the following contents into a file named kingdom_mysql_import.csv in the C:\Data\Download directory or folder. If you have Windows UAC enabled in Windows Vista or 7, you should disable it before performing this step.

Place the following in the kingdom_mysql_import.csv file. The trailing commas are meaningful in MySQL and avoid problems when reading CSV files.

Narnia, 77600,'Peter the Magnificent',12720320,12920609,
Narnia, 77600,'Edmund the Just',12720320,12920609,
Narnia, 77600,'Susan the Gentle',12720320,12920609,
Narnia, 77600,'Lucy the Valiant',12720320,12920609,
Narnia, 42100,'Peter the Magnificent',15310412,15310531,
Narnia, 42100,'Edmund the Just',15310412,15310531,
Narnia, 42100,'Susan the Gentle',15310412,15310531,
Narnia, 42100,'Lucy the Valiant',15310412,15310531,
Camelot, 15200,'King Arthur',06310310,06861212,
Camelot, 15200,'Sir Lionel',06310310,06861212,
Camelot, 15200,'Sir Bors',06310310,06351212,
Camelot, 15200,'Sir Bors',06400310,06861212,
Camelot, 15200,'Sir Galahad',06310310,06861212,
Camelot, 15200,'Sir Gawain',06310310,06861212,
Camelot, 15200,'Sir Tristram',06310310,06861212,
Camelot, 15200,'Sir Percival',06310310,06861212,
Camelot, 15200,'Sir Lancelot',06700930,06821212,

Step #2 : Connect as the student user

Disconnect and connect as the student user, or reconnect as the student user. The reconnect syntax that protects your password is:

mysql -ustudent -p

Connect to the sampledb database, like so:

mysql> USE sampledb;

Step #3 : Run the script that creates tables and sequences

Copy the following into a create_mysql_kingdom_upload.sql file within a directory of your choice. Then, run it as the student account.

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

-- This enables dropping tables with foreign key dependencies.
-- It is specific to the InnoDB Engine.
SET FOREIGN_KEY_CHECKS = 0; 
 
-- Conditionally drop objects.
SELECT 'KINGDOM' AS "Drop Table";
DROP TABLE IF EXISTS KINGDOM;
 
SELECT 'KNIGHT' AS "Drop Table";
DROP TABLE IF EXISTS KNIGHT;
 
SELECT 'KINGDOM_KNIGHT_IMPORT' AS "Drop Table";
DROP TABLE IF EXISTS KINGDOM_KNIGHT_IMPORT;
 
-- Create normalized kingdom table.
SELECT 'KINGDOM' AS "Create Table";
CREATE TABLE kingdom
( kingdom_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, kingdom_name  VARCHAR(20)
, population    INT UNSIGNED) ENGINE=INNODB;
 
-- Create normalized knight table.
SELECT 'KNIGHT' AS "Create Table";
CREATE TABLE knight
( knight_id             INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, knight_name           VARCHAR(24)
, kingdom_allegiance_id INT UNSIGNED
, allegiance_start_date DATE
, allegiance_end_date   DATE
, CONSTRAINT fk_kingdom FOREIGN KEY (kingdom_allegiance_id)
  REFERENCES kingdom (kingdom_id)) ENGINE=INNODB;
 
-- Create external import table in memory only - disappears after rebooting the mysqld service.
SELECT 'KINGDOM_KNIGHT_IMPORT' AS "Create Table";
CREATE TABLE kingdom_knight_import
( kingdom_name          VARCHAR(20)
, population            INT UNSIGNED
, knight_name           VARCHAR(24)
, allegiance_start_date DATE
, allegiance_end_date   DATE) ENGINE=MEMORY;

Step #4 : Load the data into your target upload table

There a number of things that could go wrong but when you choose LOCAL there generally aren’t any problems. Run the following query from the student account while using the sampledb database, and check whether or not you can access the kingdom_import.csv file.

1
2
3
4
5
6

LOAD DATA LOCAL INFILE 'c:/Data/kingdom_mysql_import.csv'
INTO TABLE kingdom_knight_import
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';

Step #5 : Create the upload procedure

Copy the following into a create_mysql_upload_procedure.sql file within a directory of your choice. You should note that unlike Oracle’s MERGE statement, this is done with the ON DUPLICATE KEY clause and requires actual values not a source query. This presents few options other than a stored routine, known as a stored procedure. As you can see from the code, there’s a great deal of complexity to the syntax and a much more verbose implementation than Oracle’s equivalent PL/SQL.

Then, run it as the student account. As you look at the structure to achieve this simple thing, the long standing complaint about PL/SQL being a verbose language comes to mind. Clearly, stored procedures are new to MySQL but they’re quite a bit more verbose than PL/SQL.

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

-- Conditionally drop the procedure.
SELECT 'UPLOAD_KINGDOM' AS "Drop Procedure";
DROP PROCEDURE IF EXISTS upload_kingdom;
 
-- Reset the execution delimiter to create a stored program.
DELIMITER $$
 
-- The parentheses after the procedure name must be there or the MODIFIES SQL DATA raises an compile time exception.
CREATE PROCEDURE upload_kingdom() MODIFIES SQL DATA
 
BEGIN
 
  /* Declare a handler variables. */
  DECLARE duplicate_key INT DEFAULT 0;
  DECLARE foreign_key   INT DEFAULT 0;
 
  /* Declare a duplicate key handler */
  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1;
  DECLARE CONTINUE HANDLER FOR 1216 SET foreign_key = 1;
 
  /* ---------------------------------------------------------------------- */
 
  /* Start transaction context. */
  START TRANSACTION;
 
  /* Set savepoint. */  
  SAVEPOINT both_or_none;
 
  /* Open a local cursor. */  
  REPLACE INTO kingdom
  (SELECT   DISTINCT
            k.kingdom_id
   ,        kki.kingdom_name
   ,        kki.population
   FROM     kingdom_knight_import kki LEFT JOIN kingdom k
   ON       kki.kingdom_name = k.kingdom_name
   AND      kki.population = k.population);
 
   REPLACE INTO knight
   (SELECT   kn.knight_id
    ,        kki.knight_name
    ,        k.kingdom_id
    ,        kki.allegiance_start_date AS start_date
    ,        kki.allegiance_end_date AS end_date
    FROM     kingdom_knight_import kki INNER JOIN kingdom k
    ON       kki.kingdom_name = k.kingdom_name
    AND      kki.population = k.population LEFT JOIN knight kn
    ON       k.kingdom_id = kn.kingdom_allegiance_id
    AND      kki.knight_name = kn.knight_name
    AND      kki.allegiance_start_date = kn.allegiance_start_date
    AND      kki.allegiance_end_date = kn.allegiance_end_date);
 
  /* ---------------------------------------------------------------------- */
 
  /* This acts as an exception handling block. */  
  IF duplicate_key = 1 OR foreign_key = 1 THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT both_or_none;
 
  ELSE
 
    /* This commits the writes. */
    COMMIT;
 
  END IF;
 
END;
$$
 
-- Reset the delimiter to the default.
DELIMITER ;

Step #6 : Run the upload procedure

You can run the file by calling the stored procedure built by the script. The procedure ensures that records are inserted or updated into their respective tables.

CALL upload_kingdom;

Step #7 : Test the results of the upload procedure

You can test whether or not it worked by running the following queries.

-- Check the kingdom table.
SELECT * FROM kingdom;
SELECT * FROM knight;

It should display the following information:

+------------+--------------+------------+
| kingdom_id | kingdom_name | population |
+------------+--------------+------------+
|          1 | Narnia       |      77600 |
|          2 | Narnia       |      42100 |
|          3 | Camelot      |      15200 |
+------------+--------------+------------+
 
+-----------+-------------------------+-----------------------+-----------------------+---------------------+
| knight_id | knight_name             | kingdom_allegiance_id | allegiance_start_date | allegiance_end_date |
+-----------+-------------------------+-----------------------+-----------------------+---------------------+
|         1 | 'Peter the Magnificent' |                     1 | 1272-03-20            | 1292-06-09          |
|         2 | 'Edmund the Just'       |                     1 | 1272-03-20            | 1292-06-09          |
|         3 | 'Susan the Gentle'      |                     1 | 1272-03-20            | 1292-06-09          |
|         4 | 'Lucy the Valiant'      |                     1 | 1272-03-20            | 1292-06-09          |
|         5 | 'Peter the Magnificent' |                     2 | 1531-04-12            | 1531-05-31          |
|         6 | 'Edmund the Just'       |                     2 | 1531-04-12            | 1531-05-31          |
|         7 | 'Susan the Gentle'      |                     2 | 1531-04-12            | 1531-05-31          |
|         8 | 'Lucy the Valiant'      |                     2 | 1531-04-12            | 1531-05-31          |
|         9 | 'King Arthur'           |                     3 | 0631-03-10            | 0686-12-12          |
|        10 | 'Sir Lionel'            |                     3 | 0631-03-10            | 0686-12-12          |
|        11 | 'Sir Bors'              |                     3 | 0631-03-10            | 0635-12-12          |
|        12 | 'Sir Bors'              |                     3 | 0640-03-10            | 0686-12-12          |
|        13 | 'Sir Galahad'           |                     3 | 0631-03-10            | 0686-12-12          |
|        14 | 'Sir Gawain'            |                     3 | 0631-03-10            | 0686-12-12          |
|        15 | 'Sir Tristram'          |                     3 | 0631-03-10            | 0686-12-12          |
|        16 | 'Sir Percival'          |                     3 | 0631-03-10            | 0686-12-12          |
|        17 | 'Sir Lancelot'          |                     3 | 0670-09-30            | 0682-12-12          |
+-----------+-------------------------+-----------------------+-----------------------+---------------------+

You can rerun the procedure to check that it doesn’t alter any information, then you could add a new knight to test the insertion portion.

Looking through an error on the web, I notices that the solution is nested in Ask Tom. That’s true for so many solutions, but they likewise have long discussions like this one in the OraFAQ Forum.

It seems that most folks search on is the following. The problem appears to be linked to attempts to call the DBMS_SESSION.SET_CONTEXT directly in their code, instead of through a predefined procedure. The procedure is generally inside a security package in a security schema for reference.

BEGIN
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 94
ORA-06512: at line 2

I figured it might help to provide a simple example because I use VPDs in my second database class, and this is where some of my students get hung up. It strikes me others in the Oracle community may get stuck here too.

1. Create a user with necessary permissions as the SYSTEM user:

CREATE USER sample IDENTIFIED BY sample;
GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE ANY PROCEDURE TO sample;

2. Create the CONTEXT reference as the SAMPLE user, which uses a function to populate the CONTEXT.

CREATE OR REPLACE CONTEXT sample_ctx USING set_context;

3. Create the function as the SAMPLE user to set the context. The CONTEXT is a literal value inside the procedure with a name and value pair.

CREATE OR REPLACE PROCEDURE set_context
( pname  VARCHAR2
, pvalue VARCHAR2) IS
BEGIN
  -- Create a session with a previously defined context.
  DBMS_SESSION.SET_CONTEXT('SAMPLE_CTX',pname,pvalue);
END;
/

4. Set the local session sample_ctx CONTEXT as the SAMPLE user.

EXECUTE set_context('email','sherman@atlanta.org');

5. You now query the user-defined CONTEXT with case insensitive strings that match the CONTEXT and pname call parameter that you set it. The following shows that query against dual. You should note that it returns a case sensitive string of the pvalue call parameter.

SELECT sys_context('sample_ctx','email') FROM dual;

As always, I hope this helps somebody and saves them time.

I was working through some example files and test scripts with Virtual Private Databases and discovered a nifty and potentially misleading error. Google didn’t pick up any search results with it, so I thought noting it would be a good idea.

When you create a security policy with DBMS_RLS.ADD_POLICY incorrectly, and then try to drop it, you must make sure to include the OBJECT_SCHEMA parameter. If you don’t and provide named parameters like the following, you’ll raise an error.

BEGIN
  DBMS_RLS.DROP_POLICY(object_name=>'valid_table'
                      ,policy_name=>'valid_policy');
END;
/

The error is quite misleading, as shown below.

BEGIN
*
ERROR at line 1:
ORA-28103: adding a policy TO an object owned BY SYS IS NOT allowed
ORA-06512: at "SYS.DBMS_RLS", line 59
ORA-06512: at line 2

The error is actually triggered when the OBJECT_SCHEMA is required. The default value is a NULL in the DBMS_RLS package specification.

The correct syntax is:

BEGIN
  DBMS_RLS.DROP_POLICY(object_schema=>'valid_schema'
                      ,object_name=>'valid_table'
                      ,policy_name=>'valid_policy');
END;
/

Two posts in a row on hardware failures – whew :-(. Just after fixing my Mac Pro Video card, one of my 1 TB Seagate Barracuda hard drives failed. Naturally, it’s the one with half a terabyte of virtual machines.

First thing I checked was whether or not the disk still spun. It did but was not recognized by the Mac OS X or Windows 7 OS. Then, I swapped the disk controller card with another one of the same model numbered disks. The Mac OS X recognized it and launched Disk Utility but with a catch. I’d need to re-partition it, which would trash the important data.

I’m now working on recovering it byte-by-byte, big ouch! As time consuming as it is, I’m going to start performing weekly backups to DVD on my test and development machine.

To add insult to injury, I found out that Seagate knew about the problem but kept it quiet. Hoping the data recovery works, I’ll do an RMA afterward on the broken drive, which also makes a clicking noise after the disk controller failure.

This is the Seagate Barracuda 7200.11 (1 Terabyte) Drive, Model #ST3100340AS, which was heralded for its breakthrough four 250 GB platters. It is prone to failure. You may avoid failure by applying a firmware update but only before the disk fails. I’ve got two more of these that require a firmware update. Also, my eroded confidence in Seagate causes me to worry whether they’re in my External Seagate drives. I use them for time machines. You can read the story from January 2009, which unfortunately I missed before now.

A quick update, you can download DriveDetect from Seagate if you’re running the Windows OS but they’ve not got a utility for Mac OS X. You’ll have to manually pull the drives. For the USB drives, it appears that you’ll need a PC to run their utility.

It’s been a terribly busy three weeks, needless to say when I got back to working my Mac Pro video card failed. That was Saturday, and I’m waiting on the replacement card. Thank goodness for AppleCare because the card is still $200.

In the meantime, I was stuck without being able to use Screen Sharing. A friend, Kyle, told me how to use Target Disk Mode to boot my Mac Pro on my MacBook Pro, which let me enable Screen Sharing without trying to sort through the configuration files. This basically lets me use my MacBook Pro as a bridge until I get the new video card.

I figured it would be nice to label how to do it. First, you’ll need a FireWire 800 cable or with older equipment a FireWire 400 cable will work. Connect the two machines, then:

1. Boot the disabled machine in Disk Target Mode by holding down the “T” key when turning on the physical machine.
2. Boot the target console by holding down the Option key.
3. You’ll see two disks, one should be the standard silver disk icon and the other a gold remote disk icon. Use the right arrow key to select the gold remote disk, then click the return key.

Your target machine is now booted off the remote machine. You can now navigate to System Preferences, choose Sharing, and check the Checkbox for Screen Sharing. Now you can shutdown both systems and use screen sharing or continue to work this way. It’s really your preference. I like screen sharing because then I can use the resources from both machines.

This may be old hat to many but I’ve got a hunch it’s news to others. While I knew there was a way to do this, I’d never had to try it until now.

An update 4/27/10, AppleCare got me the replacement video card. I installed it and it worked like a charm.

Added a new PHP Function Primer for my students. This one’s not large but fits as a page better. If you’re interested it’s here.

It covers:

• Function Definitions
• Creating & Using Functions

• Pass-by-value Functions
• Pass-by-reference Functions
• Dynamic Functions

• Function Default Parameters
• Variable-length Parameter Lists

I finally got around to writing that PHP Tutorial. It’s a bit large and takes about 10 seconds to load or longer depending on your connection and machine. It covers the basics from writing your first page to loops. It was too large for a blog post, so it’s a blog page. Click on the link if you’d like to check it out.

I plan others on functions, objects, and files. Then, I’ll get to tutorials against databases.

Naturally, suggestions are always welcome.

My son’s graduating with his B.S. in Computer Information Technology next month, moving on to his internship, and today released his first independent iPhone App – Chronos Stopwatch. This link takes you to his blog. He did an awesome job on writing the Bizarro iPhone App under contract, but I’m very happy he and Miles Ponson started writing their own software.

It was amazing seeing him sort through all the myriad issues in balancing analog clocks, the decaseconds refresh rates, and a lap counter. I’m sure a few swimming and track coaches will like this utility if they have an iPhone. Throughout his development cycle, the process has been fun to watch. He certainly loves Objective C.

Just as an aside, it has also been gratifying to know that he finally found major uses for the database, like set operators, inline views, and stored procedures. He once noted they weren’t too useful. 😉