5 Replies Latest reply on Jun 24, 2004 9:45 AM by degnbol

    SQL not generating properly

    sclementi Newbie

      I can't get this to work EntityBean to work with CMP.

      The queries generate incorrectly...what am I missing here? (I'm sure I'm just screwing up the configuration somewhere...)

      The generated SQL is (pulled from the logs):
      SELECT FROM TIGER.TOPOLOGY WHERE

      No select list and an empty condition! Basically, the persistent attibutes are simply not being replaced. I get similar behavior if trying a findAll (with or without specifying the EJB-QL)

      Thanks in advance,

      Details follow:

      Exception resulting from a call to findByPrimaryKey( ...

      2004-05-22 00:52:45,702 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Topology#findByPrimaryKey] Executing SQL: SELECT FROM TIGER.TOPOLOGY WHERE
      2004-05-22 00:52:45,763 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Topology#findByPrimaryKey] Find failed
      java.sql.SQLException: ORA-00936: missing expression

      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
      at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:630)
      at oracle.jdbc.driver.T2CPreparedStatement.execute_for_describe(T2CPreparedStatement.java:851)
      at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:896)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:986)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2888)
      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2929)
      at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:304)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:166)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:111)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.execute(JDBCFindByPrimaryKeyQuery.java:108)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCFindEntityCommand.execute(JDBCFindEntityCommand.java:49)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.findEntity(JDBCStoreManager.java:571)
      at org.jboss.ejb.plugins.CMPPersistenceManager.findEntity(CMPPersistenceManager.java:299)
      at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.findEntity(CachedConnectionInterceptor.java:300)
      at org.jboss.ejb.EntityContainer.find(EntityContainer.java:697)
      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:324)
      at org.jboss.ejb.EntityContainer$ContainerInterceptor.invokeHome(EntityContainer.java:1043)
      at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:88)
      at org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invokeHome(EntitySynchronizationInterceptor.java:197)
      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:89)
      at org.jboss.ejb.plugins.EntityLockInterceptor.invokeHome(EntityLockInterceptor.java:61)
      at org.jboss.ejb.plugins.EntityCreationInterceptor.invokeHome(EntityCreationInterceptor.java:28)
      at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:88)
      at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:267)
      at org.jboss.ejb.plugins.TxInterceptorCMT.invokeHome(TxInterceptorCMT.java:98)
      at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome(SecurityInterceptor.java:92)
      at org.jboss.ejb.plugins.LogInterceptor.invokeHome(LogInterceptor.java:120)
      at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invokeHome(ProxyFactoryFinderInterceptor.java:93)
      at org.jboss.ejb.EntityContainer.internalInvokeHome(EntityContainer.java:483)
      at org.jboss.ejb.Container.invoke(Container.java:720)
      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:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
      at org.jboss.invocation.local.LocalInvoker.invoke(LocalInvoker.java:101)
      at org.jboss.invocation.InvokerInterceptor.invoke(InvokerInterceptor.java:90)
      at org.jboss.proxy.TransactionInterceptor.invoke(TransactionInterceptor.java:46)
      at org.jboss.proxy.SecurityInterceptor.invoke(SecurityInterceptor.java:45)
      at org.jboss.proxy.ejb.HomeInterceptor.invoke(HomeInterceptor.java:173)
      at org.jboss.proxy.ClientContainer.invoke(ClientContainer.java:85)
      at $Proxy35.findByPrimaryKey(Unknown Source)
      at com.clementisoftware.graph.topology.web.TopologyServlet.doPost(TopologyServlet.java:90)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
      at org.jboss.web.tomcat.security.JBossSecurityMgrRealm.invoke(JBossSecurityMgrRealm.java:220)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
      at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
      at org.jboss.web.tomcat.tc4.statistics.ContainerStatsValve.invoke(ContainerStatsValve.java:76)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
      at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2417)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
      at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
      at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:65)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
      at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:577)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
      at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
      at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
      at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:197)
      at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:781)
      at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:549)
      at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:605)
      at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:677)
      at java.lang.Thread.run(Thread.java:534)


      The bean class:

      package com.clementisoftware.graph.topology.ejb;

      import java.rmi.RemoteException;

      import javax.ejb.EJBException;
      import javax.ejb.EntityBean;
      import javax.ejb.EntityContext;
      import javax.ejb.RemoveException;

      /**
      *
      * @author Clementi
      *
      * @ejb.bean name = "Topology"
      * display-name = "Topology"
      * jndi-name = "ejb/graph/Topology"
      * local-jndi-name = "ejb/graph/TopologyLocal"
      * type = "CMP"
      * cmp-version = "2.x"
      * schema = "Topology"
      *
      * @ejb.pk class = "java.lang.Integer"
      *
      * @ejb.persistence table-name = "TIGER.TOPOLOGY"
      *
      * @ejb.finder signature = "java.util.Collection findAll()"
      * query = "SELECT OBJECT(t) FROM Topology AS t"
      *
      * @ejb.resource-ref res-ref-name = "jdbc/TigerDB"
      * res-type = "javax.sql.DataSource"
      * res-auth = "Container"
      *
      * @jboss.persistence create-table = "false"
      * remove-table = "false"
      * table-name = "TIGER.TOPOLOGY"
      * datasource = "java:/XAOracleDS"
      * datasource-mapping = "Oracle9i"
      *
      * @jboss.resource-ref res-ref-name = "jdbc/TigerDB"
      * jndi-name = "java:/XAOracleDS"
      *
      */
      public abstract class TopologyBean
      implements EntityBean
      {
      private EntityContext context;

      /**
      * @ejb.persistence column-name = "TOPOLOGY_ID"
      * @ejb.interface-method view-type = "both"
      * @ejb.pk-field
      *
      * @jboss.column-name name = "TOPOLOGY_ID"
      */
      public abstract Integer getId();
      public abstract void setId( Integer id );

      /**
      * @ejb.persistence column-name = "NAME"
      * @ejb.interface-method view-type = "both"
      *
      * @jboss.column-name name = "NAME"
      */
      public abstract String getName();
      public abstract void setName( String name );

      /**
      * @ejb.persistence column-name = "DESCRIPTION"
      * @ejb.interface-method view-type = "both"
      *
      * @jboss.column-name name = "DESCRIPTION"
      */
      public abstract String getDescription();
      public abstract void setDescription( String description );


      public void ejbActivate()
      throws EJBException, RemoteException
      {
      }

      public void ejbLoad()
      throws EJBException, RemoteException
      {
      }

      public void ejbPassivate()
      throws EJBException, RemoteException
      {
      }

      public void ejbRemove()
      throws RemoveException, EJBException, RemoteException
      {
      }

      public void ejbStore()
      throws EJBException, RemoteException
      {
      }

      public void setEntityContext( EntityContext context )
      throws EJBException, RemoteException
      {
      this.context = context;
      }

      public void unsetEntityContext()
      throws EJBException, RemoteException
      {
      context = null;
      }

      }

      The entity element from ejb-jar.xml


      <![CDATA[]]>
      <display-name>Topology</display-name>

      <ejb-name>Topology</ejb-name>

      com.clementisoftware.graph.topology.interfaces.TopologyHome
      com.clementisoftware.graph.topology.interfaces.Topology
      <local-home>com.clementisoftware.graph.topology.interfaces.TopologyLocalHome</local-home>
      com.clementisoftware.graph.topology.interfaces.TopologyLocal

      <ejb-class>com.clementisoftware.graph.topology.ejb.TopologyBean</ejb-class>
      <persistence-type>Container</persistence-type>
      <prim-key-class>java.lang.Integer</prim-key-class>
      False
      <cmp-version>2.x</cmp-version>
      <abstract-schema-name>Topology</abstract-schema-name>
      <cmp-field >
      <![CDATA[]]>
      <field-name>id</field-name>
      </cmp-field>
      <cmp-field >
      <![CDATA[]]>
      <field-name>name</field-name>
      </cmp-field>
      <cmp-field >
      <![CDATA[]]>
      <field-name>description</field-name>
      </cmp-field>

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


      <query-method>
      <method-name>findAll</method-name>
      <method-params>
      </method-params>
      </query-method>
      <ejb-ql><![CDATA[SELECT OBJECT(t) FROM Topology AS t]]></ejb-ql>

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



      And the jbosscmp-jdbc.xml specifics:

      <?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>



      <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>Topology</ejb-name>
      java:/XAOracleDS
      <datasource-mapping>Oracle9i</datasource-mapping>
      <create-table>false</create-table>
      <remove-table>false</remove-table>

      <table-name>TIGER.TOPOLOGY</table-name>

      <cmp-field>
      <field-name>id</field-name>
      <column-name>TOPOLOGY_ID</column-name>

      </cmp-field>
      <cmp-field>
      <field-name>name</field-name>
      <column-name>NAME</column-name>

      </cmp-field>
      <cmp-field>
      <field-name>description</field-name>
      <column-name>DESCRIPTION</column-name>

      </cmp-field>



      </enterprise-beans>

      </jbosscmp-jdbc>