Oracle - Null and Zero-length string problem

[Parthasarathy Thandavarayan <Pa...@sos.sungard.com>]

 
Hi all,



I am working on an framework that uses torque 3.2 as the ORM layer.
Everything works perfectly

on MySQL 4.1 and I am right now trying to make it work on Oracle 10g
also. One of the problems

i am facing is with respect to the difference in the way oracle handles
zero-length strings

('') when compared to other dbs. In oracle zero-length strings are
treated as null. If we

are inserting '' in a column and query it back with where clause -->
where <column>

= ''  no rows will be returned. We should rather query it with the where
clause --> where

<column> is null. 

On other dbs for eg., MySQL 4.1 the where clause condition should be -->
where <column>

= ''. 



If I change the where clause to IS NULL then the app wont work on
MySQL.. If I retain it as

'' then it wont work on Oracle.

Can anyone help me with a solution or workaround for this? 



One possible way to make it work is by changing the SQLExpression class
to convert the criteria

to null from '' if the db is oracle. Is there anyother way to make this
work? the only restriction

is that the same where clause should work on all dbs





Thanks,



Sarathy
 
T.Parthasarathy *  SunGard  * Offshore Services * Divyasree Chambers
Langford Road * Bangalore 560025 India 
Tel +91-80-2222-0501 * Mobile +91-99450-00394 * Fax +91-80-2222-0511 *
www.sungard.com

Please note my email address -
<ma...@sos.sungard.com>
Parthasarathy.Thandavarayan@sos.sungard.com .  Please update your
contact list and use this address for all future communication.      

CONFIDENTIALITY: This email (including any attachments) may contain
confidential, proprietary and privileged information, and unauthorized
disclosure or use is prohibited. If you received this email in error,
please notify the sender and delete this email from your system. Thank
you.