db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Fischer <tfisc...@apache.org>
Subject RE: Oracle - Null and Zero-length string problem
Date Sun, 08 Oct 2006 08:50:32 GMT
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;, &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