Fwd: Oracle trimming, and NULLs

Anthony Berglas <[email protected]>
Newsgroups gmane.comp.java.orm.simpleorm
Message-ID <[email protected]> 
>Date: Tue, 09 Sep 2008 10:17:37 +1000
>To: Harry <[email protected]>
>From: Anthony Berglas <[email protected]>
>Subject: Oracle trimming, and NULLs
>
>You forgot about CHAR type.  Which for Oracle 
>seems to return spaced filled fields, and even 
>seems to require padding for comparison.  My 
>feeling was that a field of spaces was pretty 
>empty -- my ancient COBOL heritage...
>
>But the Oracle "" vs null is an outstanding 
>issue.  And some dbs (Access) can represent both 
>but normally use "" to represent null, which is 
>actually a good idea because it avoids the weird 
>null semantics.  That is why I provided 
>setEmpty, with the idea that it could be tunable later.
>
>I'm not sure what the best solution is.  I'm 
>pretty sure Hibernate just ignores the 
>problem.  Currently we leave it to the user, and 
>if they have a query in Oracle ...eq(FOO, "") it 
>will simply always return false.  But isEmpty 
>seems to be the first and most important step.
>
>If you would like to contribute then please 
>improve the DataTypesTest for Oracle for 
>EMPTYSTR, which should really use isEmpty.
>
>A method SDriver.emptyStringsAreNulls could be 
>written, but how would it be used?  And remember 
>that SQL Server also has a SET ANSI_NULLS 
>statement that affects the handling of nulls dynamically.
>
>I think that the simple rule is
>
>* Never use empty strings.  Always use null to 
>represent empty string.  And always test with IS 
>NULL.  Painful, but it is what most aps expect.
>
>* Use isEmpty to test for null.
>
>We need to fix SQuery.eq to work 
>properly.  Maybe generate (FOO = '' OR FOO IS NULL).
>
>Note that on a non-Oracle database if you use 
>both "" and Nulls you will quickly end up with a mess.
>
>Anthony
>
>At 01:59 AM 9/09/2008, Harry wrote:
>>Hi Anthony,
>>While I was looking at the modifications we made, I was just wondering
>>if we are not doing a mistake by trimming the value to look if it's
>>empty.
>>We as java programmers tend to trim strings to avoid difficult bugs
>>where values look empty but in fact contain spaces. But is it legitimate
>>to treat *field* values that have spaces as "empty" ? For sure Oracle
>>does not, and also other databases do not. So maybe we should explain
>>better what "empty" means in this context, and why it would be useful
>>to test for it. I haven't seen any use of the method inside simpleorm,
>>so I don't know what the intent of this method is.
>>
>>Also, do you remember we once had a discussion (this was in Message #1545)
>>about strings of length 0 in java that are treated as NULL by Oracle
>>and a few others ?
>>Well I still think it would be a good thing to have a method in the
>>specific SDriverXXX that returns true if a string of length 0 means
>>the same than NULL. In this manner, we won't let the user encounter the
>>confusing situation where he has put "" in a primary key field,
>>isNull(SFieldMeta) returns false, and then simpleorm commits to the
>>database, suprise, he gets an SQL Exception for insertion of a null
>>value. I am quite sure that they won't guess immediately that it was
>>because the empty string meant in fact null.
>>
>>Here is an example with spaces and nulls in Oracle :
>>
>>CREATE TABLE FOO (
>>   FOO_ID INTEGER PRIMARY KEY,
>>   BAR VARCHAR(32)
>>);
>>
>>INSERT INTO FOO(FOO_ID, BAR) VALUES (1, NULL);
>>INSERT INTO FOO(FOO_ID, BAR) VALUES (2, '');
>>INSERT INTO FOO(FOO_ID, BAR) VALUES (3, ' ');
>>INSERT INTO FOO(FOO_ID, BAR) VALUES (4, '    ');
>>INSERT INTO FOO(FOO_ID, BAR) VALUES (5, 'Hard Rock Cafe');
>>
>>select FOO_ID, BAR, '#' || BAR || '#' from FOO;
>>
>>FOO_ID  BAR             '#'||BAR||'#'
>>1       <null>          ##
>>2       <null>          ##
>>3                       # #
>>4                       #    #
>>5       Hard Rock Cafe  #Hard Rock Cafe#
>>
>>
>>Best regards,
>>
>>                                                        Harry Karadimas
>>______________________________________________________________________
>>Dr Harry Karadimas  Medecin Ingenieur
>>resp. Recherche et Developpement, Administration Wintel
>>Departement d'Information Hospitalier (DIH)
>>C.H.U. Albert Chenevier - Henri Mondor
>>51, av. du Marechal de Lattre de Tassigny               94010  CRETEIL
>>tel : (00 33 1) 49 81 21 79                fax : (00 33 1) 49 81 27 08
>>secr.: (00 33 1) 49 81 23 82         m.el.:[email protected]
>>
>>
>>
>> > -----Message d'origine-----
>> > De : Anthony Berglas [mailto:[email protected]]
>> > Envoyé : lundi 8 septembre 2008 12:35
>> > À : Harry; [email protected]
>> > Objet : RE: [SimpleORM] Re: New SimpleORM Whitepaper
>> >
>> > Hello Harry,
>> >
>> > Good point, I fixed isEmpty, but to the code
>> > below (avoids creating the trimmed object).
>> >
>> > You mentioned "Oracle Locking",  what are you doing with it?
>> >
>> > Anthony
>> >
>> >      public boolean isEmpty(SFieldMeta field) {
>> >          Object obj = getObject(field);
>> >          if (obj == null) return true;
>> >          if (obj instanceof String) {
>> >              String str = (String)obj;
>> >              for (int sx=0; sx < str.length(); sx++)
>> >                  if (str.charAt(sx) != ' ') return false;
>> >              return true;
>> >          }
>> >          return false;
>> >          }
>> >
>> > At 12:54 AM 3/09/2008, Harry wrote:
>> > >Hi Anthony,
>> > >
>> > >I have downloaded version 3.0 and also revision 806 on svn.
>> > >
>> > >For the moment I have just recompiled and it does not recompile
>> > >against 1.5. Here are 2 modifications I made to make it compile
>> > >against 1.5 :
>> > >
>> > >-------------------------------------------------------------
>> > ------------------
>> > >Method :
>> > >
>> > >simpleorm.dataset.SRecordInstance.isEmpty(SFieldMeta)
>> > >
>> > >Replaced
>> > >
>> > >public boolean isEmpty(SFieldMeta field) {
>> > >         Object obj = getObject(field);
>> > >         if (obj == null) return true;
>> > >         if (obj instanceof String) {
>> > >             String str = (String)obj;
>> > >             if (str.isEmpty()) return true;
>> > >             if (str.trim().isEmpty()) return true; //
>> > little expensive
>> > >         }
>> > >         return false;
>> > >}
>> > >
>> > >With
>> > >
>> > >public boolean isEmpty(SFieldMeta field) {
>> > >         Object obj = getObject(field);
>> > >         if (obj == null) return true;
>> > >         if (obj instanceof String) {
>> > >             String str = (String)obj;
>> > >             if (str.trim().equals("")) return true;
>> > >         }
>> > >         return false;
>> > >}
>> > >
>> > >Rationale :
>> > >
>> > >String.isEmpty() only exists since java 6, and comparing
>> > with "" does the job
>> > >also.
>> > >Most of the time the object is not empty, so comparing with
>> > "" and then
>> > >trimming and comparing again with "" is actually more expensive than
>> > >systematically trimming and comparing with "".
>> > >
>> > >
>> > >-------------------------------------------------------------
>> > ------------------
>> > >Method:
>> > >simpleorm.sessionjdbc.SQueryExecute.addJoinedSelectFields(SFi
>> > eldScalar[],
>> > >Map<SRecordMeta<?>, SSelectMode>, SRecordMeta<?>)
>> > >
>> > >Replaced
>> > >
>> > >         private SFieldScalar[] addJoinedSelectFields(
>> > >         SFieldScalar[] selectList,
>> > > Map<SRecordMeta<?>, SSelectMode> joinTables,
>> > >SRecordMeta<?> mainMeta) {
>> > >
>> > >                 SFieldScalar[] result = selectList == null ?
>> > >mainMeta.getQueriedScalarFields() : selectList;
>> > >                 for (Map.Entry<SRecordMeta<?>, SSelectMode> entry :
>> > >joinTables.entrySet()) {
>> > >                         int resSize = result.length;
>> > >                         if ( !
>> > SSelectMode.SNONE.equals(entry.getValue())) {
>> > >                                 SFieldScalar[] joinFlds =
>> > >entry.getKey().fieldsForMode(entry.getValue());
>> > >                                 result = Arrays.copyOf(result,
>> > >result.length+joinFlds.length);
>> > >                                 for (int i = 0; i <
>> > joinFlds.length; i++) {
>> > >                                         result[i+resSize] =
>> > joinFlds[i];
>> > >                                 }
>> > >                         }
>> > >                 }
>> > >                 return result;
>> > >         }
>> > >
>> > >With
>> > >
>> > >         private SFieldScalar[] addJoinedSelectFields(
>> > >         SFieldScalar[] selectList,
>> > > Map<SRecordMeta<?>, SSelectMode> joinTables,
>> > >SRecordMeta<?> mainMeta) {
>> > >
>> > >                 SFieldScalar[] result = selectList == null ?
>> > >mainMeta.getQueriedScalarFields() : selectList;
>> > >                 ArrayList<SFieldScalar> resultLst = new
>> > >ArrayList<SFieldScalar>(Arrays.asList(result));
>> > >                 for (Map.Entry<SRecordMeta<?>, SSelectMode> entry :
>> > >joinTables.entrySet()) {
>> > >                         int resSize = result.length;
>> > >                         if ( !
>> > SSelectMode.SNONE.equals(entry.getValue())) {
>> > >                                 SFieldScalar[] joinFlds =
>> > >entry.getKey().fieldsForMode(entry.getValue());
>> > >
>> > resultLst.addAll(Arrays.asList(joinFlds));
>> > >                         }
>> > >                 }
>> > >                 result = resultLst.toArray(new
>> > > SFieldScalar[resultLst.size()]);
>> > >                 return result;
>> > >         }
>> > >
>> > >Rationale :
>> > >
>> > >Arrays.copyOf exists starting with jdk 6 only.
>> > >Using java collections and adding to a list is just as
>> > efficient, the list
>> > >is converted back to an array at the end.
>> > >
>> > >-------------------------------------------------------------
>> > ------------------
>> > >
>> > >I am testing further aspects against my app.
>> > >
>> > >Best regards,
>> > >
>> > >Harry
>> > >
>> > >
>> > Harry Karadimas
>> > >_____________________________________________________________
>> > _________
>> > >Dr Harry Karadimas  Medecin Ingenieur
>> > >resp. Recherche et Developpement, Administration Wintel
>> > >Departement d'Information Hospitalier (DIH)
>> > >C.H.U. Albert Chenevier - Henri Mondor
>> > >51, av. du Marechal de Lattre de Tassigny
>> > 94010  CRETEIL
>> > >tel : (00 33 1) 49 81 21 79                fax : (00 33 1)
>> > 49 81 27 08
>> > >secr.: (00 33 1) 49 81 23 82
>> > m.el.:[email protected]
>> > >
>> > >
>> > >
>> > > > -----Message d'origine-----
>> > > > De : Anthony Berglas [mailto:[email protected]]
>> > > > Envoyé : mardi 19 août 2008 04:30
>> > > > À : hkara1
>> > > > Objet : Re: [SimpleORM] Re: New SimpleORM Whitepaper
>> > > >
>> > > > Hello Harry,
>> > > >
>> > > > I have uploaded the new version 3.0.  Is there any chance that you
>> > > > will have time to have a look at it, and ideally upgrade?  This
>> > > > involves a bit of tedious typing, but should be fairly fast.
>> > > >
>> > > > (The old version is on a branch and could be pached if necessary.)
>> > > >
>> > > > Anthony
>> > > >
>> > > >
>> > > > >Hi,
>> > > > >
>> > > > >I welcome this change; I am heavily using Oracle with SimpleOrm
>> > > > >(storing and serving a few ten of thousands of
>> > biological results in
>> > > > >an 900 bed hospital), and every now and then, (once
>> > every month in
>> > > > >average) Oracle entered in a deadlock. I resolved the problem by
>> > > > >subclassing the Oracle driver and disabling locking, and then I
>> > > > >used the modified driver.
>> > > > >Otherwise SimpleOrm works quite well for me.
>> > > > >
>> > > > >Best regards,
>> > > > >
>> > > > >Harry
>> > > > >
>> > > > >
>> > > >
>> > > > Dr Anthony Berglas, [email protected]       Mobile: +61
>> > 4 4838 8874
>> > > > Just because it is possible to push twigs along the ground
>> > > > with ones nose
>> > > > does not necessarily mean that is the best way to collect
>> > firewood.
>> > > >
>> >
>> > Dr Anthony Berglas, [email protected]       Mobile: +61 4 4838 8874
>> > Just because it is possible to push twigs along the ground
>> > with ones nose
>> > does not necessarily mean that is the best way to collect firewood.
>> >
>> >
>> >
>
>Dr Anthony Berglas, [email protected]       Mobile: +61 4 4838 8874
>Just because it is possible to push twigs along the ground with ones nose
>does not necessarily mean that is the best way to collect firewood.

Dr Anthony Berglas, [email protected]       Mobile: +61 4 4838 8874
Just because it is possible to push twigs along the ground with ones nose
does not necessarily mean that is the best way to collect firewood.


------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/SimpleORM/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/SimpleORM/join
    (Yahoo! ID required)

<*> To change settings via email:
    mailto:[email protected] 
    mailto:[email protected]

<*> To unsubscribe from this group, send an email to:
    [email protected]

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.