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.

Comments

Post a Comment

Popular posts from this blog

Remove copy protection from PDF documents

This is probably illegal, so use it at your own risk. Some PDFs on the internet have a copy protection to make sure you cannot copy-paste any content from the PDF into a document you're writing. To circumvent this protection there are several tools available on the internet. I haven't tried any of them, because they're not free ;) There is however a procedure that will enable you to circumvent the copy protection using free tools. The trick is to convert the PDF to a PS (PostScript) document first and then convert it back into a PDF. So, download Ghostview en Ghostscript from: http://www.cs.wisc.edu/~ghost/ . Next, open your PDF in Ghostview. Next, from the "File" menu, select "Convert" (a dialog will pop up) Press "Ok" Fill in the name of the "converted" file. Press "Save" Tada, you can now copy content from your newly created PDF ;)
Read more

The story of the Cobalt Qube

Image
The other week, a friendly colleague of mine brought me a small present. He gave me one of his Cobalt Qube s (for free!) which was collecting dust at his home. He was certain that I could give it a better use. I think I can still remember the days these things hit the market (somewhere in the late 90's). This was a home / office server appliance way before the people of Microsoft thought up their Windows Home Server . It was running some modified version of Linux RedHat, featuring a web interface for all administrative tasks. It also had 2 network cards, so you could easily turn it into a gateway / firewall. The RedHat version running on the Qube was known to be notoriously insecure. It was also running an older 2.0.x kernel which was outdated, even at that time. The Qube 2700 and Qube 2 were both equipped with a MIPS processor. Later models had an i386 architecture. Mine was a Qube 2. Getting the serial connection to work The Qube does not have any VGA adapter, so if you want to s...
Read more

The end of vinyl records

Vinyl records have been there for ages. My dad still has a couple of old records but he has no turntable anymore to play them. This is because _I_ claimed his turntable a long time ago, which he thought had become old fashioned. I started buying vinyl records about 12 years ago. I remember saving all my pocket money and buying the latest records with it, instead of visiting pubs with friends. I remember everyone thinking vinyl records belonged to the past and MP3's and CD's were taking over. In fact, this was not true. DJ's playing at parties and clubs never stopped buying records and from my experience, about 80% of their music came from plain old vinyl and a tiny 20% from other media like CD's or MP3's. The reasoning behind this is simple, if you wanted something new, something not popular (yet), you bought it on vinyl. If it became popular, some record company would probably be so kind to put it on some CD and sell it. But then again, ages, if not decades ( :) ) ...
Read more