Archive for May, 2009
Not quite an invalid function
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; / |
MySQL Merge gone Awry
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.
MySQL Installation and More
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:
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.
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 , 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. 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. 5. You can put the files anywhere you choose but I’d recommend that you opt to use something like this directory path: 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 6. The choice of your file directory is now displayed below the caption Install to:, and you click the Next button to continue. 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: You click the Change button to enter another location for the database files. 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: Click on the OK button to complete the selection of a customized database file location. 9. You should now see the wizard screen again. Choose the Next button to cotinue. 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. 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. After the status bar, you’ll see the following marketing screen. Click the Next button to continue. 12. The second marketing dialog then shows up, just click the Next button to continue. 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. 14. The next dialog is the configuration welcome. Click the Next button to continue. 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. 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). 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. 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. 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. 20. This dialog lets you choose a multiple byte unicode character set or the standard 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. 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. 23. You’re almost done. The next dialog confirms you want to configure MySQL. Click the Execute button to configure the server. After the configuration, you’ll see the following dialog. It means you’re done. 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. 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 You can confirm your the root user by running the following query: (b). You create a database with the following syntax: (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. (d). You sign-off as the root user by typing quit, like (e). You sign-on as the restricted You can confirm your the restricted user by running the following query: (f). You can’t do much except explore generic metadata at this point because you must select a database. You select the You could now create a 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: If you named the file 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 
C:\Program Files\MySQL 5.1\MySQL Server 5.1\
(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.
C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\
C:\Data\MySQL 5.1\MySQL Server 5.1\
latin1. For those coming from an Oracle world, that’s WEISO8859-1 character set.
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 -P3306mysql> SELECT CURRENT_USER();
mysql> CREATE DATABASE sampledb;
mysql> CREATE USER 'myuser' IDENTIFIED BY 'myuser';
mysql> GRANT ALL ON sampledb.* TO 'myuser'@'localhost' IDENTIFIED BY 'myuser';
mysql> QUIT;myuser with the following syntax:C:\Data> mysql -umyuser -pmyuser -P3306mysql> SELECT CURRENT_USER();
sampledb database by doing the following:mysql> USE sampledb;
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 |
+-----------+-------------+
-- 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
sample.sql and it is found in the C:\Data directory, you’d run it like this:mysql> SOURCE C:/DATA/sample.sql
sqlplus, MySQL’s mysql, and Microsoft SQL Server 2008’s sqlcmd.
SQL Automated Numbers
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.
Fix SQL Server 2008 Client
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.
Launching that provides you the following screen shot:
Open the SQL Server Network Configuration detail and choose Protocols for SQLEXPRESS. Double click on the Named Pipes protocol name.
You then enable the protocol and change the Pipe Name as noted above and shown in the screen shot below:
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.
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.
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.
MySQL Explain Plan
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.
MySQL MSI Service Fails
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.
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.
MySQL export to CSV
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.
Oracle Interval Data Types
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.
Subscribe via RSS