[SOLVED]Why is String " " being read as NULL when mapping from the database to an object using ibatis?

Question

In my oracle database I have table called PERSON with columns code, surname, forename.

eg. 'PER001', '________________', 'Bob' (NOTE: _ = space)

surname is set to NOT NULL but you can enter in a string of spaces like " ".

I am having a problem with this because when Ibatis maps this column to an Object it is mapping it as NULL! when it should be mapping it as " " ?

Any ideas?

Answer 1

It is one of the quirks of Oracle that it treats empty strings as NULL. This is controversial in some quarters (trying googling on http://www.google.co.uk/search?q=oracle+null+"empty+string" ). Oracle may change this behaviour in the future. Just don't hold your breath.

Edit

As has been pointed out, it's not an empty string, it's a string of blanks. Well, in that case it's not Oracle itself.

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                     NOT NULL VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> insert into emp (empno, ename) values (9999, '      ')
  2  /

1 row created.


SQL> select length(ename) from emp
  2  where empno = 9999
  3  /

LENGTH(ENAME)
-------------
            6
SQL>

Possible culprits are:

1. a trigger applying a TRIM() to the column in question (or some similar processing)

2. IBATIS intervening in some fashion

Answer 2

This is evading the issue, but could you choose another string, such as "-" or "_" to indicate a missing value?

Depending on a blank string being treated different from an empty string being treated different from a null string by all parts of your software stack seems rather risky.

Answer 3

Vent here ;)

http://forums.thedailywtf.com/forums/t/1225.aspx?PageIndex=1

Answer 4

Ok I've figured this out! I didn't expect the ibatis generated setter's to be trimming the string! >_>

BEFORE:

public void setSURNAME(String SURNAME) {
    this.SURNAME = SURNAME == null ? null : SURNAME.trim();
}

FIX!:

public void setSURNAME(String SURNAME) {
    this.SURNAME = SURNAME == null ? null : SURNAME;
}

Thanks for the help guys! Sorry >_>!