Wednesday, November 29, 2006

Development: Empty strings in Oracle and MySQL

Today I was testing some code I've written, on a client's QA environment. These tests revealed, that if your tests succeed locally on a MySQL server environment this doesn't necessarily mean the tests will succeed on an Oracle server. I was having problems with Oracle's interpretation of an empty string. It seems that if you're executing the following statement:

insert into MyTable (c1,c2) values ('val1','');

this is the same as executing:

insert into MyTable (c1,c2) values ('val1',null);

IMHO this is plain wrong. '' does not equal NULL from a programmer's point of view.
Inserting an empty string (or '') can be convenient to make your prepared statements easier.
Suppose, in the above example, c2 is an optional parameter. When c2 is not available you insert ''. In this case a prepared statement to select records from MyTable could be:

select * from MyTable where c1 = ? and c2 = ?

After substitution the resulting statement for MySQL will be:

select * from MyTable where c1 = 'example' and c2 = ''

And for Oracle:

select * from MyTable where c1 = 'example' and c2 = null

Which is wrong.
So, if Oracle replaces my empty string with null, the prepared statement should be:

select * from MyTable where c1 = ? and coalesce(c2,'dontcare') = coalesce(?,'dontcare')

(please see also this link)
This works for both MySQL and Oracle ... of course.

No comments: