Bugs item #887832, was opened at 2004-01-30 22:56
Message generated for change (Comment added) made by loubyansky
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=376685&aid=887832&group_id=22866
Category: JBossCMP
Group: v3.2
>Status: Closed
>Resolution: Fixed
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: Alexey Loubyansky (loubyansky)
Date: 2004-07-13 22:26
Message:
Logged In: YES
user_id=543482
Ok, it's finally added.
http://www.jboss.org/wiki/Wiki.jsp?page=CMPParamSettersResultReaders
----------------------------------------------------------------------
Comment By: Frank Langelage (lafr)
Date: 2004-02-09 00: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 01: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 20: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 17: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 22: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 04: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-04 00: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 14: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 09: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 03: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
-------------------------------------------------------
This SF.Net email sponsored by Black Hat Briefings & Training.
Attend Black Hat Briefings & Training, Las Vegas July 24-29 -
digital self defense, top technical experts, no vendor pitches,
unmatched networking opportunities. Visit www.blackhat.com
_______________________________________________
JBoss-Development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development
