3 Replies Latest reply on Mar 29, 2004 4:57 AM by aloubyansky

    JBoss Generating Bad SQL When Getting EJB Data

    chadlavy

      Since this is a long listing, I will thank you for your help at the top... Thank You For Your Help

      ENVIRONMENT:
      JBoss 3.2.3, WIN2000, XDoclet Generated Bean involved in a relationship.
      PROBLEM:
      I am getting the following error (see below) when running a unit test against my EJB. It seems that JBoss is sticking an extra comma in the FROM clause of one of the lookups. I can't see anything wrong with my configuration but maybe someone else can... I have attached the source code of the bean in question. Note that the error occurs when the getFieldFunctionArgValue method is called.

      2004-03-23 16:29:43,909 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.testFieldValidationFunction#findByPrimaryKey] Executing SQL: SELECT ENVIRONMENT, INPUT_REC_ID, INPUT_FIELD_CAT_CODE, INPUT_FIELD_ID, VALIDATION_FUNCTION_ID FROM CDWDBA.FVF_FIELD_VALIDATION_FUNCTION WHERE ENVIRONMENT=? AND INPUT_REC_ID=? AND INPUT_FIELD_CAT_CODE=? AND INPUT_FIELD_ID=? AND VALIDATION_FUNCTION_ID=?
      2004-03-23 16:29:43,989 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.testFieldValidationFunction] Executing SQL: SELECT ENVIRONMENT, INPUT_REC_ID, INPUT_FIELD_CAT_CODE, INPUT_FIELD_ID, FU_ID, FUA_ID FROM CDWDBA.FFA_FIELD_FUNCTION_ARG WHERE (environment=? AND input_rec_id=? AND input_field_cat_code=? AND input_field_id=? AND fu_id=?)
      2004-03-23 16:29:44,039 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.testFieldFunctionArg] Executing SQL: SELECT ENVIRONMENT, INPUT_REC_ID, INPUT_FIELD_CAT_CODE, INPUT_FIELD_ID, FU_ID, FUA_ID, ARG_VAL, FROM CDWDBA.FFA_FIELD_FUNCTION_ARG WHERE (ENVIRONMENT=? AND INPUT_REC_ID=? AND INPUT_FIELD_CAT_CODE=? AND INPUT_FIELD_ID=? AND FU_ID=? AND FUA_ID=?) OR (ENVIRONMENT=? AND INPUT_REC_ID=? AND INPUT_FIELD_CAT_CODE=? AND INPUT_FIELD_ID=? AND FU_ID=? AND FUA_ID=?)
      2004-03-23 16:29:44,059 ERROR [org.jboss.ejb.plugins.LogInterceptor] TransactionRolledbackLocalException in method: public abstract com.bankone.mdr.datamodel.FieldFunctionArgValue com.bankone.cdw.emdr.datamodel.interfaces.LocalFieldFunctionArg.getFieldFunctionArgValue() throws javax.ejb.EJBException, causedBy:
      COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/6000] SQL0104N An unexpected token "." was found following "RG_VAL, FROM CDWDBA". Expected tokens may include: "FROM". SQLSTATE=42601


      Here is the source code:

      /*
       * Created on Mar 17, 2004
       *
       */
      package com.bankone.cdw.emdr.datamodel.ejb;
      
      import java.rmi.RemoteException;
      
      import javax.ejb.CreateException;
      import javax.ejb.EJBException;
      import javax.ejb.EntityBean;
      import javax.ejb.EntityContext;
      import javax.ejb.RemoveException;
      
      import com.bankone.cdw.emdr.datamodel.interfaces.LocalFieldValidationFunction;
      import com.bankone.mdr.datamodel.FieldFunctionArgValue;
      import com.bankone.mdr.datamodel.FunctionArgValue;
      import com.bankone.mdr.datamodel.keys.FieldFunctionArgPK;
      
      /**
       * @author Chad Lavy
       *
       * @ejb.bean
       * name="testFieldFunctionArg"
       * type="CMP"
       * view-type="local"
       * jndi-name="emdr/test/datamodel/FieldFunctionArg"
       * transaction-type="Container"
       * cmp-version="2.x"
       * schema="FieldFunctionArg"
       * @ejb.persistence table-name = "CDWDBA.FFA_FIELD_FUNCTION_ARG"
       * @ejb.transaction type = "Required"
       * @ejb.pk class = "com.bankone.mdr.datamodel.keys.FieldFunctionArgPK" generate = "false"
       * @ejb.interface local-class = "com.bankone.cdw.emdr.datamodel.interfaces.LocalFieldFunctionArg"
       * @ejb.home local-class = "com.bankone.cdw.emdr.datamodel.interfaces.LocalFieldFunctionArgHome"
       * @ejb.finder signature = "java.util.Collection findByField(java.lang.String env, java.lang.Integer recId, java.lang.String catCode, java.lang.Integer fieldId)"
       * query = "SELECT OBJECT(o) FROM FieldFunctionArg AS o WHERE o.environment=?1 AND o.inputRecId=?2 AND o.inputFieldCatCode=?3 AND o.inputFieldId=?4"
       * @ejb.finder signature = "java.util.Collection findByRec(java.lang.String env, java.lang.Integer recId)"
       * query = "SELECT OBJECT(o) FROM FieldFunctionArg AS o WHERE o.environment=?1 AND o.inputRecId=?2"
       * @ejb.finder signature = "java.util.Collection findAll()"
       * @ejb.finder signature = "com.bankone.cdw.emdr.datamodel.interfaces.LocalFieldFunctionArg findByPrimaryKey(com.bankone.mdr.datamodel.keys.FieldFunctionArgPK key)"
       *
       */
      public abstract class FieldFunctionArgBean implements EntityBean {
      
       /**
       * @ejb.create-method
       */
       public FieldFunctionArgPK ejbCreate(
       String environment,
       Integer inputRecId,
       String inputFieldCatCode,
       Integer inputFieldId,
       Integer fuId,
       Integer fuaId,
       String argVal)
       throws CreateException {
       setEnvironment(environment);
       setInputRecId(inputRecId);
       setInputFieldCatCode(inputFieldCatCode);
       setInputFieldId(inputFieldId);
       setFuId(fuId);
       setFuaId(fuaId);
       setArgVal(argVal);
      
       return null;
       }
      
       public void ejbPostCreate(
       String environment,
       Integer inputRecId,
       String inputFieldCatCode,
       Integer inputFieldId,
       Integer fuId,
       Integer fuaId,
       String argVal) {
       }
      
       /**
       * @ejb.persistent-field
       * @ejb.pk-field
       * @jboss.column-name name = "ENVIRONMENT"
       * @jboss.jdbc-type type = "CHAR"
       */
       public abstract java.lang.String getEnvironment();
       public abstract void setEnvironment(java.lang.String val);
      
       /**
       * @ejb.interface-method
       * @ejb.persistent-field
       * @ejb.pk-field
       * @jboss.column-name name = "INPUT_REC_ID"
       * @jboss.jdbc-type type = "INTEGER"
       */
       public abstract java.lang.Integer getInputRecId();
       public abstract void setInputRecId(java.lang.Integer val);
      
       /**
       * @ejb.interface-method
       * @ejb.persistent-field
       * @ejb.pk-field
       * @jboss.column-name name = "INPUT_FIELD_CAT_CODE"
       * @jboss.jdbc-type type = "CHAR(2)"
       */
       public abstract java.lang.String getInputFieldCatCode();
       public abstract void setInputFieldCatCode(java.lang.String val);
      
       /**
       * @ejb.interface-method
       * @ejb.persistent-field
       * @ejb.pk-field
       * @jboss.column-name name = "INPUT_FIELD_ID"
       * @jboss.jdbc-type type = "INTEGER"
       */
       public abstract java.lang.Integer getInputFieldId();
       public abstract void setInputFieldId(java.lang.Integer val);
      
       /**
       * @ejb.interface-method
       * @ejb.persistent-field
       * @ejb.pk-field
       * @jboss.column-name name = "FU_ID"
       * @jboss.jdbc-type type = "INTEGER"
       */
       public abstract java.lang.Integer getFuId();
       public abstract void setFuId(java.lang.Integer val);
      
       /**
       * @ejb.interface-method
       * @ejb.persistent-field
       * @ejb.pk-field
       * @jboss.column-name name = "FUA_ID"
       * @jboss.jdbc-type type = "INTEGER"
       */
       public abstract java.lang.Integer getFuaId();
       public abstract void setFuaId(java.lang.Integer val);
      
       /**
       * @ejb.interface-method
       * @ejb.persistent-field
       * @jboss.column-name name = "ARG_VAL"
       */
       public abstract java.lang.String getArgVal();
       public abstract void setArgVal(java.lang.String val);
      
       /**
       * @ejb.value-object
       * relation = "external"
       * @ejb.relation
       * name = "FieldFunctionArg-FunctionArg"
       * role-name = "field-function-arg-has-function-arg"
       * target-ejb = "testFunctionArg"
       * target-role-name = "function-arg-has-field-function-args"
       * @jboss.relation
       * related-pk-field = "fuaId"
       */
       public abstract FunctionArgValue getFunctionArg();
       public abstract void setFunctionArg(FunctionArgValue val);
      
       /**
       * @ejb.interface-method
       */
       public abstract void setValidationFunction(LocalFieldValidationFunction fvf);
      
       /**
       * @ejb.relation
       * name = "FieldFunction-FieldFunctionArg"
       * role-name = "field-function-argument-has-field-function"
       * cascade-delete = "true"
       * @ejb.interface-method
       * @jboss.relation
       * related-pk-field = "environment"
       * fk-column = "environment"
       * @jboss.relation
       * related-pk-field = "inputRecId"
       * fk-column = "input_rec_id"
       * @jboss.relation
       * related-pk-field = "inputFieldCatCode"
       * fk-column = "input_field_cat_code"
       * @jboss.relation
       * related-pk-field = "inputFieldId"
       * fk-column = "input_field_id"
       * @jboss.relation
       * related-pk-field = "validationFunctionId"
       * fk-column = "fu_id"
       */
       public abstract LocalFieldValidationFunction getValidationFunction();
      
       /**
       * @ejb.interface-method
       */
       public FieldFunctionArgValue getFieldFunctionArgValue()
       throws EJBException {
       return new FieldFunctionArgValue(
       this.getEnvironment(),
       this.getInputRecId(),
       this.getInputFieldCatCode(),
       this.getInputFieldId(),
       this.getFuId(),
       this.getFuaId(),
       this.getArgVal(),
       this.getFunctionArg());
       }
      
       /* (non-Javadoc)
       * @see javax.ejb.EntityBean#setEntityContext(javax.ejb.EntityContext)
       */
       public void setEntityContext(EntityContext arg0)
       throws EJBException, RemoteException {
       }
      
       /* (non-Javadoc)
       * @see javax.ejb.EntityBean#ejbActivate()
       */
       public void ejbActivate() throws EJBException, RemoteException {
       }
      
       /* (non-Javadoc)
       * @see javax.ejb.EntityBean#ejbLoad()
       */
       public void ejbLoad() throws EJBException, RemoteException {
       }
      
       /* (non-Javadoc)
       * @see javax.ejb.EntityBean#ejbPassivate()
       */
       public void ejbPassivate() throws EJBException, RemoteException {
       }
      
       /* (non-Javadoc)
       * @see javax.ejb.EntityBean#ejbRemove()
       */
       public void ejbRemove()
       throws RemoveException, EJBException, RemoteException {
       }
      
       /* (non-Javadoc)
       * @see javax.ejb.EntityBean#ejbStore()
       */
       public void ejbStore() throws EJBException, RemoteException {
       }
      
       /* (non-Javadoc)
       * @see javax.ejb.EntityBean#unsetEntityContext()
       */
       public void unsetEntityContext() throws EJBException, RemoteException {
       }
      }


        • 1. Re: JBoss Generating Bad SQL When Getting EJB Data
          chadlavy

          It seems to be the unidirectional, 1:1 relationship that is causing the problem. If I take out the following snippit, everything works:

          /**
           * @ejb.value-object
           * relation = "external"
           * @ejb.relation
           * name = "FieldFunctionArg-FunctionArg"
           * role-name = "field-function-arg-has-function-arg"
           * target-ejb = "testFunctionArg"
           * target-role-name = "function-arg-has-field-function-args"
           * @ejb.interface-method
           * @jboss.relation
           * related-pk-field = "fuaId"
           */
           public abstract FunctionArgValue getFunctionArg();
           /**
           * @ejb.interface-method
           */
           public abstract void setFunctionArg(FunctionArgValue val);

          Anyone have any idea as to why this might be happening? I don't have the get/set flagged as a persistent field, but it seems to confuse JBoss none the less.

          • 2. Re: JBoss Generating Bad SQL When Getting EJB Data
            chadlavy

            Near as I can tell this is a bug...
            By moving the snippit (mentioned in my last post/reply) down in the source file, things began working. Let me know if anyone has any insights...

            • 3. Re: JBoss Generating Bad SQL When Getting EJB Data
              aloubyansky

              Is FunctionArgValue a local interface of the related 'testFunctionArg' entity?