SQL Syntax Error in MySQL

[john_anderson_ii]

I'm a newbie, but in the past 12 hours I have gone through the JBoss IDE Tutorial, IBM's DeveloperWorks Tutorial on CMP as well as the JBoss Admin and CMP Documents. After building the samples, etc. I decided to try and wing it on my own.

Losely basing my practical application on the first example in Hightower's tutorial I decided to try and use mysql as the default ds.

I removed the hypersonic .xmls from the the appropriate place in $JBOSS_HOME and replaced them with the samples found in docs/jca. I then downloaded the latest and greatest j/connect from MySql AB. In short this is what I did.

mysql-connector-java-3.0.9-stable-bin.jar --> $JBOSS_HOME/server/default/lib

$JBOSS_HOME/docs/examples/jca/mysql-ds.xml --> $JBOSS_HOME/server/default/deploy/

$JBOSS_HOME/docs/examples/jms/mysql-jdbc2-service.xml --> $JBOSS_HOME/server/default/deploy/jms

I then edited mysql-ds.xml to reflect my connection string, username, password, etc... As a matter of fact, JBoss is connecting to my MySql server just fine because it is creating meta tables such as JMS_TRANSACTIONS.

The problem comes when the CMP container tries to create a table in order to persist an entity. For instantce, the application should create a table called "User" and "User" should contain an "email" field. However, when the container attempts this the console output indicates an exception where the SQL Server is complaining about incorrect syntax. To be more specific, JBoss is passing:

CREATE TABLE User(email VARCHAR(250) BINARY NOT NULL........

When it should be passing

CREATE TABLE User (email VARCHAR.........

(notice the space between "User" and "(".

That is what is causing the syntax error!

I've looked completely through the aforementioned .xml files and I cannot find where the CREATE TABLE template is. However, I may be barking up a tree in the wrong forrest here.

Any help in this area would much appreciated! I thank you in advance.

For reference:

MySql version = 4.0.16
MySql-Connector-Java Verison = 3.09
JBoss Version (Tomcat) = 3.2.2
OS = Linux 2.4.22

[jonlee]

Sure it's not because user is a reserved word? Just a thought. I'm a Postgresql/DB2/Oracle person so I'm a bit unfamiliar with MySQL.

[john_anderson_ii]

Yeah, I'm pretty sure because I can create the table manually from the mysql console with no problems as long as I put the space where it belongs. Thanks for the concern though!

[john_anderson_ii]

I stand (or sit as the case may be) corrected. You were 100% absolutely right. It's pretty weird that MySQL will let me create a table with the name "User" as long as a I put a space between the name and the table definition. However, I can create table foo, dog, cat, beaver, whatever without the space. Strange, but true.

I wonder if mysql creates a with the name "User ", that is user+space?

[markmatthews]

'user()' is a function in MySQL. Currently, MySQL's parser can't tell the difference between a table name and a function call in this case (when you don't use a space between the end of the identifier and the open paren).