• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Problem with many-to-many relationships with MySQL

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi all,
I'm having some troubles with many-to-many bidirectional relationships in MySQL.
There're two entity beans (OrderBean and ShopBean) wich are related this way.
An order can be placed in many shops and a shop can have many orders.
Everything deploys correctly, but when I try to create an Order I get this exception from JBoss:
2004-02-25 19:51:47,649 ERROR [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.Order] Error checking if entity exists
java.sql.SQLException: Syntax error or access violation, message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'order WHERE idOrder=7' at line 1"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1874)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1538)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:304)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCInsertPKCreateCommand.beforeInsert(JDBCInsertPKCreateCommand.java:83)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractCreateCommand.execute(JDBCAbstractCreateCommand.java:137)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.createEntity(JDBCStoreManager.java:554)
at org.jboss.ejb.plugins.CMPPersistenceManager.createEntity(CMPPersistenceManager.java:208)
at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.createEntity(CachedConnectionInterceptor.java:269)
at org.jboss.ejb.EntityContainer.createLocalHome(EntityContainer.java:581)
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)
....
Order table has idOrder as primary key and Shop table has idShop. There is also a mapping table called order_shop with two fields : idOrder and idShop.
Here's the most important code in both beans :

and the relation in ejb-jar.xml is as follows :



What am I doing wrong? Any help? Any idea to solve this?
Thanks in advance.
Pablo.
 
Pablo Mu�iz
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Never mind. I didn't realize "order" is a reserved word in MySQL, so Ive changed the name of the table.
Pablo.
 
reply
    Bookmark Topic Watch Topic
  • New Topic