12 Replies Latest reply on Jul 18, 2003 2:29 AM by thebluebanana

    Failing to properly parse QL finder methods

    thebluebanana

      Warning! New user alert. (However, I didn't think this belonged in the Newbies section.)

      I've recently started learning about EJBs etc., and have come across the following poser. I'm trying to create my first CMP-based entity bean, and have reached a point from where I can't proceed.

      Running my client, I get the following (for any of the finder methods):
      javax.ejb.FinderException: Find failed: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

      Looking at the log files, I can see that the QL strings passed to JBoss seem to be incorrectly parsed, causing the Access ODBC to fail. An example:

      @jboss.query signature = "java.util.Collection findByLastName(java.lang.String lastName)"
      query = "SELECT OBJECT(pers) FROM Person AS pers WHERE pers.lastName LIKE ?1"

      becomes:

      "SELECT FROM Person t0_p WHERE t0_p.lastName LIKE ?"

      Yep, OBJECT() is translated to a space.

      The bean's jbosscmp-jdbc.xml file seems fine. I had to add entries to standardjbosscmp-jdbc.xml, though, for MS Access. They are what I suspect, but...?

      I'm using Eclipse w. JBossIde, and MS Access (for the moment).

      I hope my problem is clear, and thanks for any help.

        • 1. Re: Failing to properly parse QL finder methods

          Don't use Access since there are free alternatives (hypersonic that came with your distribution, PostgreSQL etc). Also please include the deployment descriptors xdoclet creates any time you have a configuration problem.

          Start with checking that you've correctly declared an abstract schema name for instance.

          • 2. Re: Failing to properly parse QL finder methods
            thebluebanana

            Thanks for replying, Juha.

            I've been advised to look at MySQL, and I will get around to looking at alternatives, post-haste. MS Access was handy, and I've seen a few claims of success with it in this forum.

            Deployment descriptors... well, here's "ejb-jar.xml":

            <?xml version="1.0" encoding="UTF-8"?>
            <!DOCTYPE ejb-jar PUBLIC "-//Sun Microsystems, Inc.//DTD Enterprise JavaBeans 2.0//EN" "http://java.sun.com/dtd/ejb-jar_2_0.dtd">

            <ejb-jar >

            <![CDATA[No Description.]]>
            <display-name>Generated by XDoclet</display-name>

            <enterprise-beans>

            <!-- Session Beans -->

            <![CDATA[EJB that accesses an entity Bean]]>
            <display-name>AddressBook EJB</display-name>

            <ejb-name>AddressBook</ejb-name>

            addressbook.interfaces.AddressBookHome
            addressbook.interfaces.AddressBook
            <ejb-class>addressbook.ejb.AddressBookBean</ejb-class>
            <session-type>Stateful</session-type>
            <transaction-type>Container</transaction-type>



            <!--
            To add session beans that you have deployment descriptor info for, add
            a file to your XDoclet merge directory called session-beans.xml that contains
            the markup for those beans.
            -->

            <!-- Entity Beans -->

            <![CDATA[EJB that personalises stuff in a Big Brother manner, via CMP!]]>
            <display-name>Person EJB</display-name>

            <ejb-name>Person</ejb-name>

            addressbook.interfaces.PersonHome
            addressbook.interfaces.Person

            <ejb-class>addressbook.ejb.PersonBean</ejb-class>
            <persistence-type>Container</persistence-type>
            <prim-key-class>addressbook.ejb.PersonPK</prim-key-class>
            False
            <cmp-version>2.x</cmp-version>
            <abstract-schema-name>Person</abstract-schema-name>
            <cmp-field >
            <![CDATA[The primary key field.]]>
            <field-name>personId</field-name>
            </cmp-field>
            <cmp-field >
            <![CDATA[The first name field.]]>
            <field-name>firstName</field-name>
            </cmp-field>
            <cmp-field >
            <![CDATA[The middle name field.]]>
            <field-name>middleName</field-name>
            </cmp-field>
            <cmp-field >
            <![CDATA[The last name field.]]>
            <field-name>lastName</field-name>
            </cmp-field>
            <cmp-field >
            <![CDATA[The primary residential address field.]]>
            <field-name>address1</field-name>
            </cmp-field>
            <cmp-field >
            <![CDATA[The secondary residential address field.]]>
            <field-name>address2</field-name>
            </cmp-field>
            <cmp-field >
            <![CDATA[The residential city field.]]>
            <field-name>city</field-name>
            </cmp-field>
            <cmp-field >
            <![CDATA[The residential state field.]]>
            <field-name>state</field-name>
            </cmp-field>
            <cmp-field >
            <![CDATA[The post-code field.]]>
            <field-name>postCode</field-name>
            </cmp-field>
            <cmp-field >
            <![CDATA[The age field.]]>
            <field-name>age</field-name>
            </cmp-field>
            <cmp-field >
            <![CDATA[The home phone number field.]]>
            <field-name>homePhone</field-name>
            </cmp-field>
            <cmp-field >
            <![CDATA[The work phone number field.]]>
            <field-name>workPhone</field-name>
            </cmp-field>
            <cmp-field >
            <![CDATA[The mobile phone number field.]]>
            <field-name>mobilePhone</field-name>
            </cmp-field>

            <env-entry>
            <![CDATA[An env-var or alias for the database]]>
            <env-entry-name>DataSourceName</env-entry-name>
            <env-entry-type>java.lang.String</env-entry-type>
            <env-entry-value>java:/PersonDB</env-entry-value>
            </env-entry>

            <resource-ref >
            <res-ref-name>jdbc/PersonDB</res-ref-name>
            <res-type>javax.sql.DataSource</res-type>
            <res-auth>Container</res-auth>
            </resource-ref>


            <![CDATA[Find a/many Person object/s by the last name]]>
            <query-method>
            <method-name>findByLastName</method-name>
            <method-params>
            <method-param>java.lang.String</method-param>
            </method-params>
            </query-method>
            <ejb-ql><![CDATA[SELECT DISTINCT OBJECT(p) FROM Person p]]></ejb-ql>


            <![CDATA[Find a/many Person object/s by the first and last name]]>
            <query-method>
            <method-name>findByFirstAndLastName</method-name>
            <method-params>
            <method-param>java.lang.String</method-param>
            <method-param>java.lang.String</method-param>
            </method-params>
            </query-method>
            <ejb-ql><![CDATA[SELECT OBJECT(p) FROM Person p WHERE p.firstName]]></ejb-ql>

            <!-- Write a file named ejb-finders-PersonBean.xml if you want to define extra finders. -->


            <!--
            To add entity beans that you have deployment descriptor info for, add
            a file to your XDoclet merge directory called entity-beans.xml that contains
            the markup for those beans.
            -->

            <!-- Message Driven Beans -->
            <!--
            To add message driven beans that you have deployment descriptor info for, add
            a file to your XDoclet merge directory called message-driven-beans.xml that contains
            the <message-driven></message-driven> markup for those beans.
            -->

            </enterprise-beans>

            <!-- Relationships -->

            <!-- Assembly Descriptor -->
            <assembly-descriptor >
            <!--
            To add additional assembly descriptor info here, add a file to your
            XDoclet merge directory called assembly-descriptor.xml that contains
            the <assembly-descriptor></assembly-descriptor> markup.
            -->

            <!-- finder permissions -->

            <!-- finder permissions -->

            <method-permission >
            <![CDATA[description not supported yet by ejbdoclet]]>


            <ejb-name>Person</ejb-name>
            <method-name>findAll</method-name>
            <method-params>
            </method-params>

            </method-permission>

            <method-permission >
            <![CDATA[description not supported yet by ejbdoclet]]>


            <ejb-name>Person</ejb-name>
            <method-name>findByLastName</method-name>
            <method-params>
            <method-param>java.lang.String</method-param>
            </method-params>

            </method-permission>

            <method-permission >
            <![CDATA[description not supported yet by ejbdoclet]]>


            <ejb-name>Person</ejb-name>
            <method-name>findByFirstAndLastName</method-name>
            <method-params>
            <method-param>java.lang.String</method-param>
            <method-param>java.lang.String</method-param>
            </method-params>

            </method-permission>

            <!-- transactions -->
            <container-transaction >

            <ejb-name>Person</ejb-name>
            <method-name>*</method-name>

            <trans-attribute>Required</trans-attribute>
            </container-transaction>

            <!-- finder transactions -->
            </assembly-descriptor>

            </ejb-jar>


            "jboss.xml" follows:

            <?xml version="1.0" encoding="UTF-8"?>
            <!DOCTYPE jboss PUBLIC "-//JBoss//DTD JBOSS 3.0//EN" "http://www.jboss.org/j2ee/dtd/jboss_3_0.dtd">



            <enterprise-beans>

            <!--
            To add beans that you have deployment descriptor info for, add
            a file to your XDoclet merge directory called jboss-beans.xml that contains
            the , and <message-driven></message-driven>
            markup for those beans.
            -->


            <ejb-name>Person</ejb-name>
            <jndi-name>ejb/addressbook/People</jndi-name>
            <resource-ref>
            <res-ref-name>jdbc/PersonDB</res-ref-name>
            <jndi-name>java:/PersonDB</jndi-name>
            </resource-ref>




            <ejb-name>AddressBook</ejb-name>
            <jndi-name>ejb/addressbook/AddressBook</jndi-name>



            </enterprise-beans>

            <resource-managers>
            </resource-managers>




            and, finally, "jbosscmp-jdbc.xml":

            <?xml version="1.0" encoding="UTF-8"?>
            <!DOCTYPE jbosscmp-jdbc PUBLIC "-//JBoss//DTD JBOSSCMP-JDBC 3.0//EN" "http://www.jboss.org/j2ee/dtd/jbosscmp-jdbc_3_0.dtd">

            <jbosscmp-jdbc>

            java:/PersonDB
            <datasource-mapping>MS Access</datasource-mapping>


            <enterprise-beans>

            <!--
            To add beans that you have deployment descriptor info for, add
            a file to your XDoclet merge directory called jbosscmp-jdbc-beans.xml
            that contains the markup for those beans.
            -->


            <ejb-name>Person</ejb-name>
            <create-table>true</create-table>
            <row-locking>true</row-locking>
            <pk-constraint>false</pk-constraint>

            <cmp-field>
            <field-name>personId</field-name>

            </cmp-field>
            <cmp-field>
            <field-name>firstName</field-name>

            </cmp-field>
            <cmp-field>
            <field-name>middleName</field-name>

            </cmp-field>
            <cmp-field>
            <field-name>lastName</field-name>

            </cmp-field>
            <cmp-field>
            <field-name>address1</field-name>

            </cmp-field>
            <cmp-field>
            <field-name>address2</field-name>

            </cmp-field>
            <cmp-field>
            <field-name>city</field-name>

            </cmp-field>
            <cmp-field>
            <field-name>state</field-name>

            </cmp-field>
            <cmp-field>
            <field-name>postCode</field-name>

            </cmp-field>
            <cmp-field>
            <field-name>age</field-name>

            </cmp-field>
            <cmp-field>
            <field-name>homePhone</field-name>

            </cmp-field>
            <cmp-field>
            <field-name>workPhone</field-name>

            </cmp-field>
            <cmp-field>
            <field-name>mobilePhone</field-name>

            </cmp-field>


            <query-method>
            <method-name>findByLastName</method-name>
            <method-params>
            <method-param>java.lang.String</method-param>
            </method-params>
            </query-method>
            <jboss-ql><![CDATA[SELECT OBJECT(pers) FROM Person AS pers WHERE pers.lastName LIKE '?1']]></jboss-ql>



            <query-method>
            <method-name>findByFirstAndLastName</method-name>
            <method-params>
            <method-param>java.lang.String</method-param>
            <method-param>java.lang.String</method-param>
            </method-params>
            </query-method>
            <jboss-ql><![CDATA[SELECT OBJECT(p) FROM Person p WHERE p.firstName LIKE ?1 AND p.lastName LIKE ?2]]></jboss-ql>





            </enterprise-beans>

            </jbosscmp-jdbc>


            A schema is declared (Person), correctly, I hope!

            Cheers.


            • 3. Re: Failing to properly parse QL finder methods

              Can't spot anything obvious. Try deploying with the default Hypersonic DB and see if it works.

              • 4. Re: Failing to properly parse QL finder methods
                thebluebanana

                Well, I installed mySQL, and the same result occurred - OBJECT() is not being interpreted, and the resultant SQL request is declined. I get the feeling I should re-install JBoss (v3.2.1, by-the-way).

                Juha, I'll try Hypersonic, but I'll go out on a limb and suggest it'll make no difference. Can't hurt to try, though.

                Ta.

                • 5. Re: Failing to properly parse QL finder methods
                  thebluebanana

                  As an aside of experimentation, I tried returning a single field, rather than an entire object. The QL for the finder method is at least correct, and an appropriate number of beans is found (except when using MS Access!!!)... however, any attempt to iterate through the collection fails when any getXXX() accessor is called. Again, it's a matter of incorrectly supplied EJB-QL, in the WHERE clause... it is now empty!

                  I'm not having much of an introduction to the world of EJB! Thanks for having taken the time to consider my problem, anyway, Juha.

                  • 6. Re: Failing to properly parse QL finder methods

                    what warnings do you get on your server.log when you deploy your bean?

                    • 7. Re: Failing to properly parse QL finder methods
                      thebluebanana

                      I've just redeployed the bean.

                      The only warning in server.log is the following:

                      2003-07-16 08:44:19,318 WARN [org.jboss.deployment.DeploymentInfo] Could not delete directory file:/D:/java/jboss-3.2.1_tomcat-4.1.24/server/all/tmp/deploy/server/all/deploy/AddressBookEJB.jar/57.AddressBookEJB.jar restart will delete it

                      It is surrounded by volumes of INFO and DEBUG statements, which I think will be of no interest, because it is not a severe warning, as such (I hope!)

                      However, the following is the corresponding console output in Eclipse:

                      08:44:19,188 INFO [MainDeployer] Undeploying file:/D:/java/jboss-3.2.1_tomcat-4.1.24/server/all/deploy/AddressBookEJB.jar
                      08:44:19,188 INFO [EjbModule] Stopping
                      08:44:19,188 INFO [StatefulSessionContainer] Stopping
                      08:44:19,188 INFO [StatefulSessionInstancePool] Stopping
                      08:44:19,188 INFO [StatefulSessionInstancePool] Stopped
                      08:44:19,188 INFO [StatefulSessionFilePersistenceManager] Stopping
                      08:44:19,188 INFO [StatefulSessionFilePersistenceManager] Stopped
                      08:44:19,188 INFO [StatefulSessionContainer] Stopped
                      08:44:19,188 INFO [EntityContainer] Stopping
                      08:44:19,188 INFO [EntityInstancePool] Stopping
                      08:44:19,188 INFO [EntityInstancePool] Stopped
                      08:44:19,188 INFO [EntityContainer] Stopped
                      08:44:19,188 INFO [EjbModule] Stopped
                      08:44:19,188 INFO [EjbModule] Destroying
                      08:44:19,208 INFO [StatefulSessionContainer] Destroying
                      08:44:19,208 INFO [StatefulSessionInstancePool] Destroying
                      08:44:19,208 INFO [StatefulSessionInstancePool] Destroyed
                      08:44:19,208 INFO [StatefulSessionFilePersistenceManager] Destroying
                      08:44:19,258 INFO [StatefulSessionFilePersistenceManager] Destroyed
                      08:44:19,258 INFO [StatefulSessionContainer] Destroyed
                      08:44:19,258 INFO [EntityContainer] Destroying
                      08:44:19,258 INFO [EntityInstancePool] Destroying
                      08:44:19,258 INFO [EntityInstancePool] Destroyed
                      08:44:19,258 INFO [EntityContainer] Destroyed
                      08:44:19,268 INFO [EjbModule] Destroyed
                      08:44:19,268 INFO [EJBModule] destroy(), remove EJB-Module: jboss.management.local:J2EEApplication=null,J2EEServer=Local,j2eeType=EJBModule,name=AddressBookEJB.jar
                      08:44:19,318 WARN [DeploymentInfo] Could not delete directory file:/D:/java/jboss-3.2.1_tomcat-4.1.24/server/all/tmp/deploy/server/all/deploy/AddressBookEJB.jar/57.AddressBookEJB.jar restart will delete it
                      08:44:19,318 INFO [MainDeployer] Undeployed file:/D:/java/jboss-3.2.1_tomcat-4.1.24/server/all/deploy/AddressBookEJB.jar
                      08:44:19,318 INFO [MainDeployer] Starting deployment of package: file:/D:/java/jboss-3.2.1_tomcat-4.1.24/server/all/deploy/AddressBookEJB.jar
                      08:44:19,919 INFO [EjbModule] Creating
                      08:44:19,929 INFO [EjbModule] Deploying Person
                      08:44:19,949 INFO [EjbModule] Deploying AddressBook
                      08:44:19,969 INFO [EntityContainer] Creating
                      08:44:19,989 INFO [EntityInstancePool] Creating
                      08:44:19,989 INFO [EntityInstancePool] Created
                      08:44:19,999 INFO [EntityContainer] Created
                      08:44:20,009 INFO [StatefulSessionContainer] Creating
                      08:44:20,009 INFO [StatefulSessionInstancePool] Creating
                      08:44:20,009 INFO [StatefulSessionInstancePool] Created
                      08:44:20,009 INFO [StatefulSessionFilePersistenceManager] Creating
                      08:44:20,019 INFO [StatefulSessionFilePersistenceManager] Created
                      08:44:20,019 INFO [StatefulSessionContainer] Created
                      08:44:20,019 INFO [EjbModule] Created
                      08:44:20,029 INFO [EjbModule] Starting
                      08:44:20,029 INFO [EntityContainer] Starting
                      08:44:20,530 INFO [Person] Table 'Person' already exists
                      08:44:20,540 INFO [EntityInstancePool] Starting
                      08:44:20,540 INFO [EntityInstancePool] Started
                      08:44:20,540 INFO [EntityContainer] Started
                      08:44:20,540 INFO [StatefulSessionContainer] Starting
                      08:44:20,570 INFO [StatefulSessionInstancePool] Starting
                      08:44:20,570 INFO [StatefulSessionInstancePool] Started
                      08:44:20,570 INFO [StatefulSessionFilePersistenceManager] Starting
                      08:44:20,570 INFO [StatefulSessionFilePersistenceManager] Started
                      08:44:20,570 INFO [StatefulSessionContainer] Started
                      08:44:20,570 INFO [EjbModule] Started
                      08:44:20,570 INFO [EJBDeployer] Deployed: file:/D:/java/jboss-3.2.1_tomcat-4.1.24/server/all/deploy/AddressBookEJB.jar
                      08:44:20,620 INFO [MainDeployer] Deployed package: file:/D:/java/jboss-3.2.1_tomcat-4.1.24/server/all/deploy/AddressBookEJB.jar

                      As far as I can tell, there are no faults.

                      I'm perplexed!

                      • 8. Re: Failing to properly parse QL finder methods

                        And the field names in your PersonPK match the CMP field names in the Person entity?

                        • 9. Re: Failing to properly parse QL finder methods
                          thebluebanana

                          Yep.

                          'PersonPK' declares just one member, an integer containing a unique identifier.

                          'Person' (implicitly, since v2 of CMP) declares many members related to a person (name, address, etc.), as well as an integer identifier.

                          OK, the two identifier fields weren't quite the same (one had an upper-case D), but I've remedied that, and redeployed it, to no avail.

                          Hmmm. The WHERE clause remains empty.

                          (My code is heavily based on an example in "Using Java 2 - Enterprise Edition - Special Edition" by Mark Wutka, Que, if that's of use.)

                          Juha, am I correct in suggesting that the customisation of different DB SQL implementations is specified in the <type-mappings> section of standardjbosscmp-jdbc.xml? Apart from what I added to that file in an attempt to gain MS Access support, I'm using a standard implementation of it.

                          • 10. Re: Failing to properly parse QL finder methods

                            > Juha, am I correct in suggesting that the
                            > customisation of different DB SQL implementations is
                            > specified in the <type-mappings> section of
                            > standardjbosscmp-jdbc.xml?

                            Yes that is correct.

                            I'm running out of ideas where the error might be short of being able to deploy the beans myself. I know it is of no consolidation to you but I got a very similar query running ok for me.

                             <jboss-ql><![CDATA[
                             SELECT OBJECT(a)
                             FROM AccountTable AS a
                             WHERE a.name LIKE ?1]]>
                             </jboss-ql>
                            



                            • 11. Re: Failing to properly parse QL finder methods
                              theravednh

                              Juha,

                              I've just had exactly the same problem (3.0.5 & MYSQL), and yes, it turned out that one of my developers had renamed the PK fieldname. Renaming it back to match the pk field on the ejb fixed the problem.

                              So

                              a) a big thank you for reminding me of that naming dependancy

                              b) TheBlueBanana - do look at the naming of the field in your primary key class. It is almost certainly the cause of your problem.

                              Cheers
                              /david

                              • 12. Re: Failing to properly parse QL finder methods
                                thebluebanana

                                I checked again, and they do match. I can't understand it.

                                Anyone willing to import an Eclipse project to aid this novice!?!