CODEWITHSUNDEEP
javaspringsql-server-2008oracle10gjdbctemplate
bnguyen82
bnguyen82

Reputation: 6248

Update() method doesn't work with ORACLE database

I don't know why the update() method doesn't work with ORACLE database

deleteQuery = "delete from USBRPF where upper(userid) = upper(?)" ;

String s= "ABC " ;
getJdbcTemplate().update(deleteQuery, s.trim());

There's a row with column USERID having data 'ABC ' (there's some spaces character after 'C' character)

It seems to not find out that row.

However, if I change code to below, it works

deleteQuery = "delete from USBRPF where upper(userid) like upper(?)" ;
String s= "ABC " ;
getJdbcTemplate().update(deleteQuery, s.trim() +  "%");

or

deleteQuery = "delete from USBRPF where upper(trim(userid)) = upper(?)" ;
String s= "ABC " ;
getJdbcTemplate().update(deleteQuery, s.trim());

Note: all works with MSSQL database, with data migrated from ORACLE. I guess there's problem with database setting. Could have someone figure it out? Thanks

MODIFIED:

Column information:

ORACLE

  BRANCH    CHAR(2 CHAR)          
  COMPANY   CHAR(1 CHAR)         
  DATIME    TIMESTAMP(6)          
  JOBNM CHAR(10 CHAR)          
  UNIQUE_NUMBER NUMBER(18,0)    
  USERID    CHAR(10 CHAR)         
  USRPRF    CHAR(10 CHAR)

MSSQL

[UNIQUE_NUMBER] [bigint] IDENTITY(1,1) NOT NULL,    
[USERID] [nchar](10) NULL,  
[COMPANY] [nchar](1) NULL,  
[BRANCH] [nchar](2) NULL,   
[USRPRF] [nchar](10) NULL,  
[JOBNM] [nchar](10) NULL,   
[DATIME] [datetime2](6)> NULL,

Upvotes: 1

Views: 430

Answers (1)

Andres Olarte
Andres Olarte

Reputation: 4380

CHAR is a fixed length type. So even if your data looks like "ABC" in the database, it's stored as "ABC ". CHAR columns will be padded with spaces up their size.

Therefore on the first example you're comparing "ABC " (as stored in the DB) to "ABC" (as passed from Java after the trim() call). On your second and third example you're working around this.

I would recommend that you use VARCHAR2 since it's more natural and more commonly used. If not possible, you could try padding the value that you pass from Java up to the CHAR size as defined in Oracle.

Upvotes: 2

Related Questions