Christian Roy

We are on the last stretch of our project and we need to deliver soon.

Sometimes, we have to make concessions and compromises in order to make sure to deliver in time.
This means coding something that is not necessarly clean.

We wanted to use a column that could have 3 different significations.
The column was already a varchar2 and we needed to keep it that way for legacy reasons.

The possible values we wanted were:

• value was a positive float (I already said it was a varchar2, yuk) which represent a salary
• value was null which represented that the user did not want to disclosed the salary.
• value was an empty string which represent a salary to be discussed.

The problem was that when using Doctrine to insert/update the field when using an empty string, it would insert/update a null instead.

We first thought the problem was Doctrine but we later found out the problem was Oracle.
As far as Oracle is concerned, an empty string and null are totally the same thing.

• http://forums.thedailywtf.com/forums/t/1225.aspx
• https://forums.oracle.com/forums/thread.jspa?messageID=1536505
• http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-em...

Maybe I am too used to MySQL but this seems like a WTF to me.

So we cannot do our dirty hack. Using empty string and null to mean something different is not kosher.

I realized that this oracle "feature" have a consequence. If we were using MySQL than it would mean that an empty string would also be considered null (for compatibility reasons).
(http://www.doctrine-project.org/projects/orm/1.2/docs/manual/configurati...)

Thoughts?