In my day job over at Jive Software I get to work on the crazy cool Clearspace product and unless you’ve been reading the system requirements lately, you probably didn’t notice that we support six different database platforms: MySQL, Oracle, Postgres, DB2, SQL Server and HSQLDB. Clearspace borrowed a number of classes from Jive Forums so a lot of the database specific code has already been flushed out, but I ran into a couple interesting things this past week that I thought needed to be blogged.

The first thing I ran into was a varchar case sensitivity issue: DB2, Oracle, Postgres and HSQLDB are all sensitive about case with respect to the values they store. So if you do this:

INSERT INTO appuser(username,password) VALUES('Administrator','password');

and then try to retrieve the user:

SELECT username,password FROM appuser WHERE username = 'administrator'

you’ll get different results than you will with MySQL and SQL Server (which are both case insensitive by default). Bottom line: if you plan supporting an application on a variety of database servers, make sure you toLowerCase() or toUpperCase() the string values you store if you plan on doing look ups against those values. If you’re into this kind of thing, page 87 of the SQL 92 standard appears to discuss the case sensitivity issues, but I can’t make heads of tails of it. Any good specification readers out there?

The second thing involved Oracle, which has this wonderful feature where you can’t insert an empty string into a varchar column. Example:

INSERT INTO appuser(username, password) VALUES('administrator', '');

Bennett McElwee blogged about this feature in more detail on his blog, but the jist of this nugget of goodness is that if you attempt to insert an empty space into an Oracle varchar, your empty space will get converted to

null

which is not helpful in the least bit. Consider yourself warned.

Finally, and again Oracle. Assume you have the sample table I used above with two columns: username and password. Assume further that you want to modify the username column to be 100 chars instead of 50. You write an ALTER statement that looks something like this for SQL Server:

ALTER TABLE appuser ALTER COLUMN username nvarchar(100) NOT NULL

on MySQL you’d have this:

ALTER TABLE appuser MODIFY COLUMN username nvarchar(100) NOT NULL

on DB2:

ALTER TABLE appuser ALTER COLUMN username varchar(100) NOT NULL

All very similar. But Oracle, no, if you tried this:

ALTER TABLE appuser MODIFY(COLUMN username varchar(100) NOT NULL)

on Oracle you’d get this error:

ORA-01451: column to be modified to NULL cannot be modified to NULL

See Oracle, for whatever reason, decided that if you are going to modify the column that you can ONLY specify the nullability of a column IF the nullability itself is changing. Which is ridiculous nice.

And yes, nullability is a word.