db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Parthasarathy Thandavarayan" <Parthasarathy.Thandavara...@sos.sungard.com>
Subject RE: Oracle - Null and Zero-length string problem
Date Fri, 06 Oct 2006 13:45:27 GMT


Thanks Thoralf, I will try it.. I am sure this will work.

I am thinking that instead of building these type of workarounds to take care of db specific
idiosyncrasies in our code we should do it in the torque classes. 

The build expression in torque.util.SQLExpression class accepts the db parameter and even
the comment for it says - this is for vendor specific functions

   * Builds a simple SQL expression.
     *
     * @param columnName A column.
     * @param criteria The value to compare the column against.
     * @param comparison One of =, &lt;, &gt;, ^lt;=, &gt;=, &lt;&gt;,
     *        !=, LIKE, etc.
     * @param ignoreCase If true and columns represent Strings, the appropriate
     *        function defined for the database will be used to ignore
     *        differences in case.
     * @param db Represents the database in use, for vendor specific functions.
     * @param whereClause A StringBuffer to which the sql expression will be
     *        appended.
     */
    public static void build(String columnName,
                              Object criteria,
                              SqlEnum comparison,
                              boolean ignoreCase,
                              DB db,
                              StringBuffer whereClause)


Why cannot we write a code in this method which simply says -  if db is of type dboracle and
if criteria="" then criteria is null

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 - 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.



-----Original Message-----
From: Thoralf Rickert [mailto:thoralf.rickert@cadooz.de] 
Sent: Friday, October 06, 2006 5:59 PM
To: Apache Torque Users List
Subject: AW: Oracle - Null and Zero-length string problem


Just one idea - I'm not using Oracle - but what about:

  (column = '' OR column IS NULL)

In Torque

  Criteria.Criterion c1 = criteria.getNewCriterion(COLUMN, "", Criteria.EQUAL);
  Criteria.Criterion c2 = criteria.getNewCriterion(COLUMN, (Object)null, Criteria.ISNULL);
  criteria.add(c1.or(c2));

? Does this work in Oracle. In MySQL this isn't a problem.


> -----Urspr√ľngliche Nachricht-----
> Von: Parthasarathy Thandavarayan 
> [mailto:Parthasarathy.Thandavarayan@sos.sungard.com]
> Gesendet: Dienstag, 3. Oktober 2006 06:18
> An: torque-user@db.apache.org
> Betreff: Oracle - Null and Zero-length string problem
> 
> 
> 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
> 
>  
> 


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org




I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html    

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message