finder does not find existing data

Logged In: YES
user_id=543482

If you try the same SQL with direct JDBC from JBoss will it
return the data?

Logged In: YES
user_id=300021

Do you have an example for this ?
Perhaps a testcase somewhere in the testsuite ?

Logged In: YES
user_id=543482

Assume the datasource used for entity beans is java:/DefaultDS:

Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:/DefaultDS");

Connection con = ds.getConnection();
PreparedStatement ps = con.prepareStatement(sqlQuery);
ResultSet rs = ps.executeQuery();
...

Logged In: YES
user_id=300021

I created a this method on a stateless session bean named Mask:
/**
* @ejb.interface-method
*/
public void testFinder() {
try {
javax.naming.Context ctx = new
javax.naming.InitialContext();
javax.sql.DataSource ds =
(javax.sql.DataSource)ctx.lookup("java:/OracleDS");
java.sql.Connection con = ds.getConnection();

// first try with PreparedStatement
String sqlQuery = "select * from mbi_menue where
username = ? and sprach_kbez = ?";
logger.info( "executing '" + sqlQuery + "'" );
java.sql.PreparedStatement ps =
con.prepareStatement(sqlQuery);
ps.setString( 1, "mbi" );
ps.setString( 2, "de" );
java.sql.ResultSet rs = ps.executeQuery();
int i = 0;
while ( rs.next() ) {
i++;
logger.info("Zeile: " + i);
}
rs.close();
ps.close();

// second try with normal Statement
sqlQuery = "select * from mbi_menue where
username = 'mbi' and sprach_kbez = 'de'";
logger.info( "executing '" + sqlQuery + "'" );
java.sql.Statement s = con.createStatement();
rs = s.executeQuery( sqlQuery );
i = 0;
while ( rs.next() ) {
i++;
logger.info("Zeile: " + i);
}
rs.close();
s.close();

con.close();
}
catch( java.sql.SQLException sqle ) {
logger.error( sqle.toString() );
}
catch( javax.naming.NamingException ne ) {
logger.error( ne.toString() );
}
}

and got this result:
23:30:25,821 INFO [Mask.testFinder] executing 'select *
from mbi_menue where username = ? and sprach_kbez = ?'
23:30:25,966 INFO [Mask.testFinder] executing 'select *
from mbi_menue where username = 'mbi' and sprach_kbez = 'de''
23:30:26,001 INFO [Mask.testFinder] Zeile: 1
23:30:26,004 INFO [Mask.testFinder] Zeile: 2
23:30:26,006 INFO [Mask.testFinder] Zeile: 3
23:30:26,008 INFO [Mask.testFinder] Zeile: 4
23:30:26,035 INFO [Mask.testFinder] Zeile: 5
23:30:26,037 INFO [Mask.testFinder] Zeile: 6
23:30:26,040 INFO [Mask.testFinder] Zeile: 7
23:30:26,042 INFO [Mask.testFinder] Zeile: 8
23:30:26,044 INFO [Mask.testFinder] Zeile: 9
23:30:26,046 INFO [Mask.testFinder] Zeile: 10
.....

Logged In: YES
user_id=543482

It does not look like a JBoss problem. What driver are you
using? Can you try other ones?

Logged In: YES
user_id=300021

I did some more tests today and the cause for this behaviour.
First I tried with an informix Datasource. The finder found
the data.
Then I tried different oracle versions (8.1.7, 9.2.0.1,
10.1.0.2.0).
No data found.

If I fill up the literal "mbi" with blanks up to column
length (CHAR(8)) the finder finds data with OracleDS.
Oracle does distinguish exactly between VARCHAR and CHAR.
PreparedStatement.setString() with CHAR-column does not work.
OraclePreparedStatement has method setFixedCHAR for this
purpose.
If I change some lines in my testcase to
org.jboss.resource.adapter.jdbc.WrappedPreparedStatement
wps =
(org.jboss.resource.adapter.jdbc.WrappedPreparedStatement)ps;

((oracle.jdbc.driver.OraclePreparedStatement)wps.getUnderlyingStatement()).setFixedCHAR(
1, "mbi" );

((oracle.jdbc.driver.OraclePreparedStatement)wps.getUnderlyingStatement()).setFixedCHAR(
2, "de" );

also the PreparedStatement finds the data.

Logged In: YES
user_id=543482

Thanks for the investigations.
In fact we use ps.setObject(index, value, jdbcType) so you
have complete control over JDBC type in the range of the
standard. If you can't do it with this method it is not a
JBoss bug.
To workaround this we could make plugable result set setters
and getters providing default ones and letting users to
provide their custom.

Logged In: YES
user_id=300021

OK, the only JBoss-Bug I see is, that setting the values in
the preparedStatement for the finder is done with type
VARCHAR instead of CHAR which ist the jdbcType in
jbosscmp-jdbc.xml for the bean-cols.
See my original post for this.

The problem with fixed-length-char an oracle seem to be
volitional.
I found documents on the oracle site and discussion threads
for this.
I agree with the posters there that oracle won't be willing
to change the behaviour. So we will need to find a
workaround for this.
I will submit an RFE for this.
Not using fixed-length char is no a solution for me. I
can't change the database schema for this table because it
is a table from a legacy app and also used there.

Logged In: YES
user_id=543482

We have org.jboss.ejb.plugins.cmp.jdbc.Mapper interface.
Check this
https://sourceforge.net/tracker/?func=detail&aid=782978&group_id=22866&atid=381174

I think if I add it could be used per cmp-field, you could
append or trim whitespeces when setting parameters or
reading result sets.

Logged In: YES
user_id=300021

I think your propsal would probably lead to different ear's
which depend on the datasource used.
I would prefer a solution within the datatype mapping in
standardjbosscmp-jdbc.xml or even an enhancement in the
datasource definition.
The problematic behaviour of the oracle jdbc driver
shouldn't be handled on bean level.

Logged In: YES
user_id=543482

Ok, it's finally added.
http://www.jboss.org/wiki/Wiki.jsp?page=CMPParamSettersResultReaders