OJB criteria fails with Oracle char field right filled with spaces

[gf...@fys.com.ar]

Hi all,

We are using OJB, Oracle 9i. When trying to retrieve an object by criteria,
OJB is not finding the requested object since the CHAR fields are right
filled with spaces.

Just an example:

oracle table emp
field name: FIRST_NAME(CHAR10)

object Emp, attribute firstName

Object/Record to to retrieve should have firstName = Gustavo (this was
stored using OJB; Oracle fills with spaces to the right of the user
inserted string in a CHAR field)

This will work: OJB will return the object if criteria sets emp.firstName
to "Gustavo   " (note the 3 spaces at the right):
Criteria mycrit = new Criteria();
mycrit.addEqualTo("firstName", "Gustavo   ");
Query myquery = new QueryByCriteria(emp.class, mycrit);
myemp= (Emp)broker.getObjectByQuery(myquery);

This will not work: OJB will fail to return the object if criteria is set
to emp.firstName = "Gustavo" (no spaces).
Criteria mycrit = new Criteria();
mycrit.addEqualTo("firstName", "Gustavo");
Query myquery = new QueryByCriteria(emp.class, mycrit);
myemp= (Emp)broker.getObjectByQuery(myquery);

Problem here is that the java coder has to know the database field lenght
in order to fill the string with the right number of spaces. It´s our
understanding that OJB should take care of the the object.attribute ==>
RDBMS mapping without requiring the programmer to take care of things like
this.

We´ve tested setting criteria to SQL string and it works, but again, this
approach requires us  to "wire"  some database specific things like field
names to our java code.

Criteria mycrit = new Criteria();
crit.addSql("FIRST_NAME = 'Gustavo'");
Query query = new QueryByCriteria(Usuario.class, crit);
myemp = (Emp) broker.getObjectByQuery(query);

Are we missing something here? Any ideas?

Thanks in advance

Gustavo Faerman
Buenos Aires,
Argentina.

Re: OJB criteria fails with Oracle char field right filled with spaces

[Jakob Braeuchi <jb...@gmx.ch>]

hi gustavo,

why can't you use trim or an equivalent oracle-function to get rid of 
the spaces ?

mycrit.addEqualTo("trim(firstName)", "Gustavo");


hth
jakob




gfaerman@fys.com.ar wrote:

>Hi all,
>
>We are using OJB, Oracle 9i. When trying to retrieve an object by criteria,
>OJB is not finding the requested object since the CHAR fields are right
>filled with spaces.
>
>Just an example:
>
>oracle table emp
>field name: FIRST_NAME(CHAR10)
>
>object Emp, attribute firstName
>
>Object/Record to to retrieve should have firstName = Gustavo (this was
>stored using OJB; Oracle fills with spaces to the right of the user
>inserted string in a CHAR field)
>
>This will work: OJB will return the object if criteria sets emp.firstName
>to "Gustavo   " (note the 3 spaces at the right):
>Criteria mycrit = new Criteria();
>mycrit.addEqualTo("firstName", "Gustavo   ");
>Query myquery = new QueryByCriteria(emp.class, mycrit);
>myemp= (Emp)broker.getObjectByQuery(myquery);
>
>This will not work: OJB will fail to return the object if criteria is set
>to emp.firstName = "Gustavo" (no spaces).
>Criteria mycrit = new Criteria();
>mycrit.addEqualTo("firstName", "Gustavo");
>Query myquery = new QueryByCriteria(emp.class, mycrit);
>myemp= (Emp)broker.getObjectByQuery(myquery);
>
>Problem here is that the java coder has to know the database field lenght
>in order to fill the string with the right number of spaces. It´s our
>understanding that OJB should take care of the the object.attribute ==>
>RDBMS mapping without requiring the programmer to take care of things like
>this.
>
>We´ve tested setting criteria to SQL string and it works, but again, this
>approach requires us  to "wire"  some database specific things like field
>names to our java code.
>
>Criteria mycrit = new Criteria();
>crit.addSql("FIRST_NAME = 'Gustavo'");
>Query query = new QueryByCriteria(Usuario.class, crit);
>myemp = (Emp) broker.getObjectByQuery(query);
>
>Are we missing something here? Any ideas?
>
>Thanks in advance
>
>Gustavo Faerman
>Buenos Aires,
>Argentina.
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>  
>