4 Replies Latest reply on Oct 24, 2002 11:19 PM by satria

Bug SQL statement generated by JBoss-CMR

a13519 Jul 20, 2002 9:58 PM

JDK1.4 + JBoss3.0.0 + MySql

I bought CMP2.0 doc two weeks ago, I wrote my xml files carefully following the doc's guide and examples. My case is simple, two tables:

Office ( oid VARCHAR(4), name VARCHAR(30));
Employee ( eid VARCHAR(8), ename VARCHAR(30), oid VARCHAR(4));

I use oid as fk to establish relation bewteen office and employee. Hot deployment is cool and error-less. But I found a strange SQL statement in the log files:

INSERT INTO employee (eid, oid, ename, oid) VALUES (?, ?, ?, ?)

Also when I run the client try to add employee to office it prompt a exception:
-----------------------------------------------------
20:44:32,500 ERROR [Employee] Could not create entity
java.sql.SQLException: General error: Column 'oid' specified twice
-----------------------------------------------------

I tried several times to change the jbosscmp-jdbc.xml to find out the ill elements. Finally when I change the column name of fk in relationship mapping section, the SQL statement is changed as well, so I guess the problem is here. I attached the three main xml file with a part of the log file. Any comments and advices are very very welcome!

I am appreciated your reply! Thanks.

a13519

1. Re: Bug SQL statement generated by JBoss-CMR

a13519 Jul 20, 2002 9:59 PM (in response to a13519)

as
Actions
2. Re: Bug SQL statement generated by JBoss-CMR

dsundstrom Jul 21, 2002 12:01 AM (in response to a13519)

You can't have a CMP field and CMR field mapped to the same column. Remove the following from the Employee entity and it should start working: [pre]
<cmp-field>
<field-name>oid</field-name>
<column-name>oid</column-name>
</cmp-field>
[/pre]
Actions
3. Re: Bug SQL statement generated by JBoss-CMR

a13519 Jul 21, 2002 8:36 PM (in response to a13519)

Thanks, right now it is working properly.
Actions
4. Re: Bug SQL statement generated by JBoss-CMR

satria Oct 24, 2002 11:19 PM (in response to a13519)

hi, i'm having the same problem too, and i have tried some possible combination changin the jbossjdbc-cmp.xml, and the sql statement in log file changed, but still error (10:08:39,708 ERROR [ProductInsEJB] Could not create entity java.sql.SQLException: Column not found: Unknown column 'thePrincipal' in 'field list'),
here are my descriptors and log file:

server.log:
2002-10-25 10:08:39,588 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.ProductInsEJB] Executing SQL: SELECT COUNT(*) FROM tbl_prod_ins WHERE prod_ins_id=?
2002-10-25 10:08:39,698 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.ProductInsEJB] Executing SQL: INSERT INTO tbl_prod_ins (prod_ins_id, invst_typ_id, principal_id, prod_ins_nm, thePrincipal, investmentType) VALUES (?, ?, ?, ?, ?, ?)
2002-10-25 10:08:39,708 ERROR [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.ProductInsEJB] Could not create entity
java.sql.SQLException: Column not found: Unknown column 'thePrincipal' in 'field list'
at org.gjt.mm.mysql.MysqlIO.sendCommand(Unknown Source)

ejb-jar.xml:

ProductIns CMP EJB
....
<cmp-version>2.x</cmp-version>
<abstract-schema-name>ProductIns</abstract-schema-name>
<cmp-field><field-name>productInsId</field-name></cmp-field>
<cmp-field><field-name>investmentTypeId</field-name></cmp-field>
<cmp-field><field-name>principalId</field-name></cmp-field>
<cmp-field><field-name>productInsName</field-name></cmp-field>
....

<ejb-relation>
<ejb-relation-name>ProductIns-InvestmentType</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>ProductIns-typeof-InvestmentType</ejb-relationship-role-name>
Many
<cascade-delete />
<relationship-role-source>
<ejb-name>ProductInsEJB</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>investmentType</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>

<ejb-relationship-role>
<ejb-relationship-role-name>InvestmentType-has-ProductIns</ejb-relationship-role-name>
One
<relationship-role-source>
<ejb-name>InvestmentTypeEJB</ejb-name>
</relationship-role-source>
</ejb-relationship-role>
</ejb-relation>

<ejb-relation>
<ejb-relation-name>ProductIns-ThePrincipal</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>ProductIns-owned-by-ThePrincipal</ejb-relationship-role-name>
Many
<cascade-delete />
<relationship-role-source>
<ejb-name>ProductInsEJB</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>thePrincipal</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>

<ejb-relationship-role>
<ejb-relationship-role-name>ThePrincipal-has-ProductIns</ejb-relationship-role-name>
One
<relationship-role-source>
<ejb-name>ThePrincipalEJB</ejb-name>
</relationship-role-source>
</ejb-relationship-role>
</ejb-relation>

jbossjdbc-cmp.xml:

<ejb-name>ProductInsEJB</ejb-name>
<table-name>tbl_prod_ins</table-name>
<cmp-field><field-name>productInsId</field-name><column-name>prod_ins_id</column-name></cmp-field>
<cmp-field><field-name>investmentTypeId</field-name><column-name>invst_typ_id</column-name></cmp-field>
<cmp-field><field-name>principalId</field-name><column-name>principal_id</column-name></cmp-field>
<cmp-field><field-name>productInsName</field-name><column-name>prod_ins_nm</column-name></cmp-field>

<ejb-relation>
<ejb-relation-name>ProductIns-InvestmentType</ejb-relation-name>
<foreign-key-mapping/>
<ejb-relationship-role-name>ProductIns-typeof-InvestmentType</ejb-relationship-role-name>
<key-fields>
<key-field>

</key-field>
</key-fields>

<ejb-relationship-role-name>InvestmentType-has-ProductIns</ejb-relationship-role-name>
<key-fields>
</key-fields>
</ejb-relation>

<ejb-relation>
<ejb-relation-name>ProductIns-ThePrincipal</ejb-relation-name>
<foreign-key-mapping/>
<ejb-relationship-role-name>ProductIns-owned-by-ThePrincipal</ejb-relationship-role-name>
<key-fields>
<key-field>

</key-field>
</key-fields>

<ejb-relationship-role-name>ThePrincipal-has-ProductIns</ejb-relationship-role-name>
<key-fields>
</key-fields>
</ejb-relation>

.satria.
Actions

Go to original post