3 Replies Latest reply on Aug 4, 2004 4:35 AM by aloubyansky

    MySQL problem - column xxx specified twice

    mzint

      Hi there,

      I just switched my datasource from Hypersonic to MySQL.

      JBoss Version 3.2.5
      MySQL Version 4.0.18
      Connector/J Version 3.0.14

      The szenario is like this - I have a contract which consists of components that in turn have fields ordered by a sequence holding a value. The aggregation table stores all this information including references to the contract and the component table. As per Spec I initialize the CMP fields in the ejbCreate-method and set the CMR-relations in the ejbPostCreate-method.


      The Statement that JBoss tries to execute is:

      DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.ComponentFieldValue]
      Executing SQL: INSERT INTO COMPONENTFIELDVALUE (CONTRACT, COMPONENT, FIELDNAME, SEQUENCE, VALUE, component, contract)
      VALUES (?, ?, ?, ?, ?, ?, ?)



      Everything was running fine with Hypersonic, but now I get the following error message:

      ERROR [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.ComponentFieldValue]
      Could not create entity
      java.sql.SQLException: General error, message from server: "Column 'CONTRACT' specified twice" at
      com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1977)
       at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1163)
       at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1272)
       at com.mysql.jdbc.Connection.execSQL(Connection.java:2236)
       at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1741)
       at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1588)
       at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:335).......




      // ==============================================================
       // Create Method
       // ==============================================================
      
       /**
       * @return
       * @throws CreateException
       *
       * @ejb.create-method
       */
       public ComponentFieldValuePK ejbCreateFieldValue(Integer contract, Integer component,
       String fieldname, String value, ContractLocal conl , ComponentLocal coml)
       throws CreateException, FinderException{
       setContract(contract);
       setComponent(component);
       setFieldname(fieldname);
       setSequence(new Integer(0));
       setValue(value);
       return null;
       }
      
       public void ejbPostCreateFieldValue(Integer contract, Integer component,
       String fieldname, String value, ContractLocal conl, ComponentLocal coml){
       setContractCMR(conl);
       setComponentCMR(coml);
       }
      
      
       // ==============================================================
       // Container Managed Relations
       // ==============================================================
      
       /**
       * @return
       *
       * @ejb.interface-method
       * @ejb.relation name = "ContractAndCFValueRelation"
       * role-name = "CFValueHasContract"
       * target-role-name = "ContractHasCFValues"
       * target-ejb = "Contract"
       * cascade-delete = "yes"
       *
       * @jboss.relation fk-column = "contract"
       * related-pk-field = "id"
       * fk-constraint = "true"
       */
       public abstract ContractLocal getContractCMR();
       public abstract void setContractCMR(ContractLocal con);
      
      
       /**
       * @return
       *
       * @ejb.interface-method
       * @ejb.relation name = "ComponentAndCFValueRelation"
       * role-name = "CFValueHasComponent"
       * target-role-name = "ComponentHasCFValue"
       * target-ejb = "Component"
       * cascade-delete = "yes"
       *
       * @jboss.relation fk-column = "component"
       * related-pk-field = "id"
       * fk-constraint = "true"
       */
       public abstract ComponentLocal getComponentCMR();
       public abstract void setComponentCMR(ComponentLocal col);



      I found a posting on the net that has the same problem, but I couldn't find a solution yet.

      http://www.mail-archive.com/middlegen-user@lists.sourceforge.net/msg00317.html

      Has anybody solved this problem yet ?? Is this a JBoss issue or a MySQL issue ??

      thanks
      matt

        • 1. Re: MySQL problem - column xxx specified twice
          aloubyansky

          Does the column appear only once in the statement in MySql case?

          • 2. Re: MySQL problem - column xxx specified twice
            mzint

            Hello Alexey,

            The SQL-statement that JBoss tries to execute is:

            Executing SQL: INSERT INTO COMPONENTFIELDVALUE (CONTRACT, COMPONENT, FIELDNAME, SEQUENCE, VALUE, contract, component)
            VALUES (?, ?, ?, ?, ?, ?, ?)


            I switched the Datasource to Oracle in the meantime and got the same error. The error message is ORA-00957 and states, that the column is specified twice.

            Is the Hypersonic database case-sensitive ?? It's logical that you can't have two identical column names in the same table and I never really noticed this. The funny thing is that everything works fine in the Hypersonic database.

            The database table only consists of 5 columns (Contract, Component, Fieldname, Sequence, Value).

            The table has a compound primary key consisting of contract, component, fieldname and sequence -- contract and component are supposed to be foreign keys as well.

            At the moment I'm pretty stuck. Maybe someone could give me a hint on how to solve this.

            Error Messages:
            2004-08-03 13:54:49,991 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.ComponentFieldValue]
             Executing SQL: INSERT INTO COMPONENTFIELDVALUE (CONTRACT, COMPONENT, FIELDNAME, SEQUENCE, VALUE, contract, component)
             VALUES (?, ?, ?, ?, ?, ?, ?)
            2004-08-03 13:54:50,021 ERROR [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.ComponentFieldValue] Could not create entity
            java.sql.SQLException: ORA-00957: duplicate column name
            
             at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
             at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
             at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
             at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
             at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
             at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
             at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
             at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
             at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
             at ....


            ejb-jar.xml:
            <enterprise-beans>
            
             <!-- Entity Beans -->
            
             <entity >
             <description><![CDATA[Table Component Field Value]]></description>
            
             <ejb-name>ComponentFieldValue</ejb-name>
            
             <local-home>webservices.interfaces.ComponentFieldValueLocalHome</local-home>
             <local>webservices.interfaces.ComponentFieldValueLocal</local>
            
             <ejb-class>webservices.ejb.ComponentFieldValueBean</ejb-class>
             <persistence-type>Container</persistence-type>
             <prim-key-class>webservices.ejb.ComponentFieldValuePK</prim-key-class>
             <reentrant>False</reentrant>
             <cmp-version>2.x</cmp-version>
             <abstract-schema-name>componentFieldValueSchema</abstract-schema-name>
             <cmp-field >
             <description><![CDATA[]]></description>
             <field-name>contract</field-name>
             </cmp-field>
             <cmp-field >
             <description><![CDATA[]]></description>
             <field-name>component</field-name>
             </cmp-field>
             <cmp-field >
             <description><![CDATA[]]></description>
             <field-name>fieldname</field-name>
             </cmp-field>
             <cmp-field >
             <description><![CDATA[]]></description>
             <field-name>sequence</field-name>
             </cmp-field>
             <cmp-field >
             <description><![CDATA[]]></description>
             <field-name>value</field-name>
             </cmp-field>
            
             </query>
            
             </entity>
            
            
             </enterprise-beans>
            
             <!-- Relationships -->
             <relationships >
             <ejb-relation >
             <ejb-relation-name>ContractAndCFValueRelation</ejb-relation-name>
            
             <ejb-relationship-role >
             <ejb-relationship-role-name>CFValueHasContract</ejb-relationship-role-name>
             <multiplicity>Many</multiplicity>
             <cascade-delete/>
             <relationship-role-source >
             <ejb-name>ComponentFieldValue</ejb-name>
             </relationship-role-source>
             <cmr-field >
             <cmr-field-name>contractCMR</cmr-field-name>
             </cmr-field>
             </ejb-relationship-role>
            
             <ejb-relationship-role >
             <ejb-relationship-role-name>ContractHasCFValues</ejb-relationship-role-name>
             <multiplicity>One</multiplicity>
             <relationship-role-source >
             <ejb-name>Contract</ejb-name>
             </relationship-role-source>
             <cmr-field >
             <cmr-field-name>componentFieldValuesCMR</cmr-field-name>
             <cmr-field-type>java.util.Collection</cmr-field-type>
             </cmr-field>
             </ejb-relationship-role>
            
             </ejb-relation>
             </relationships>


            jbosscmp-jdbc.xml:

            <enterprise-beans>
            
             <entity>
             <ejb-name>ComponentFieldValue</ejb-name>
            
             <table-name>COMPONENTFIELDVALUE</table-name>
            
             <cmp-field>
             <field-name>contract</field-name>
             <column-name>CONTRACT</column-name>
             </cmp-field>
            
             <cmp-field>
             <field-name>component</field-name>
             <column-name>COMPONENT</column-name>
             </cmp-field>
            
             <cmp-field>
             <field-name>fieldname</field-name>
             <column-name>FIELDNAME</column-name>
             </cmp-field>
            
             <cmp-field>
             <field-name>sequence</field-name>
             <column-name>SEQUENCE</column-name>
             </cmp-field>
            
             <cmp-field>
             <field-name>value</field-name>
             <column-name>VALUE</column-name>
             </cmp-field>
             </entity>
            
             </enterprise-beans>
            
             <relationships>
             <ejb-relation>
             <ejb-relation-name>ContractAndCFValueRelation</ejb-relation-name>
            
             <ejb-relationship-role>
             <ejb-relationship-role-name>CFValueHasContract</ejb-relationship-role-name>
             <fk-constraint>true</fk-constraint>
             <key-fields/>
            
             </ejb-relationship-role>
             <ejb-relationship-role>
             <ejb-relationship-role-name>ContractHasCFValues</ejb-relationship-role-name>
             <key-fields>
             <key-field>
             <field-name>id</field-name>
             <column-name>contract</column-name>
             </key-field>
             </key-fields>
            
             </ejb-relationship-role>
             </ejb-relation>
             <ejb-relation>
             <ejb-relation-name>ComponentAndCFValueRelation</ejb-relation-name>
            
             <ejb-relationship-role>
             <ejb-relationship-role-name>CFValueHasComponent</ejb-relationship-role-name>
             <fk-constraint>true</fk-constraint>
             <key-fields/>
            
             </ejb-relationship-role>
             <ejb-relationship-role>
             <ejb-relationship-role-name>ComponentHasCFValue</ejb-relationship-role-name>
             <key-fields>
             <key-field>
             <field-name>id</field-name>
             <column-name>component_id</column-name>
             </key-field>
             </key-fields>
            
             </ejb-relationship-role>
             </ejb-relation>
             </relationships>


            • 3. Re: MySQL problem - column xxx specified twice
              aloubyansky

              Use upper-cased column names for foreign keys.