MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for May, 2009

Not quite an invalid function

with 3 comments

An interesting thing happened today, as I was explaining how you call functions with embedded DML statements. The students were stunned at seeing an ORA-06576 error for a function that they knew existed. It’s one of those imperfect error messages …

Basically, they wrote a wrapper function to a parallel enabled function, and then they tried to call it into a session level bind variable, like this:

SQL> VARIABLE verified NUMBER
SQL> CALL update_contact INTO :verified;

It failed with the following message:

CALL update_contact INTO :verified
     *
ERROR at line 1:
ORA-06576: NOT a valid FUNCTION OR PROCEDURE name

They were stunned but I was mum. I suggested that they test the call in an anonymous block program. Here’s the PL/SQL call example:

SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> BEGIN
  2    IF update_contact = 1 THEN
  3      DBMS_OUTPUT.put_line('Success!');
  4    ELSE
  5      DBMS_OUTPUT.put_line('Failure!');
  6    END IF;
  7  END;
  8  /

It returns

Success!

I removed the puzzled look by explaining that while you don’t need to provide the open and close parentheses inside PL/SQL, you do generally require them in the context of a CALL statement. They’re not required for stored functions in SQL statements, but they’re required for stored instantiable object types.

Here’s the correct way call the program:

SQL> CALL update_contact() INTO :verified;
SQL> SELECT DECODE(:verified,1,'Success!','Failure!') AS answer FROM dual;

It prints this to console:

ANSWER
--------
Success!

The natural question is why don’t you just run the function as part of a query. It’s a great question because it lets me demonstrate another principle. The principle that you can’t can’t perform a DML in a query, which is abstract until you see it up front and personal.

SQL> SELECT update_contact() FROM dual;

this query raises the following exception:

SELECT update_contact() FROM dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "PLSQL.UPDATE_CONTACT", line 4

Setup Code Supplement

You can find the base code for this example here. It comes from the Oracle Database 11g PL/SQL Programming book. After you run the create_store.sql script, you’ll need to run the following:

-- Add a null allowed column for derived data.
ALTER TABLE contact ADD (full_name VARCHAR2(44));
 
-- Define a function concatenate strings.
CREATE OR REPLACE FUNCTION MERGE
( last_name     VARCHAR2
, first_name    VARCHAR2
, middle_initial VARCHAR2 )
RETURN VARCHAR2 PARALLEL_ENABLE IS
BEGIN
  RETURN last_name ||', '||first_name||' '||middle_initial;
END;
/
 
-- Define a wrapper function around the merge function.
CREATE OR REPLACE FUNCTION update_contact RETURN NUMBER IS
  ret_val NUMBER := 0; -- The default return value to false or zero.
BEGIN
  UPDATE contact c1
  SET    c1.full_name = (SELECT MERGE(c2.last_name
                                     ,c2.first_name
                                     ,c2.middle_initial)
                         FROM   contact c2
                         WHERE  c1.rowid = c2.rowid);
  IF SQL%ROWCOUNT > 0 THEN
    ret_val := 1; -- This is only reached when 1 or more rows are updated.
  END IF;
  RETURN ret_val;
END update_contact;
/

Written by maclochlainn

May 27th, 2009 at 1:52 pm

Posted in Oracle,pl/sql,sql

MySQL Merge gone Awry

with 13 comments

Sometimes it gets tiresome when people take shots at Oracle, MySQL, SQL Server 2008, or PostgreSQL. When I went to the MySQL for Database Administrators, the instructor mentioned a number of times how many Oracle people he’d get in his class. It was said almost as if all the Oracle customers were migrating to MySQL, which I don’t think is the case. If I’m wrong just post a comment.

After writing SQL since 1985 (IBM SQL/DS), I’ve come to appreciate a number of the features in the Oracle database. This probably makes sense because I worked there for over eight years, and probably would still be there if I hadn’t left to teach at University. I’m constantly amazed as I explore and look for points of commonality across SQL dialects and PL/SQL dialects. At least, I think it’s fair to call T-SQL stored programs and MySQL stored programs PL/SQL dialects.

This blog post is about the MERGE statement and it’s close MySQL cousin, the ON DUPLICATE KEY UPDATE statement (that only works with single row INSERT statements). I’ve recently added a post demonstrating the REPLACE INTO command syntax. You may also be interested in a newer blog post about importing external data inside a MySQL procedure with cursor loops. Upfront, I have to vote for Oracle’s MERGE statement because it’s more complete as an implementation. By the way, T-SQL supports the same MERGE syntax. They’re so exact it wasn’t worth taking the space to show the syntax for the SQL Server 2008 Express product.

Oracle’s Merge Statement

The downside of Oracle’s MERGE statement is that it takes a LOT of typing. The upside from my perspective is that it enforces that you must use the primary key column. The same is not true with the MySQL syntax.

Here’s a quick example that you can cut and paste into your environment for Oracle Database 11g or remove the Oracle specific FROM dual and it’ll run in SQL Server. You can also see how to leverage joins and imports with the MERGE statement in this more recent blog post.

-- Conditionally drop the table and sequence.
BEGIN
  FOR i IN (SELECT NULL
            FROM   user_tables
            WHERE  TABLE_NAME = 'SYSTEM_USER') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE system_user CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT NULL
            FROM   user_sequences
            WHERE  sequence_name = 'SYSTEM_USER_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE system_user_s1';
  END LOOP;
END;
/
 
-- Create the table.
CREATE TABLE system_user
( system_user_id        NUMBER        CONSTRAINT pk_su   PRIMARY KEY
, system_user_name      VARCHAR2(20)  CONSTRAINT nn_su_1 NOT NULL
, system_user_group_id  NUMBER        CONSTRAINT nn_su_2 NOT NULL
, system_user_type      NUMBER        CONSTRAINT nn_su_3 NOT NULL
, first_name            VARCHAR2(20)
, middle_name           VARCHAR2(10)
, last_name             VARCHAR2(20)
, created_by            NUMBER        CONSTRAINT nn_su_4 NOT NULL
, creation_date         DATE          CONSTRAINT nn_su_5 NOT NULL
, last_updated_by       NUMBER        CONSTRAINT nn_su_6 NOT NULL
, last_update_date      DATE          CONSTRAINT nn_su_7 NOT NULL);
 
-- Create the sequence with a default start value of 1.
CREATE SEQUENCE system_user_s1;
 
-- Insert new row.
INSERT INTO system_user
VALUES
( system_user_s1.NEXTVAL
, 'SYSADMIN'
, 1
, 1
, NULL
, NULL
, NULL
, 1
, SYSDATE - 1
, 1
, SYSDATE - 1);
 
-- Insert new or merge into existing row.
MERGE INTO system_user target
USING (SELECT   1 AS system_user_id
       ,       'SYSADMIN' AS system_user_name
       ,        1 AS system_user_group_id
       ,        1 AS system_user_type
       ,       'Samuel' AS first_name
       ,        'the' AS middle_name
       ,       'Lamanite' AS last_name
       ,        1 AS created_by
       ,        SYSDATE AS creation_date
       ,        1 AS last_updated_by
       ,        SYSDATE AS last_update_date
       FROM     dual) SOURCE
ON (target.system_user_id = SOURCE.system_user_id)
WHEN MATCHED THEN
  UPDATE SET first_name = 'Samuel'
  ,          middle_name = 'the'
  ,          last_name = 'Lamanite'
  ,          last_updated_by = 1
  ,          last_update_date = SYSDATE
WHEN NOT MATCHED THEN
  INSERT
  ( target.system_user_id
  , target.system_user_name
  , target.system_user_group_id
  , target.system_user_type
  , target.first_name
  , target.middle_name
  , target.last_name
  , target.created_by
  , target.creation_date
  , target.last_updated_by
  , target.last_update_date )  
  VALUES
  ( SOURCE.system_user_id
  , SOURCE.system_user_name
  , SOURCE.system_user_group_id
  , SOURCE.system_user_type
  , SOURCE.first_name
  , SOURCE.middle_name
  , SOURCE.last_name
  , SOURCE.created_by
  , SOURCE.creation_date
  , SOURCE.last_updated_by
  , SOURCE.last_update_date );

MySQL On Duplicate Key Update Statement

The downside of MySQL’s ON DUPLICATE KEY UPDATE statement is that it takes it lets you use an override signature on the INSERT that then creates a new row when it shouldn’t. I logged a bug when I discovered that behavior earlier today but they don’t quite agree that it’s a bug. The upside is that the typing is MUCH shorter provided you remember to use the auto increment key column in the SELECT clause.

Here’s a quick example of what not to do! The next one shows you what you should do. You can see how to tie together MySQL subroutines with an INSERT ... ON DUPLICATE KEY statement in this more recent blog entry.

-- Conditionally drop the table.
DROP TABLE IF EXISTS system_user;
 
-- Create the table.
CREATE TABLE system_user
( system_user_id              INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT
, system_user_name            CHAR(20)      NOT NULL
, system_user_group_id        INT           NOT NULL
, system_user_type            INT           NOT NULL
, first_name                  CHAR(20)
, middle_name                 CHAR(10)
, last_name                   CHAR(20)
, created_by                  INT           NOT NULL
, creation_date               DATE          NOT NULL
, last_updated_by             INT           NOT NULL
, last_update_date            DATE          NOT NULL);
 
-- Insert new row.
INSERT INTO system_user
( system_user_name
, system_user_group_id
, system_user_type
, created_by
, creation_date
, last_updated_by
, last_update_date )
VALUES
('SYSADMIN'
, 1
, 1
, 1
, DATE_SUB(NOW(),INTERVAL 1 DAY)
, 1
, DATE_SUB(NOW(),INTERVAL 1 DAY));
 
-- Insert new or merge into existing row.
INSERT INTO system_user
( system_user_name
, system_user_group_id
, system_user_type
, first_name
, middle_name
, last_name
, created_by
, creation_date
, last_updated_by
, last_update_date )
VALUES
('SYSADMIN'
, 1
, 1
,'Samuel'
,'the'
,'Lamanite'
, 1
, NOW()
, 1
, NOW())
ON DUPLICATE KEY 
UPDATE first_name = 'Samuel'
,      middle_name = 'the'
,      last_name = 'Lamanite'
,      last_updated_by = 1
,      last_update_date = UTC_DATE();

This script ends up inserting two rows when only one should be present. Why did that happen? Great question! You can fix this by adding a unique key defined for the columns that make up the natural key for the SYSTEM_USER table. As noted by a comment below, the unique key must only include columns that are NOT NULL constrained. Here’s the results without such a unique key:

mysql> SELECT * FROM system_user\G
*************************** 1. ROW ***************************
      system_user_id: 1
    system_user_name: SYSADMIN
system_user_group_id: 1
    system_user_type: 1
          first_name: NULL
         middle_name: NULL
           last_name: NULL
          created_by: 1
       creation_date: 2009-05-24
     last_updated_by: 1
    last_update_date: 2009-05-24
*************************** 2. ROW ***************************
      system_user_id: 2
    system_user_name: SYSADMIN
system_user_group_id: 1
    system_user_type: 1
          first_name: Samuel
         middle_name: NULL
           last_name: Lamanite
          created_by: 1
       creation_date: 2009-05-25
     last_updated_by: 1
    last_update_date: 2009-05-25
2 ROWS IN SET (0.02 sec)

The correct way to do this in MySQL is shown in the next example.

-- Conditionally drop table.
DROP TABLE IF EXISTS system_user;
 
-- Create table.
CREATE TABLE system_user
( system_user_id              INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT
, system_user_name            CHAR(20)      NOT NULL
, system_user_group_id        INT           NOT NULL
, system_user_type            INT           NOT NULL
, first_name                  CHAR(20)
, middle_name                 CHAR(10)
, last_name                   CHAR(20)
, created_by                  INT           NOT NULL
, creation_date               DATE          NOT NULL
, last_updated_by             INT           NOT NULL
, last_update_date            DATE          NOT NULL);
 
-- Insert new row.
INSERT INTO system_user
( system_user_name
, system_user_group_id
, system_user_type
, created_by
, creation_date
, last_updated_by
, last_update_date )
VALUES
('SYSADMIN'
, 1
, 1
, 1
, DATE_SUB(UTC_DATE(),INTERVAL 1 DAY)
, 1
, DATE_SUB(UTC_DATE(),INTERVAL 1 DAY));
 
-- Insert new or merge into existing row.
INSERT INTO system_user
VALUES
( 1
,'SYSADMIN'
, 1
, 1
,'Samuel'
,'the'
,'Lamanite'
, 1
, NOW()
, 1
, NOW())
ON DUPLICATE KEY
UPDATE first_name = 'Samuel'
,      middle_name = 'the'
,      last_name = 'Lamanite'
,      last_updated_by = 1
,      last_update_date = NOW();

This ensures that the auto increment column values are matched. It returns what you’d expect, a single row inserted into or updated in the table.

mysql> SELECT * FROM system_user\G
*************************** 1. ROW ***************************
      system_user_id: 1
    system_user_name: SYSADMIN
system_user_group_id: 1
    system_user_type: 1
          first_name: Samuel
         middle_name: NULL
           last_name: Lamanite
          created_by: 1
       creation_date: 2009-05-24
     last_updated_by: 1
    last_update_date: 2009-05-25
1 ROW IN SET (0.00 sec)

I hope this saves somebody from an insertion anomaly.

Written by maclochlainn

May 25th, 2009 at 6:03 pm

Posted in MySQL,Oracle,sql

MySQL Installation and More

with 4 comments

Installing MySQL is pretty straightforward, especially on a Windows operating system. However, recently I was surprised to find out that there were folks who didn’t know you could install multiple version on their Windows development machine. Here’s are the step-by-step instructions to do so. At the end of the screen shots you’ll find out how to sign-on and create your first database and user account. Naturally, it’s all at the command line because they work on any platform the same way.

1. After you download an Microsoft Software Installation (MSI) file or the zip file, you launch the installer by clicking the Next button:

mysqlinstall1

2. If you want to install only one copy of the software at any given time, you can accept the default Typical radio button choice. This way, you can simply uninstall the software before you install a new version. When you want to install multiple versions on a single machine, you should check the Custom radio button. This choice lets you maintain multiple versions on your single development machine and check changes between releases. After choosing a direction, you click the Next button to continue.

mysqlinstall2

3. The first thing you should do is upgrade the Developer Components. You do that by clicking on the down arrow to the right of the X

, which yields the context menu below. Place your mouse over the top most This feature will be installed on local hard drive. After that you can begin to customize the installation.

mysqlinstall2a

4. This is a step that you'll see when you opt to do a Custom installation. It is important that you click the Change button and change the default directory for the installation. When you click the Change button it will launch a File Finder window.

mysqlinstall3

5. You can put the files anywhere you choose but I'd recommend that you opt to use something like this directory path:

C:\Program Files\MySQL 5.1\MySQL Server 5.1\

As you'll see in the screen shot mine is slightly different because my virtual Windows XP machine is a 64-bit operating system. That's why you'll see the (x86) as part of the Windows Program Files directory path. It indicates that MySQL will be installed in the 32-bit executable folder. You click the OK button to accept the chosen directory and return control back to the MySQL installation program.

mysqlinstall4

6. The choice of your file directory is now displayed below the caption Install to:, and you click the Next button to continue.

mysqlinstall5

7. Before you click that Next button, you have one more step if you plan on having multiple instances installed on your machine. You need to change the default storage location for data files. You do that by clicking on the MySQL Server Datafiles. When you click on that, you'll see the following default file location below the caption Install to:

C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\

You click the Change button to enter another location for the database files.

mysqlinstall5a

8. After you click on Change button, you'll see the File Finder. You can then enter the directory that you'd prefer for the installation. My suggestion is the following:

C:\Data\MySQL 5.1\MySQL Server 5.1\

Click on the OK button to complete the selection of a customized database file location.

mysqlinstall5b

9. You should now see the wizard screen again. Choose the Next button to cotinue.

mysqlinstall6

10. This is the screen where you see what you're about to do, before you do it. It should look like this if you're following my instructions or like whatever you've opted to use in your installation. Click the Install button to begin the installation.

mysqlinstall6a

11. The installation starts and you'll see a dialog with a creeping status bar. It should take about 2-3 minutes on many computers but it may seem longer.

mysqlinstall7

After the status bar, you'll see the following marketing screen. Click the Next button to continue.

mysqlinstall8

12. The second marketing dialog then shows up, just click the Next button to continue.

mysqlinstall9

13. The installation is now complete. You should ensure you've checked the Configure the MySQL Server now box. Click the Finish button to end the installation of the file system and begin the configuration of the MySQL server.

mysqlinstall10

14. The next dialog is the configuration welcome. Click the Next button to continue.

mysqlinstall11

15. This is an important dialog because you must choose between a Standard or Detailed configuration. You should check the Detailed radio button When you plan to install more than one MySQL version on your computer because you'll need to control the listening port for the server. A default MySQL Sever installation sets the listening port at 3306. Typically, you'll want the main version to run on port 3306 and set the other versions to 3307, 3308, et cetera.

mysqlinstall12

16. This dialog lets you choose how to configure performance characteristics of your MySQL Server. As you become more skilled at MySQL, you'll find that these are configurable in a file, but for now using the interface is probably your best bet. The choice you make here sets how much memory MySQL will take while running. Typically, you should choose Developer Machine unless this is a server installation or you have a log of extra memorty to dedicate to MySQL Server (that's the mysqld process).

mysqlinstall14

17. This dialog lets you configure the Inno DB files location. You should make sure that you ensure they'll be different if you're installing multiple releases. You click the drop down arrow for the Installation Path box, choose one and manually edit it, like shown for MySQL 5.1.

mysqlinstallation16

18. This dialog governs configuration parameters about concurrency. If this is for a developer machine click the Decision Support (DSS)/OLAP radio button. It generally gives you more than enough connections for development activities.

mysqlinstall15

19. This is the dialog where you enable a firewall exception and set a port. Since MySQL 5.1 is my main testing environment, port 3306 (the default) is selected. You also want to enable both check boxes.

mysqlinstall17

20. This dialog lets you choose a multiple byte unicode character set or the standard latin1. For those coming from an Oracle world, that's WEISO8859-1 character set.

mysqlinstall18

21. This dialog names your Microsoft Windows Service for MySQL. It is important that you change this to represent the release. The default for every release is MySQL. You should also uncheck the Include Bin Directory in Windows PATH, and set that manually in a command shell file (e.g., a mysql51.bat file). You can run the batch file when you want to work in this environment. If you allow this to append to the system path, make sure you know which one you're using when working with MySQL.

mysqlinstall19

22. This dialog sets the root password. My advise is that you leave unchecked the Create An Anonymous Account and the Enable root access from remote machines boxes. Make sure you use a password that you can remember, even if it is your personal machine.

mysqlinstall20

23. You're almost done. The next dialog confirms you want to configure MySQL. Click the Execute button to configure the server.

mysqlinstall21

After the configuration, you'll see the following dialog. It means you're done.

mysqlinstall22

24. If you encounter a failure starting the service, it's not infrequent in any number of MSI files. I wrote another post on how to work around it here. The following is the error screen that may occur.

mysqlinstall23

25. Here are the steps to create your own database and user. It also shows you how to grant privileges to the new user on only their database.

(a). You connect as the superuser by calling the client-side mysql program, like the following (assumes you set the password for the root user to cangetin). You don't need to provide the port number if it is the default 3306 but will need to provide it for any other port that the mysqld service is listening on.

C:\Data> mysql -uroot -pcangetin -P3306

You can confirm your the root user by running the following query:

mysql> SELECT CURRENT_USER();

(b). You create a database with the following syntax:

mysql> CREATE DATABASE sampledb;

(c). You create a user with the trivial password of the user's name, which is a really bad example when you're doing anything but writing documentation. The following creates the user and then grants the user all privileges on their database.

mysql> CREATE USER 'myuser' IDENTIFIED BY 'myuser';
mysql> GRANT ALL ON sampledb.* TO 'myuser'@'localhost' IDENTIFIED BY 'myuser';

(d). You sign-off as the root user by typing quit, like

mysql> QUIT;

(e). You sign-on as the restricted myuser with the following syntax:

C:\Data> mysql -umyuser -pmyuser -P3306

You can confirm your the restricted user by running the following query:

mysql> SELECT CURRENT_USER();

(f). You can't do much except explore generic metadata at this point because you must select a database. You select the sampledb database by doing the following:

mysql> USE sampledb;

You could now create a sample table that uses automatic numbering, insert values, and query the contents by using the following syntax.

mysql> CREATE TABLE sample ( sample_id   INT PRIMARY KEY AUTO_INCREMENT
                           , sample_text VARCHAR(20));
mysql> INSERT INTO sample (sample_text) VALUES ('One'),('Two'),('Three'),('Four'),('Five');
mysql> SELECT * FROM sample;
+-----------+-------------+
| sample_id | sample_text |
+-----------+-------------+
|         1 | One         |
|         2 | Two         |
|         3 | Three       |
|         4 | Four        |
|         5 | Five        |
+-----------+-------------+

Alternatively, you could place those three commands (minus the "mysql>" on each line) in a file and run the file from the command line. Just so there's no misunderstanding, the file would look like this:

-- Open the database.
USE sampledb;
 
-- Split standard out, and write a log file to the relative directory.
TEE C:/DATA/sample.txt
 
-- Run code.
DROP TABLE IF EXISTS sample;
CREATE TABLE sample ( sample_id   INT PRIMARY KEY AUTO_INCREMENT
                    , sample_text VARCHAR(20));
INSERT INTO sample (sample_text) VALUES ('One'),('Two'),('Three'),('Four'),('Five');
SELECT * FROM sample;
 
-- Turn off tee, close file to automatic appending.
NOTEE

If you named the file sample.sql and it is found in the C:\Data directory, you'd run it like this:

mysql> SOURCE C:/DATA/sample.SQL

This completes the basic steps, I'll put a link here for a cross platform comparative tutorial on client-side command line interfaces, like Oracle's sqlplus, MySQL's mysql, and Microsoft SQL Server 2008's sqlcmd.

Written by maclochlainn

May 24th, 2009 at 10:29 pm

Posted in Microsoft XP,MySQL

SQL Automated Numbers

with 4 comments

I’ve begun putting together an online database tutorial and expanded this entry and added horizontal scrolling to it. You can find the improved version of the blog post as blog page here.

Surrogate keys are interesting structures in databases. They’re essential if you want to make sure you optimize your design. They’re also very useful when you want to capture the automatic numbering value for a prior INSERT statement and reuse the automatic numbering value as the foreign key value in a subsequent statement. It was interesting to see how they’re implemented differently across Oracle, MySQL, and SQL Server while providing the same utility.

Below is a synopsis of how you implement these in Oracle, MySQL, and SQL Server.

Oracle

The first thing to qualify is that Oracle is generally always in a transactional mode. That means you don’t need to do anything special to set this example up.

Oracle doesn’t support automated numbering in tables prior to Oracle 12c. Oracle 12c introduces identity columns, and the mechanics change. However, you can use sequences to mimic automated numbering prior to Oracle 12c and without identity columns in Oracle 12c. A sequence is a structure in the database that holds a current value, increments by a fixed value – typically 1. Sequences are available in SQL and PL/SQL scopes through two pseudo columns. The pseudo columns are .nextval and .currval (note the two r’s because it’s not a stray dog).

The sequence_name.nextval call in any session places the next number from the sequence into your Personal Global Area (PGA), which is a memory context. After you’ve called the sequence into memory, you can access it again by using sequence_name.currval. The sequence only changes when you call it again with the .nextval pseudo column.

-- Conditionally drop data sturctures - tables and sequences.
BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
			WHERE  TABLE_NAME IN ('ONE','TWO')) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.TABLE_NAME||' CASCADE CONSTRAINT';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
			WHERE  sequence_name IN ('ONE_S1','TWO_S1')) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/
 
-- Create base table and sequence.
CREATE TABLE one
( one_id   INT         NOT NULL CONSTRAINT pk_one PRIMARY KEY
, one_text VARCHAR(10) NOT NULL );
 
CREATE SEQUENCE one_s1;
 
-- Create dependent table and sequence.
CREATE TABLE two
( two_id   INT         NOT NULL CONSTRAINT pk_two PRIMARY KEY
, one_id   INT         NOT NULL
, two_text VARCHAR(10) NOT NULL );
 
CREATE SEQUENCE two_s1;
 
-- Insert rows into the tables with sequence values.
INSERT INTO one VALUES (one_s1.NEXTVAL,'One!');
INSERT INTO one VALUES (one_s1.NEXTVAL,'Two!');
INSERT INTO two VALUES (two_s1.NEXTVAL, one_s1.currval,'Other Two!');
 
-- Display the values inserted with sequences.
SELECT   o.one_id
,        o.one_text
,        t.two_id
,        t.two_text
FROM     one o JOIN two t ON o.one_id = t.one_id;

If you mimic automatic numbering with database triggers, you may not have access to the .currval value for the second INSERT statement. This occurs when you provide a NULL value expecting the trigger to manage .NEXTVAL call for you.

Transactions require that you keep the primary key value for the first table in a locally scoped variable for reuse. Then, you can pass it to the next INSERT statement. You do that with the .CURRVAL value.

You can make a potentially erroneous assumption that you’re the only user updating the table. Operating under that assumption, you can query the highest sequence number from the table before an insert, add one to it, and then attempt the INSERT statement. In a multi-user system, it’s possible that somebody beats you to the finish line with their INSERT statement. Your insert would then have a duplicate surrogate key value for the one_id column, and fail on an ORA-00001 error for a uniqueness violation on a primary key column.

A database trigger can help you avoid a race condition. The trigger would ensure sequence values are unique but it may also introduce problems. A common Oracle trigger with a pseudo automatic numbering paradigm is represented by the following trigger (found in APEX generated code).

CREATE OR REPLACE TRIGGER one_t1 
BEFORE INSERT ON one
FOR EACH ROW
BEGIN
  :NEW.one_id := one_s1.NEXTVAL;
END;
/

Caution is required on this type of automated sequence trigger. There are two problems with this type of trigger.

One scenario is where you include a call to sequence_name.NEXTVAL in your INSERT statement. It then increments the sequence, and attempts to insert the value whereupon the trigger fires and repeats the behavior. Effectively, this type of logic creates a sequence that increments by one when you submit a null value in the values clause and by two when you submit a sequence_name.NEXTVAL value.

Another scenario occurs when you attempt a bulk INSERT operation on the table. The sequence call and substitution occurs on each row of the sequence.

You face another problem when you rewrite the trigger to only fire when a surrogate primary key isn’t provided, like this:

CREATE OR REPLACE TRIGGER one_t1 
BEFORE INSERT ON one
FOR EACH ROW
WHEN (NEW.one_id IS NULL)  -- Asynchronous with bulk insert operations when a value is provided by the bulk operation to the surrogate key column.
BEGIN
  :NEW.one_id := one_s1.NEXTVAL;
END;
/

This trigger design causes a problem only with bulk INSERT statements. Effectively, the sequence remains unaltered when you provide surrogate key values as part of inserting an array of values. The next non-bulk INSERT statement would then grab the .NEXTVAL value, attempt to use it, and raise a unique constraint violation because the bulk operation probably already used the value from the sequence.

The fix to bulk operations requires that you lock the table, disable a trigger like this, and get the .NEXTVAL value. Then, you assign the .NEXTVAL value to two local variables. One of these remains unchanged while the other increments as you populate the array for the bulk insert operation. After assigning the result from the .NEXTVAL, you drop the sequence and find the highest key value for the bulk insertion operation, add one to the highest key value, and store it in another locally stored variable. You perform the bulk insert operation and then recreate the sequence with a value one greater than the highest value in the table, which should already be in a locally scored variable. Don’t forget that you’d locked the table, so unlock it now.

You should note that database triggers run in a subshell with access only to the immediate shell that fired them. Therefore, you can’t set a bind variable in a SQL*Plus session and subsequently reference it inside the trigger body because it doesn’t have access to the variable.

MySQL

MySQL supports automatic numbering but not a default transactional mode like Oracle. You need to disable auto commit and start a transaction. You also need to assign the last automatic numbering value to a variable before using it in a subsequent INSERT statement. You must also provide an overriding list of mandatory columns when you opt to exclude the automated numbering column value. The one thing that we should all appreciate about MySQL is their desire to stay close to and comply with ANSI standards.

-- Conditionally drop the tables.
DROP TABLE IF EXISTS one;
DROP TABLE IF EXISTS two;
 
-- Create the tables with a surrogate key that automatically increments.
CREATE TABLE one ( one_id   INT PRIMARY KEY AUTO_INCREMENT
                 , one_text VARCHAR(20));
 
CREATE TABLE two ( two_id INT PRIMARY KEY AUTO_INCREMENT
                 , one_id INT
                 , two_text VARCHAR(20));
 
-- Start transaction cycle.
START TRANSACTION;
 
-- Insert first row, transfer auto increment to memory.
INSERT INTO one (one_text) VALUES ('One');
 
-- Assign last auto increment to local scope variable, the = works too.
SET @one_fk := last_insert_id();
 
-- Insert second row with auto increment and local scope variable.
INSERT INTO b (one_id, two_text) VALUES (@one_fk,'Two');
 
COMMIT;
 
-- Display the values inserted with auto incremented values.
SELECT   o.one_id
,        o.one_text
,        t.two_id
,        t.two_text
FROM     one o JOIN two t ON o.one_id = t.one_id;

SQL Server

SQL Server supports automatic numbering but they call it the identity value. There are two ways to use it but the one I’m showing is for SQL Server 2005 or newer. You can replace the older @@identity for the SCOPE_IDENTITY() function call but Microsoft has already removed first level support from SQL Server 2000. While they’ve not said @@identity is deprecated, it sure appears that’s possible in a future release.

USE student;
 
BEGIN TRAN;
 
-- Conditionally drop tables when they exist.
IF OBJECT_ID('dbo.one','U') IS NOT NULL DROP TABLE dbo.one;
IF OBJECT_ID('dbo.two','U') IS NOT NULL DROP TABLE dbo.two;
 
-- Create auto incrementing tables.
CREATE TABLE one
( one_id   INT         NOT NULL IDENTITY(1,1) CONSTRAINT pk_one PRIMARY KEY
, one_text VARCHAR(10) NOT NULL );
 
CREATE TABLE two
( two_id   INT         NOT NULL IDENTITY(1,1) CONSTRAINT pk_two PRIMARY KEY
, one_id   INT         NOT NULL
, two_text VARCHAR(10) NOT NULL );
 
-- Insert the values, and magically no override signature required.
INSERT INTO one VALUES ('One!');
INSERT INTO one VALUES ('Two!');
INSERT INTO two VALUES (SCOPE_IDENTITY(),'Other Two!');
 
-- Query the results.
SELECT   o.one_id
,        o.one_text
,        t.two_id
,        t.two_text
FROM     one o JOIN two t ON o.one_id = t.one_id;
 
COMMIT TRAN;

You should note that T-SQL doesn’t require an override signature when you use an automatic numbering column. This is different, isn’t it?

While the prior example works with two tables, it doesn’t scale to a series of tables. You should consider the following assignment pattern when you’ll have multiple last identity values in a single transaction scope.

DECLARE @one_pk AS INT;
SET @one_pk = SCOPE_IDENTITY();

As mentioned, this style is important when you’ve got a series of primary and foreign keys to map in the scope of a single transaction. Also, I’d suggest that you put all the declarations at the beginning of the transaction’s scope.

As always, I hope this helps some folks.

Written by maclochlainn

May 16th, 2009 at 11:04 pm

Fix SQL Server 2008 Client

with 63 comments

I finally got back to my Microsoft SQL Server 2008 Express installation for a bit of comparison documentation in the sqlcmd.exe (the client tool peer to sqlplus and mysql). After all the energy to install it, I found it didn’t work.

Update for SQL Server 2012 – it’s still broken the same way and this fix works.

When I tried to connect with:

C:\>sqlcmd.exe

It raised the following error:

HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific
error has occurred while establishing a connection to SQL Server. Server is not found or not
accessible. Check if instance name is correct and if SQL Server is configured to allow remote
connections. For more information see SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

A bit of poking around yielded an answer on Chris Rasmussen’s blog and a bit more about .NET pipes on Jesse Johnston’s blog. It appears Microsoft SQL Server 2008 Express edition ships and installs with their client tool effectively shutoff.

The client executable uses a pipe to communicate to the server. You need to change the pipe from this default configuration:

\\.\pipe\MSSQL$SQLEXPRESS\sql\query

To this working version

\\.\pipe\sql\query

Then, you need to enable it and restart the service. Here are the step-by-step instructions, so you don’t have to poke around.

You’ll need to launch the SQL Server Configuration Manager to make these changes. You’ll find it by navigating to Start and then open the Microsoft SQL Server 2008 folder. You’ll then select Configuration Tools and launch SQL Server Configuration Manager, as shown in the screen shot.

startmenu

Launching that provides you the following screen shot:

netpipeconfiguration

Open the SQL Server Network Configuration detail and choose Protocols for SQLEXPRESS. Double click on the Named Pipes protocol name.

netpipedefault

You then enable the protocol and change the Pipe Name as noted above and shown in the screen shot below:

netpipefixed

Click the Apply button, and the only remaining step requires you to restart the service. The easiest way (thanks to Griffth) is to click on SQL Server Services in the left dialog, then right click the SQL Server (SQLEXPRESS) service and choose Restart.

sqlrestarteasy1

An alternative would be to open a command line window (cmd.exe) and launch the services console with the following command:

C:\> services.msc

Click on SQL Server (SQLEXPRESS), then click the Restart the services link in the middle column, as shown.

sqlserverservice

Now you should be able to launch the SQL Server 2008 Express command line console. You can get some basic help with the tool by doing the following:

C:\Data>sqlcmd -?
Microsoft (R) SQL Server Command Line Tool
Version 10.0.1600.22 NT INTEL X86
Copyright (c) Microsoft Corporation.  All rights reserved.
 
usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, enviroment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

After you’ve configured this, you can schedule routine operations, like the daily submission process in this external post.

Written by maclochlainn

May 16th, 2009 at 12:26 am

Posted in sql,SQL Server 2008

MySQL Explain Plan

without comments

I finally got the magic trick to find the explain plan for a query in MySQL, but chagrined to find out that you can’t explain the cost or execution of INSERT, UPDATE or DELETE statements. This blog shows you how to get the execution and execution plan for a query and the error when you try to EXPLAIN DML (Data Manipulation Language) statements.

You can see the selection type, possible indexes, chosen index, et cetera:

EXPLAIN query;

You can see the selection type, possible indexes, chosen index, et cetera plus the query execution plan with the magic EXTENDED key word:

EXPLAIN EXTENDED query;

In some cases, you may need to type SHOW ERRORS to see the execution plan. There’s no way to format it natively, like Oracle, but there may be a tool out there.

If you try to explain a DML statement, you’ll get an error message like the following:

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 'UPDATE city SET District = 'Funny'' at line 1

The last rule of thumb on query execution is that you should avoid subqueries because they degrade processing speed.

Written by maclochlainn

May 8th, 2009 at 11:45 am

Posted in MySQL,sql

MySQL MSI Service Fails

with 4 comments

While installing the MySQL 6.0 Alpha release, I encountered a failure running the configuration component. It shows the following dialog, which hangs until you cancel it. By the way, I’ve encountered this on other MySQL 5.0 and 5.1 installs from time to time.

mysql6servicefailure

Don’t uninstall and reinstall because nothing will change. The only problem appears to be setting the root password. This show you how to verify it and fix the missing configuration step. While the service says it failed, it actually started. You can check that by launching services.msc from Start and Run.

You can verify the problem by attempting to connect to the MySQL server. My server is setup on localhost with port 3308 because there are multiple MySQL servers running on my virtual machine. A typically connection would look like this if your password was cangetin (the old Solaris training password):

C:\>mysql -uroot -pcangetin -P3308

If you get the following error message, it’s most likely a missing root password.

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Since my machine is running multiple MySQL servers and it’s my preference to associate their execution to their binaries, the paths to the installations aren’t loaded automatically on installation. A quick caution, my path statements are from the Windows XP 64-bit installation and they’ll differ from a 32-bit installation path. Specifically, the executable programs are in C:\Program Files (x86) directory not C:\Program Files. You can set the path like this:

C:\>set PATH=C:\Program Files (x86)\MySQL 6.0\MySQL Server 6.0\bin;%PATH%

To verify and fix the problem requires you login without a password, connect to the mysql database, and query the user table. All those steps follow below, unless you’re on Microsoft Vista. If you’re running Microsoft Vista follow these instructions.

C:\>mysql -uroot -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.0.10-alpha-community MySQL Community Server (GPL)
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> USE mysql;
Database changed
 
mysql> SELECT host, user, passowrd
    -> FROM user WHERE user='root' AND host='localhost'\G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
              Password:
1 row in set (0.00 sec)

You fix this problem by running the following grant of privileges to the root user:

mysql> GRANT ALL ON *.* TO 'root'@'localhost'
    -> IDENTIFIED BY 'cangetin' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

I learned this technique by attending the MySQL for Database Administrator’s course. I hope it solves a mystery for somebody along the way. I also hope that Oracle Education maintains the excellent folks that Sun Microsystems acquired when they snagged MySQL.

Written by maclochlainn

May 7th, 2009 at 11:40 pm

MySQL export to CSV

with 6 comments

While working through export techniques in MySQL (5.1.34-community), I discovered some tricks and techniques with MySQL exports to a CSV file.

Here’s a standard export statement to a CSV file format:

mysql> SELECT *
    -> INTO OUTFILE 'C:/Data/City.csv'
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> ESCAPED BY '\\'
    -> LINES TERMINATED BY '\r\n'
    -> FROM City;
Query OK, 4079 ROWS affected (0.02 sec)

On Microsoft Windows, when you attempt to export it a second time, you’ll get an error unless you’ve previously deleted the physical file. You’ll also need the \r on the Windows platform but not the Linux platform.

mysql> SELECT *
    -> INTO OUTFILE 'C:/Data/City.csv'
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> ESCAPED BY '\\'
    -> LINES TERMINATED BY '\r\n'
    -> FROM City;
ERROR 1086 (HY000): File 'c:/Data/City.csv' already EXISTS

While reviewing Alan Beaulieu’s Learning SQL, 2nd Edition, I noticed he’s got a small example in his Appendix B. He’s using the back-quoted backslash approach to directories in Windows. You can use it, but I prefer the one shown in my examples. Here’s the alternative syntax for the outbound file line:

    -> INTO OUTFILE 'C:\\Data\\City.csv'

When you want to use the CASE statement, you need to use a derived (MySQL terminology). It appears that you can’t include a CASE statement in the SELECT clause when exporting the contents to an OUTFILE. Also, for reference, MySQL doesn’t support the WITH clause.

SELECT *
INTO OUTFILE 'c:/Data/City4.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
FROM (SELECT ID
      ,      CASE
               WHEN Name IS NULL THEN '' ELSE Name
             END AS Name
      ,      CASE
               WHEN CountryCode IS NULL THEN '' ELSE CountryCode
             END AS CountryCode
      ,      CASE
               WHEN District IS NULL THEN '' ELSE District
             END AS District
      ,      CASE
               WHEN Population IS NULL THEN '' ELSE Population
             END AS Population
      FROM City) Subquery;

Hope this helps somebody.

Written by maclochlainn

May 7th, 2009 at 2:23 pm

Posted in Microsoft XP,MySQL,sql

Oracle Interval Data Types

with 5 comments

I saw an interesting post on INTERVAL YEAR TO MONTH while checking things out today. It struck me as odd, so I thought I’d share a similar sample along with my opinion about how it should be done in a PL/SQL block.

The example is a modification of what I found in a forum. You should see immediately that it’s a bit complex and doesn’t really describe what you should do with any months. Naturally, the example only dealt with years.

DECLARE
  lv_interval  INTERVAL YEAR TO MONTH;
  lv_end_day   DATE := '30-APR-2009';
  lv_start_day DATE := '30-APR-1975';
BEGIN
  lv_interval := TO_CHAR(FLOOR((lv_end_day - lv_start_day)/365.25))||'-00';
  DBMS_OUTPUT.put_line(lv_interval);
END;
/

I suggest that the better way is the following because it allows for months, which are a bit irregular when it comes to divisors.

DECLARE
  lv_interval  INTERVAL YEAR TO MONTH;
  lv_end_day   DATE := '30-APR-2009';
  lv_start_day DATE := '30-JAN-1976';
BEGIN
  lv_interval := TO_CHAR(EXTRACT(YEAR FROM lv_end_day) -
                            EXTRACT(YEAR FROM lv_start_day)) ||'-'||
                   TO_CHAR(EXTRACT(MONTH FROM lv_end_day) -
                             EXTRACT(MONTH FROM lv_start_day));
  DBMS_OUTPUT.put_line(lv_interval);
END;
/

Let me know if you’ve another alternative that you prefer.

Written by maclochlainn

May 2nd, 2009 at 9:10 pm

Posted in Oracle,pl/sql,sql