0 Replies Latest reply on Jan 25, 2007 11:23 AM by Ludger Komas

    SQL Syntax Error

    Ludger Komas Newbie

      I have a problem to create a table via CMP. Everything was OK using the Hypersonic DB. I switched to MySQL and encounter the following problem.

      Running on JBoss 3.2.1, MySQL 5.

      I imported the mysl-connector-java-3.1.14.bin.jar into server\default\lib and mysql-ds.xml into the server\default\deploy.

      <jndi-name>MySqlDS</jndi-name>
       <connection-url>jdbc:mysql://localhost:3306/auditdb</connection-url>
       <driver-class>com.mysql.jdbc.Driver</driver-class>
       <user-name>root</user-name>

      (correct password used)

      and changed standardjaws.xml

      <datasource>java:/MySqlDS</datasource>
       <type-mapping>mySQL</type-mapping>
       <debug>false</debug>


      and standardjbosscmp-jdbc.xml:

      <defaults>
       <datasource>java:/MySqlDS</datasource>
       <datasource-mapping>mySQL</datasource-mapping>
      
       <create-table>true</create-table>
       <remove-table>false</remove-table>
       <read-only>false</read-only>
       <time-out>300</time-out>
       <pk-constraint>true</pk-constraint>
       <fk-constraint>false</fk-constraint>
       <row-locking>false</row-locking>
       <preferred-relation-mapping>foreign-key</preferred-relation-mapping>
       <read-ahead>
       <strategy>on-load</strategy>
       <page-size>1000</page-size>
       <eager-load-group>*</eager-load-group>
       </read-ahead>
       <list-cache-max>1000</list-cache-max>
      
       <unknown-pk>
       <key-generator-factory>UUIDKeyGeneratorFactory</key-generator-factory>
       <unknown-pk-class>java.lang.String</unknown-pk-class>
       <jdbc-type>VARCHAR</jdbc-type>
       <sql-type>VARCHAR(32)</sql-type>
       </unknown-pk>
      
       <entity-command name="default"/>
      
       </defaults>
      

      xdoclet.xml:

      datasource="java:/MySqlDS"
      datasourcemapping="mySQL"
      preferredrelationmapping="foreign-key"


      jbosscmp-jdbc.xml

      <jbosscmp-jdbc>
       <defaults>
       <datasource>java:/MySqlDS</datasource>
       <datasource-mapping>mySQL</datasource-mapping>
       <preferred-relation-mapping>foreign-key</preferred-relation-mapping>
       </defaults>
      
       <enterprise-beans>
      
       <entity>
       <ejb-name>Baustein</ejb-name>
      
       <table-name>baustein</table-name>
      
       <cmp-field>
       <field-name>bausteinid</field-name>
       <column-name>bausteinid</column-name>
      
       <jdbc-type>VARCHAR</jdbc-type>
       <sql-type>INTEGER</sql-type>
      
       </cmp-field>
       <cmp-field>
       <field-name>kuerzel</field-name>
       <column-name>kuerzel</column-name>
      
       <jdbc-type>VARCHAR</jdbc-type>
       <sql-type>VARCHAR</sql-type>


      When I try to deploy my project, I receive an error:

      16:33:08,000 ERROR [EntityContainer] Starting failed
      org.jboss.deployment.DeploymentException: Error while creating table; - nested throwable: (java.sql.SQLException: 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 ' CONSTRAINT pk_baustein PRIMARY KEY (bausteinid))' at line 1)

      ...and I have no more ideas, how I can fix it. Any hints?