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;=, >=, <>,
* !=, 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 - [EMAIL PROTECTED] . 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:[EMAIL PROTECTED]
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:[EMAIL PROTECTED]
> Gesendet: Dienstag, 3. Oktober 2006 06:18
> An: [email protected]
> 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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
