• 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

JBOSS EJBQL generate wrong SQL

 
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi.
I use Lomboz eclipse 3.1 (Build-20050722) and mySQL4.1 to develop my EJB application. I have created a cmp bean in eclipse and can deploy to jboss (version 4.0.2) successfully. However, there is an exception when I call the "findAll()" function in CMP thru' a JSP program.

11:20:09,734 ERROR [User#findAll] Find failed
java.sql.SQLException: Table 'irs.xuser' doesn't exist
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2921)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2978)

I haven't defined any sql setting like 'irs.xuser' or 'xuser' in both Eclipse or Jboss. I don't know where it comes from. Seems it is generated by xdoclet or EJBQL. Do I mis-set some eclipse or jboss setting ? Is it a bugs of JBOSS4 or what else ? Please help.
Note :
"irs" is the mySQL database name of my application and 'user' is the valid table name of my bean

Below please find my program details,

[UserBean]
.....
import ....
/**
* @ejb.bean name="User"
* display-name="Name for User"
* description="Description for User"
* jndi-name="ejb/User"
* type="CMP"
* cmp-version="2.x"
* view-type="both"
*
* @ejb.persistence table-name = "user"
*
* @ejb.transaction type= "Required"
*
* @ejb.finder
* signature="java.util.Collection findAll()"
* query="SELECT OBJECT(u) FROM User AS u"
*
*/
public abstract class UserBean implements EntityBean {

private EntityContext _context;

public UserBean() {
super();
// TODO Auto-generated constructor stub
}
........


[ejb-jar]
.....
<!-- Entity Beans -->

<![CDATA[Description for User]]>
<display-name>Name for User</display-name>

<ejb-name>User</ejb-name>

tutorial.interfaces.UserHome
tutorial.interfaces.User
<local-home>tutorial.interfaces.UserLocalHome</local-home>
tutorial.interfaces.UserLocal

<ejb-class>tutorial.ejb.UserCMP</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>tutorial.interfaces.UserPK</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>User</abstract-schema-name>
<cmp-field >
<![CDATA[Getter for CMP Field userid]]>
<field-name>userid</field-name>
</cmp-field>
<cmp-field >
<![CDATA[Getter for CMP Field password]]>
<field-name>password</field-name>
</cmp-field>
<cmp-field >
<![CDATA[Getter for CMP Field group]]>
<field-name>group</field-name>
</cmp-field>


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

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

.......

[jbosscmp-jdbc.xml]
<?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>

java:/MySqlDS
<datasource-mapping>mySQL</datasource-mapping>
<create-table>false</create-table>

<enterprise-beans>

<ejb-name>User</ejb-name>
<cmp-field>
<field-name>userid</field-name>
<column-name>userid</column-name>
</cmp-field>
<cmp-field>
<field-name>password</field-name>
<column-name>password</column-name>
</cmp-field>
<cmp-field>
<field-name>group</field-name>
<column-name>group</column-name>
</cmp-field>

</enterprise-beans>
</jbosscmp-jdbc>

[mysql-ds.xml]

<local-tx-datasource>
<jndi-name>MySqlDS</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/irs</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>root</user-name>
<password>ABC1234</password>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>

<type-mapping>mySQL</type-mapping>

</local-tx-datasource>


Thanks
Ivan
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Reason:
"user" is a keyword in some databases and hence in the list of reserved words in JBoss which makes it not possible to use as table name in the default configuration.

Solution:
MySQL does not have user as a keyword so it is safe to change the behavior.
Remove the reserved keyword by editing your conf/standardjbosscmp-jdbc.xml file.
At the bottom there is a section called "reserved-words" where the keyword "user" can be removed.

More info:
JBoss forums
 
Ivan
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It's working now.
Thank you very much !!!

Ivan
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic