I'm not so sure whether this is a good thing to do. On the one hand, it is
nice if Torque code behaves exactly the same for all databases. On the
other hand, someone who is used to write oracle sql can be confused by
this.
A change like this could break existing applications, if someone relies on
the behaviour which is now implemented. After the discussion about
TORQUE-44, I'd rather not change that in 3.2.1 (Other opinions are
welcome, of course).
I'd suggest the following:
- I'll add a section in the oracle FAQ addressing the problem and
Thoralf's solution.
- Parthasarathy, could you create a jira issue for this ? I'll schedule it
for Torque 4.0
- We will adress this for Torque 4.0
Please speak up if you do not like this suggestion.
Thomas
On Fri, 6 Oct 2006, Parthasarathy Thandavarayan wrote:
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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
