9 Replies Latest reply on Jan 8, 2009 4:42 PM by Franco Fernandes

    Oracle varchar2 with null value

    Franco Fernandes Novice

      I apologize to the moderator if this seems like a cross-post - posted to the Hibernate forum for help but got no response. Really could use some help here.



      Oracle Thin JDBC Driver 10.2.0.4.0
      Seam 2.0.2 SP1



      It seems like all varchar2 null values are triggering unnecessary updates from EntityHome even if I have made no changes.


      Here's an example.


      Item entity


      @Column(name = "MODEL_DESCRIPTION")
      public String getModelDescription() {
          return this.modelDescription;
      }
      
      public void setModelDescription(String modelDescription) {
         this.modelDescription = modelDescription;
      } 
      



      itemEdit.xhtml


           <h:inputText id="itemModelDescription"
                            value="#{itemHome.instance.modelDescription}"/>
      
      other fields ... 
      



      If I change any other fields on my page and call itemHomeitemHome.update, everything works fine. After update modelDescription is still null in the db.



      The problem I have is that when I first load my entity and make NO changes, Hibernate issues an unnecessary update.


      The update statement I see is


         update
              ITEM
          set
              DATE_MODIFIED=?,
              VERSION_ID=?,
              MODEL_DESCRIPTION=?
          where
              ITEM_ID=?
              and VERSION_ID=?
      




      date_modified is from MappedSuperClass field



      Debugging - it looks like at some points setModelDescription sets ""  into the field making Hibernate believe the data is dirty triggering an update.


      Varchar2 maps to String is that correct ?


      Is there  a JDBC driver parameter I need to specify in persistence.xml ?


      Thanks in advance for any help.




        • 1. Re: Oracle varchar2 with null value
          Stephen Friedrich Novice

          If you try and write a blank string to a VARCHAR2 column it will be stored as NULL in the DB (in other words: Oracle does not support empty strings at all).
          Try modifying your setter to change the empty string to null before storing it in the field.

          • 2. Re: Oracle varchar2 with null value
            Ingo Jobling Master

            Beware, Oracle stores empty strings ("") as nulls, this can cause the sort of problem you are seeing.

            • 3. Re: Oracle varchar2 with null value
              Franco Fernandes Novice

              Thanks for your replies guys, yes I am aware that Oracle will not store empty strings.


              Stephen - Are you actually suggesting that the setter be like this -


              public void setModelDescription(String modelDescription) {
                  // set only if not empty string
                  if ( modelDescription.trim().length() > 0 )     
                      this.modelDescription = modelDescription;
              }
              





              • 4. Re: Oracle varchar2 with null value
                Franco Fernandes Novice

                Bad code, setter does not get a value. I should shoot myself!



                • 5. Re: Oracle varchar2 with null value
                  Franco Fernandes Novice

                  Ok I wrote and tested this, works like a charm, no update fired if it is already a null value coming in and no changes were made.


                  public void setModelDescription(String modelDescription) {
                       this.modelDescription = ( modelDescription == null || modelDescription.trim().length() == 0 ) ? null : modelDescription;
                  }
                  



                  Now I gotta do this for my gazillion varchar2 fields ?


                  Could seam-gen generate-model have looked for null varchar2 columns and generated this code ?, just a thought.


                  I am fairly new to Hibernate / Seam and the rest of this cool stuff.


                  I am surprised no one else encountered this before.


                  Thanks for your help again, much appreciated.



                  Franco


                  • 6. Re: Oracle varchar2 with null value
                    Stephen Friedrich Novice

                    I am surprised no one else encountered this before.

                    Yes, that's what I was thinking, too.
                    My suggestion was more a check to see if the null-problem was really the cause.
                    However there really must be something else in your setup/use case.
                    I haven't seen that behavior in my app (that uses Oracle, too).


                    When exactly are the updates happening? You simply load your entities and they are automatically saved again during the same http request?
                    What versions of hibernate, seam, oracle exactly?

                    • 7. Re: Oracle varchar2 with null value
                      Franco Fernandes Novice

                      Seam 2.1.0.SP1


                      Hibernate-Version: 3.2.4.sp1


                      Server  Tomcat 6



                      Oracle Database 10g Release 10.2.0.1.0 (running on localhost)
                        with Oracle Thin JDBC Driver 10.2.0.4.0 - using this driver version because our production environment uses it.


                      The application was generated from a seam-gen template, the flow is


                      itemList.xhtml - Click existing record from list



                      itemEdit.xhtml - Click save button to call itemHome.update



                      I am going to create a new db table and play ...



                      • 8. Re: Oracle varchar2 with null value
                        Franco Fernandes Novice

                        I added a null column to another table and tested it's view and got the same issue.


                        I figured since it is so easy with seam-gen I created a new project connected to the same db instance and got the same issues.


                        At this point I am thinking it is a database version to JDBC driver incompatibility issue of sorts.


                        Stephen - perhaps you can share your dev environment.



                        Thanks


                        Franco

                        • 9. Re: Oracle varchar2 with null value
                          Franco Fernandes Novice

                          I spent all of yesterday trying to figure this out.


                          I generated a new seam-gen project connecting to the same db instance - no dice.


                          I then created another new seam-gen project this time connecting to a Oracle 9.2.0.8 instance and using the Oracle 9i driver - same result.


                          I do have a solution - modifying the setter for all my null varchar2 columns as above.


                          I am going to give up for now, if anyone else experiences a similar issue, I would be happy to join this discussion again.


                          Franco