[JBoss-dev] [ jboss-Bugs-887832 ] finder does not find existing data

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 454-5900

Bugs item #887832, was opened at 2004-01-30 21:56
Message generated for change (Comment added) made by lafr
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=376685&aid=887832&group_id=22866

Category: JBossCMP
Group: v3.2
Status: Open
Resolution: None
Priority: 5
Submitted By: Frank Langelage (lafr)
Assigned to: Alexey Loubyansky (loubyansky)
Summary: finder does not find existing data

Initial Comment:
Bean MbiMenue with this finder-declaration processed by
XDoclet:
 * @ejb.finder signature="java.util.Collection
findByUsernameSprachKbez (java.lang.String username,
java.lang.String sprach_kbez)"
 *   view-type="local"
 *   query="SELECT OBJECT(o) FROM MbiMenue AS o WHERE
o.username = ?1 AND o.sprachKbez = ?2"
 *   result-type-mapping="Local"

This finder does not find data.
If I replace the variables ?1 and ?2 by constant values
the data is found.
I swichted on log level trace an saw, that the
variables are filled correctly, but they are filled as
jdbc-type VARCHAR.

But field username is defined
	 * 	column-name="username"
	 * 	sql-type="CHAR(8)"
	 * 	jdbc-type="CHAR"
and field sprach_kbez
	 * 	column-name="sprach_kbez"
	 * 	sql-type="CHAR(2)"
	 * 	jdbc-type="CHAR"

The table is created by jboss and the columns are of
the expected type.
If I switch the Oracle9i mapping for String from
VARCHAR to CHAR  the jdbc-type of the variables changes
from VARCHAR to CHAR but finder does not find anything.

System:
- current JBoss-3.2 branch
- JDK 1.4.2_03
- Solaris 9 on Sparc
- XDoclet 1.2
- Oracle 9i 9.2.0.4

----------------------------------------------------------------------

>Comment By: Frank Langelage (lafr)
Date: 2004-02-08 23:24

Message:
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.

----------------------------------------------------------------------

Comment By: Alexey Loubyansky (loubyansky)
Date: 2004-02-08 00:24

Message:
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.

----------------------------------------------------------------------

Comment By: Frank Langelage (lafr)
Date: 2004-02-07 19:53

Message:
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.

----------------------------------------------------------------------

Comment By: Alexey Loubyansky (loubyansky)
Date: 2004-02-05 16:28

Message:
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.

----------------------------------------------------------------------

Comment By: Frank Langelage (lafr)
Date: 2004-02-04 21:42

Message:
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.

----------------------------------------------------------------------

Comment By: Alexey Loubyansky (loubyansky)
Date: 2004-02-04 03:11

Message:
Logged In: YES 
user_id=543482

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

----------------------------------------------------------------------

Comment By: Frank Langelage (lafr)
Date: 2004-02-03 23:34

Message:
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
.....

----------------------------------------------------------------------

Comment By: Alexey Loubyansky (loubyansky)
Date: 2004-02-03 13:59

Message:
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();
...

----------------------------------------------------------------------

Comment By: Frank Langelage (lafr)
Date: 2004-02-03 08:11

Message:
Logged In: YES 
user_id=300021

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

----------------------------------------------------------------------

Comment By: Alexey Loubyansky (loubyansky)
Date: 2004-02-03 02:00

Message:
Logged In: YES 
user_id=543482

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

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=376685&aid=887832&group_id=22866