Oracle varchar2 with null value

[francof]

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.

[stephen]

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.

[joblini]

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

[francof]

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;
}

[francof]

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

[francof]

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

[stephen]

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?

[francof]

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 ...

[francof]

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

[francof]

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