EJB QL and In expression

[htaepper]

Configuration: JBoss 3.0 , jdk 1.4.0-beta3 ( Windows ), Oracle 8.1.6

We tried to execute following finder-method to prevent calling ten times findByPrimaryKey()


Collection findByKeys( java.lang.String pk1,
java.lang.String pk2,
java.lang.String pk3,
java.lang.String pk4,
java.lang.String pk5,
java.lang.String pk6,
java.lang.String pk7,
java.lang.String pk8,
java.lang.String,pk9,
java.lang.String pk10)


<query-method>
<method-name>findByKeys</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql><![CDATA[ SELECT OBJECT(o) FROM Fir o WHERE o.pK IN ( ?1,?2,?3,?4,?5,?6,?7,?8,?9,?10 ) ]]></ejb-ql>



It seems that the query returns all rows of the table.
Anyone encountered similiar problems and has a solution ?

[davidjencks]

Can you do this in plain oracle sql?? I never could get anything resembling this to work.

Try

o.Pk = ?1 or o.Pk = ?2 or ...

[htaepper]

In plain Oracle you can use an IN-clause. You also can use an IN-clause whith JDBC on various Databases i. e. ORACLE, MS-SQL, MS-ACCESS. Whith a testversion of BEA Weblogic 6.1 it works fine too.
As a workaround the suggestion of an joined query by OR will work. Thanks.

[dsundstrom]

What sql is the engine generating? If you turn on debugging in the jbosscmp-jdbc.xml file, JBossCMP will log (debug) the sql generated from the EJB-QL.

-dain

[htaepper]

The log file contains following entries:
[2001-12-05 12:04:51,248,CMP,DEBUG] EQL-QL: SELECT OBJECT(o) FROM Fir o WHERE o.pk in ( ?1, ?2 , ?3 , ?4 , ?5 ,?6 ,?7 ,?8 ,?9 ,?10 )
[2001-12-05 12:04:51,418,CMP,DEBUG] Assembly: []SELECT/OBJECT/(/o/)/FROM/Fir/o^WHERE/o/./pk/in/(/?1/,/?2/,/?3/,/?4/,/?5/,/?6/,/?7/,/?8/,/?9/,/?10/)
[2001-12-05 12:04:51,418,CMP,DEBUG] findByKeys SQL: SELECT t1_o.pk FROM Fir t1_o



...

[2001-12-05 11:39:46,068,CMP,DEBUG] findByKeys command executing: SELECT t1_o.pk FROM Fir t1_o
[2001-12-05 11:39:46,068,CMP,DEBUG] Get result: index=1, javaType=java.lang.String, S value=1006954324229Fir-6878221951898700712
[2001-12-05 11:39:46,068,CMP,DEBUG] Get result: index=1, javaType=java.lang.String, S value=1006954354192Fir3300652343385115741
[2001-12-05 11:39:46,068,CMP,DEBUG] Get result: index=1, javaType=java.lang.String, S value=1006954394095Fir-3951422115451076574
[2001-12-05 11:39:46,068,CMP,DEBUG] Get result: index=1, javaType=java.lang.String, S value=1006954452113Fir2256422688763234438

Changing to the or-clause creates following :

[2001-12-05 11:58:13,772,CMP,DEBUG] EQL-QL: SELECT OBJECT(o) FROM Fir o WHERE o.pk = ?1 or o.pk = ?2 or o.pk =?3 or o.pk =?4 or o.pk =?5 or o.pk =?6 or o.pk =?7 or o.pk =?8 or o.pk =?9 or o.pk =?10
[2001-12-05 11:58:14,533,CMP,DEBUG] Assembly: []SELECT/OBJECT/(/o/)/FROM/Fir/o/WHERE/o/./pk/=/?1/or/o/./pk/=/?2/or/o/./pk/=/?3/or/o/./pk/=/?4/or/o/./pk/=/?5/or/o/./pk/=/?6/or/o/./pk/=/?7/or/o/./pk/=/?8/or/o/./pk/=/?9/or/o/./pk/=/?10^
[2001-12-05 11:58:14,533,CMP,DEBUG] findByKeys SQL: SELECT t1_o.pk FROM Fir t1_o WHERE t1_o.pk = ? or t1_o.pk = ? or t1_o.pk = ? or t1_o.pk = ? or t1_o.pk = ? or t1_o.pk = ? or t1_o.pk = ? or t1_o.pk = ? or t1_o.pk = ? or t1_o.pk = ?

[dsundstrom]

Ok, this is obviously a bug. I'll look into it.

[dsundstrom]

Can you post a bug report at sourceforge?