3 Replies Latest reply on May 30, 2005 1:07 AM by Ami Desai

    Sql not generating properly

    Guillermo Guisado Newbie

      Hi:
      I have jboss 4.0.0 not generating proper sql for this bean:
      ejb-jar:

      <entity>
       <display-name>BondStrips</display-name>
       <ejb-name>BondStripEJB</ejb-name>
       <abstract-schema-name>BondStrips</abstract-schema-name>
       <home>com.db.gto.cio.gm.it.madrid.dbsales.ejbs.BondStripHomeRemote</home>
       <remote>com.db.gto.cio.gm.it.madrid.dbsales.ejbs.BondStripRemote</remote>
       <ejb-class>com.db.gto.cio.gm.it.madrid.dbsales.ejbs.BondStripBean</ejb-class>
       <persistence-type>Container</persistence-type>
       <prim-key-class>com.db.gto.cio.gm.it.madrid.dbsales.ejbs.BondStripPK</prim-key-class>
       <reentrant>False</reentrant>
       <cmp-version>2.x</cmp-version>
       <cmp-field><field-name>bondsId</field-name></cmp-field>
       <cmp-field><field-name>bondsIdStrip</field-name></cmp-field>
       <cmp-field><field-name>stripType</field-name></cmp-field>
       <query>
       <query-method>
       <method-name>findByBondsId</method-name>
       <method-params>
       <method-param>java.lang.Integer</method-param>
       </method-params>
       </query-method>
       <ejb-ql>
       <![CDATA[select object(e) from BondStrips as e where e.bondsId = ?1]]>
       </ejb-ql
       </query>
       </entity>

      jboss.xml:
      <entity>
       <ejb-name>BondStripEJB</ejb-name>
       <configuration-name>Standard CMP 2.x EntityBean</configuration-name>
       <jndi-name>XAdbsalesipaDS/BondStripEJB</jndi-name>
       <method-attributes>
       <method>
       <method-name>get*</method-name>
       <read-only>true</read-only>
       </method>
       </method-attributes>
       </entity>

      jbosscmp-jdbc.xml
      <entity>
       <ejb-name>BondStripEJB</ejb-name>
       <table-name>bondsstrip</table-name>
       </entity>
      


      Deployment of the bean does not give any error. but when I call the finder findByBondsId I get the following:
      2005-05-04 17:31:00,062 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCDeclaredSQLQuery.CouponPaymentEJB#findByBondsId] Executing SQL: SELECT , bondsId, couponDate FROM SINKINGTABLE WHERE bondsId = ? ORDER BY coupondate
      2005-05-04 17:31:00,073 ERROR [org.jboss.ejb.plugins.cmp.jdbc.JDBCDeclaredSQLQuery.CouponPaymentEJB#findByBondsId] Find failed
      java.sql.SQLException: ORA-00936: missing expression


      The debug of the deployment already shows some clues:
      2005-05-04 17:25:25,903 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.BondStripEJB] Insert Entity SQL: INSERT INTO BONDSSTRIP (bondsId, bondsIdStrip, stripType) VALUES (?, ?, ?)
      2005-05-04 17:25:25,904 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.BondStripEJB] Entity Exists SQL: SELECT COUNT(*) FROM BONDSSTRIP WHERE
      2005-05-04 17:25:25,905 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCommandFactory.BondStripEJB] entity-command: [commandName=default,commandClass=class org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand,attributes={}]
      2005-05-04 17:25:25,906 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCRemoveEntityCommand.BondStripEJB] Remove SQL: DELETE FROM BONDSSTRIP WHERE
      2005-05-04 17:25:25,921 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.BondStripEJB] Table not create as requested: BONDSSTRIP
      2005-05-04 17:25:25,922 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.BondStripEJB#findByPrimaryKey] SQL: SELECT , t0_BondStripEJB.bondsId, t0_BondStripEJB.bondsIdStrip, t0_BondStripEJB.stripType FROM BONDSSTRIP t0_BondStripEJB WHERE
      2005-05-04 17:25:25,923 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCQueryManager.BondStripEJB] Added findByPrimaryKey query command for home interface
      2005-05-04 17:25:25,924 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.BondStripEJB#findByBondsId] EJB-QL: select object(e) from BondsStrips as e where e.bondsId = ?1
      2005-05-04 17:25:25,929 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.BondStripEJB#findByBondsId] SQL: SELECT , t0_e.bondsId, t0_e.bondsIdStrip, t0_e.stripType FROM BONDSSTRIP t0_e WHERE (t0_e.bondsId = ?)
      2005-05-04 17:25:25,930 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindAllQuery.BondStripEJB#findAll] SQL: SELECT , bondsId, bondsIdStrip, stripType FROM BONDSSTRIP
      



      Any ideas why this is happening?

        • 1. Re: Sql not generating properly
          Guillermo Guisado Newbie

          fixed!! there were no getters on the PK Class...... no I have another problem see next thread

          • 2. Re: Sql not generating properly
            Ami Desai Newbie

            Hi, can you please tell me what did you mean by getters on PK class?

            I'm getting the same problem and not able to find what is wrong in my query or PK class. Thanks.

            PK class is as follow:

            public class SMPPAgentProfileParmsPK implements Serializable {
             //--------------------------------------------------------------------
             //-- Instance Attributes
             //--------------------------------------------------------------------
            
             public Integer smppAgentProfileId;
            
             //--------------------------------------------------------------------
             //-- Constructors
             //--------------------------------------------------------------------
            
             public SMPPAgentProfileParmsPK(){}
             public SMPPAgentProfileParmsPK( Integer smppAgentProfileId){
             this.smppAgentProfileId = smppAgentProfileId;
             }
            
             //--------------------------------------------------------------------
             //-- Overridden methods
             //--------------------------------------------------------------------
            
             public boolean equals( Object obj ) {
             if ( obj == null || !(obj instanceof SMPPAgentProfileParmsPK) ) {
             return false;
             }
             else if ( !((SMPPAgentProfileParmsPK)obj).smppAgentProfileId.equals(smppAgentProfileId) ) {
             return false;
             }
             else
             return true;
             }
            
             public int hashCode() {
             return smppAgentProfileId.intValue();
             }
            
             public String toString() {
             return smppAgentProfileId.toString();
             }
            }
            


            finder method is as follow:
            <query>
             <query-method>
             <method-name>findAllAgentProfiles</method-name>
             <method-params/>
             </query-method>
             <ejb-ql>[CDATA[ Select OBJECT(s) FROM SMPPAgentProfileParmsBean AS s WHERE s.smppAgentProfileID > 0]]</ejb-ql>
             </query>



            • 3. Re: Sql not generating properly
              Ami Desai Newbie

              My mistake in earlier post, I was little confused as all other CMPs in my project does not have getter and those still works!!

              Anyway I tried putting getter methods in PK class but still getting same error.

              The query jboss is generating is missing FROM a expression before FROM.
              It is generating a query as follow:

              SELECT FROM SMPPAGENTPROFILE t0_s WHERE (t0_s.smppAgentProfileId > 0)


              Log:
              2005-05-30 10:31:10,197 DEBUG [] Executing SQL: SELECT FROM SMPPAGENTPROFILE t0_s WHERE (t0_s.smppAgentProfileId > 0)
              2005-05-30 10:31:10,213 DEBUG [] Find failed
              java.sql.SQLException: ORA-00936: missing expression
              
               at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
               at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
               at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
               at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1451)
               at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:651)
               at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2117)
               at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2331)
               at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:422)
               at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:366)
               at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:314)
               at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:198)
               at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:117)
               at org.jboss.ejb.plugins.cmp.jdbc.JDBCFindEntitiesCommand.execute(JDBCFindEntitiesCommand.java:38)
               at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.findEntities(JDBCStoreManager.java:587)
               at org.jboss.ejb.plugins.CMPPersistenceManager.findEntities(CMPPersistenceManager.java:285)
               at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.findEntities(CachedConnectionInterceptor.java:322)
               at org.jboss.ejb.EntityContainer.find(EntityContainer.java:670)
               at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
               at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
               at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
               at java.lang.reflect.Method.invoke(Method.java:585)
               at org.jboss.ejb.EntityContainer$ContainerInterceptor.invokeHome(EntityContainer.java:1061)
               at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:88)
               at org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invokeHome(EntitySynchronizationInterceptor.java:204)
               at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invokeHome(CachedConnectionInterceptor.java:214)
               at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:88)
               at org.jboss.ejb.plugins.EntityInstanceInterceptor.invokeHome(EntityInstanceInterceptor.java:90)
               at org.jboss.ejb.plugins.EntityLockInterceptor.invokeHome(EntityLockInterceptor.java:61)
               at org.jboss.ejb.plugins.EntityCreationInterceptor.invokeHome(EntityCreationInterceptor.java:28)


              Any pointers? Let me know if you need more details on my CMP bean.