RE: [JBoss-user] Oracle+CMP+Fixed length columns

[Finn, Michael]

Title: Oracle+CMP+Fixed length columns

Thanks. This is really stupid behavior on Oracle's part, IMHO. Unfortunately, neither is a good option for us. VARCHARs incur unnecessary overhead, and BMP is a step backwards for us. I'll go back to plain DAOs first....

 

It's kind of a kludge, but what I have done for now is to use a rightPad on the value before calling the finder method (StringUtils.rightPad() from Jakarta-Commons for those interested).

 

Dain/other CMP-dudes: comments?

 

Mike

 

 

-----Original Message-----
From: Sasidharan, Manoj [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 16, 2002 4:42 PM
To: [EMAIL PROTECTED]
Subject: RE: [JBoss-user] Oracle+CMP+Fixed length columns

Hello All,

 

We had seen this behaviour...

 

The following are the possible workarounds:

1. Use Oracle specific syntax and BMP. CMP cannot handle this

2. Change the database schema to use VARCHAR instead of CHAR (we have adopted this as a short-term solution)

 

If somebody else have a better idea, please let me know.

 

HTH

regards

MS

-----Original Message-----
From: Finn, Michael [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 16, 2002 1:22 PM
To: Jboss-User (E-mail) (E-mail)
Subject: [JBoss-user] Oracle+CMP+Fixed length columns

Folks,

In Oracle, there is a PITA problem with prepared statements that forces you to right pad the value in a where clause arg  for fixed length fields. Example:

create table SOMETABLE
(
  SOMECOLUMN char(10)
)
// Assume this data
insert into SOMETABLE("FOO");

// And this code
PreparedStatement ps = conn.prepareStatement("select count(*) from SOMETABLE where SOMECOLUMN = ?");
ps.setString(1,"FOO");                 
ResultSet rs = ps.executeQuery();       << Will get count = 0

ps.setString(1,"FOO       ");           << Value rightpadded to size of DB col
ResultSet rs = ps.executeQuery();       << Will get count = 1

// But this
Statement st = conn.createStatement("select count(*) from SOMETABLE where SOMECOLUMN = 'FOO'");
ResultSet rs = st.executeQuery();       << Will get count = 1

Is there a provision for this (like a switch somewhere) in CMP, that allows one to pass a value (unmolested) to a finder method?

TIA

-----------------------
Mike Finn                              
Frontier Communications     
Rochester, NY                   
V: 585-777-8202
E: mailto:[EMAIL PROTECTED]