4 Replies Latest reply on May 3, 2004 4:26 AM by chris_pollentier

    mysql ejb-ql query with collection comparison

    chris_pollentier Newbie

      Hi,

      We are moving our project from a postgres database to a mysql database.
      I encountered ad problem with one of our finders:

      There are two tables: 'Kp' and 'scanner'. The scanner table has a foreign key to the Kp table. I defined a one-to-many relation between scanner to kp

      I defined the following ejb-ql to get all Kp records with a scanner record pointing to it:

      SELECT DISTINCT OBJECT(o) FROM Kp o WHERE o.countryId = ?1 AND o.scanners IS NOT EMPTY

      When I trace the SQL statements of the cmp engine, I see the following in the log file:

      2004-04-29 11:36:58,695 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.Kp#findByCountryIdAndHasScanner] Executing SQL: SELECT DISTINCT t0_o.kp_pk FROM kp t0_o WHERE (t0_o.country_fk = ? AND t1_o_scanners.scanner_pk IS NOT NULL)
      2004-04-29 11:36:58,696 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.Kp#findByCountryIdAndHasScanner] Find failed
      java.sql.SQLException: General error, message from server: "Unknown table 't1_o_scanners' in where clause"
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
      at com.mysql.jdbc.Connection.execSQL(Connection.java:1874)
      at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1538)


      In the SQL query that is executed, the table 'scanner' is missing in the FROM part.

      Does anyone know if this a bug related to mysql (it worked fine with postgres), or is there an error in my EJQ-QL query ?

      jbosscmp-jdbc.xml snippet:

      <ejb-relation>
      <ejb-relation-name>kp-cmp20-scanner-cmp20</ejb-relation-name>
      <foreign-key-mapping/>

      <ejb-relationship-role>
      <ejb-relationship-role-name>scanner-cmp20-has-kp-cmp20</ejb-relationship-role-name>
      <fk-constraint>true</fk-constraint>
      <key-fields/>

      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>kp-cmp20-has-scanner-cmp20</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>kpId</field-name>
      <column-name>kp_fk</column-name>
      </key-field>
      </key-fields>

      </ejb-relationship-role>
      </ejb-relation>

      <ejb-relation>
      <ejb-relation-name>parcel-cmp20-barcode-cmp20</ejb-relation-name>
      <foreign-key-mapping/>

      <ejb-relationship-role>
      <ejb-relationship-role-name>barcode-cmp20-has-parcel-cmp20</ejb-relationship-role-name>
      <fk-constraint>true</fk-constraint>
      <key-fields/>

      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>parcel-cmp20-has-barcode-cmp20</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>parcelId</field-name>
      <column-name>parcel_fk</column-name>
      </key-field>
      </key-fields>

      </ejb-relationship-role>
      </ejb-relation>


      ejb-jar snippet:

      <ejb-relation >
      <ejb-relation-name>parcel-cmp20-barcode-cmp20</ejb-relation-name>

      <ejb-relationship-role >
      <ejb-relationship-role-name>barcode-cmp20-has-parcel-cmp20</ejb-relationship-role-name>
      Many
      <relationship-role-source >
      <ejb-name>Barcode</ejb-name>
      </relationship-role-source>
      <cmr-field >
      <cmr-field-name>parcel</cmr-field-name>
      </cmr-field>
      </ejb-relationship-role>

      <ejb-relationship-role >
      <ejb-relationship-role-name>parcel-cmp20-has-barcode-cmp20</ejb-relationship-role-name>
      One
      <relationship-role-source >
      <ejb-name>Parcel</ejb-name>
      </relationship-role-source>
      <cmr-field >
      <cmr-field-name>barcodes</cmr-field-name>
      <cmr-field-type>java.util.Collection</cmr-field-type>
      </cmr-field>
      </ejb-relationship-role>

      </ejb-relation>



      <ejb-relation >
      <ejb-relation-name>kp-cmp20-scanner-cmp20</ejb-relation-name>

      <ejb-relationship-role >
      <ejb-relationship-role-name>scanner-cmp20-has-kp-cmp20</ejb-relationship-role-name>
      Many
      <relationship-role-source >
      <ejb-name>Scanner</ejb-name>
      </relationship-role-source>
      <cmr-field >
      <cmr-field-name>kp</cmr-field-name>
      </cmr-field>
      </ejb-relationship-role>

      <ejb-relationship-role >
      <ejb-relationship-role-name>kp-cmp20-has-scanner-cmp20</ejb-relationship-role-name>
      One
      <relationship-role-source >
      <ejb-name>Kp</ejb-name>
      </relationship-role-source>
      <cmr-field >
      <cmr-field-name>scanners</cmr-field-name>
      <cmr-field-type>java.util.Collection</cmr-field-type>
      </cmr-field>
      </ejb-relationship-role>

      </ejb-relation>