4 Replies Latest reply on Dec 2, 2003 8:28 PM by markmatthews

    SQL Syntax Error in MySQL

    John Anderson Newbie

      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