Mailing-List: contact torque-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Apache Torque Users List" <torque-user@db.apache.org>
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Subject: RE: Oracle - Null and Zero-length string problem
Date: Fri, 6 Oct 2006 19:15:27 +0530
Message-ID: 
 <E270136618D3BF4784FBD6D9544E3C84011A6AF9@mailserver.salliemaesolutions.soft.net>
In-Reply-To: 
 <F1EF69E69C1EED4FA3BB295B07664EEE523861@srv-master.cadooz-ag.intern>
Thread-Topic: Oracle - Null and Zero-length string problem
Thread-Index: AcbmovNu7bDxAukmQg6SVUDXXK/12wCnzN9AAAFVU+A=
From: "Parthasarathy Thandavarayan"
 <Parthasarathy.Thandavarayan@sos.sungard.com>
To: "Apache Torque Users List" <torque-user@db.apache.org>


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

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 =3D, &lt;, &gt;, ^lt;=3D, &gt;=3D, =
&lt;&gt;,
     *        !=3D, 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=3D"" then criteria is null

T.Parthasarathy *  SunGard  * Offshore Services * Divyasree Chambers =
Langford Road * Bangalore 560025 India=20
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.     =20

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]=20
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 =3D '' OR column IS NULL)

In Torque

  Criteria.Criterion c1 =3D criteria.getNewCriterion(COLUMN, "", =
Criteria.EQUAL);
  Criteria.Criterion c2 =3D 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=FCngliche Nachricht-----
> Von: Parthasarathy Thandavarayan=20
> [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
>=20
>=20
> Hi all,
>=20
> I am working on an framework that uses torque 3.2 as the ORM layer.=20
> Everything works perfectly on MySQL 4.1 and I am right now trying to=20
> make it work on Oracle 10g also. One of the problems i am facing is=20
> with respect to the difference in the way oracle handles zero-length=20
> 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=20
> back with where clause --> where <column> =3D ''  no rows will be
> returned. We should rather query it with the where clause -->=20
> where <column> is null.=20
> On other dbs for eg., MySQL 4.1 the where clause condition=20
> should be --> where <column> =3D ''.=20
>=20
> If I change the where clause to IS NULL then the app wont work on=20
> MySQL.. If I retain it as '' then it wont work on Oracle. Can anyone=20
> help me with a solution or workaround for this?
>=20
> One possible way to make it work is by changing the SQLExpression=20
> class to convert the criteria to null from '' if the db is oracle. Is=20
> there anyother way to make this work? the only restriction is that the =

> same where clause should work on all dbs
>=20
>=20
> Thanks,
>=20
> Sarathy
>=20
> =20
>=20


---------------------------------------------------------------------
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   =20

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