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: AW: Oracle - Null and Zero-length string problem
Date: Fri, 6 Oct 2006 14:29:04 +0200
Message-ID: 
 <F1EF69E69C1EED4FA3BB295B07664EEE523861@srv-master.cadooz-ag.intern>
Thread-Topic: Oracle - Null and Zero-length string problem
Thread-Index: AcbmovNu7bDxAukmQg6SVUDXXK/12wCnzN9A
From: "Thoralf Rickert" <thoralf.rickert@cadooz.de>
To: "Apache Torque Users List" <torque-user@db.apache.org>

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]=20
> 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=20
> layer. Everything works perfectly on MySQL 4.1 and I am right=20
> now trying to make it work on Oracle 10g also. One of the=20
> problems i am facing is with respect to the difference in the=20
> way oracle handles zero-length strings ('') when compared to=20
> other dbs. In oracle zero-length strings are treated as null.=20
> If we are inserting '' in a column and query it back with=20
> where clause --> where <column> =3D ''  no rows will be=20
> 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=20
> work on MySQL.. If I retain it as '' then it wont work on=20
> Oracle. Can anyone help me with a solution or workaround for this?=20
>=20
> One possible way to make it work is by changing the=20
> SQLExpression class to convert the criteria to null from ''=20
> if the db is oracle. Is there anyother way to make this work?=20
> the only restriction is that the same where clause should=20
> 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