Unable to Run Example with MySQL

[garymarsh]

Hi,
I am using JBoss 3.2.0beta2 and MySQL 4.0.6gamma with the Driver class com.mysql.jdbc.Driver from the mysql-connector-java-3.0.2-beta-bin.jar. I am getting the following error:

2002-12-22 13:57:04,841 ERROR [org.jboss.ejb.EntityContainer] Starting failed
org.jboss.deployment.DeploymentException: Error while creating table; - nested throwable: (java.sql.SQLException: Syntax error or access violation: 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 'desc VARCHAR(250) BINARY NOT NULL, the_boss INTEGER, CONSTRAINT)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createTable(JDBCStartCommand.java:177)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.execute(JDBCStartCommand.java:86)

I have copied the mysql-service.xml to the default\deploy directory and made the following modifications:



<config-property name="ConnectionURL" type="java.lang.String">jdbc:mysql://localhost:3306/gopack</config-property>
<config-property name="DriverClass" type="java.lang.String">com.mysql.jdbc.Driver</config-property>
<!--set these only if you want only default logins, not through JAAS -->
<config-property name="UserName" type="java.lang.String"></config-property>
<config-property name="Password" type="java.lang.String"></config-property>




I also copied the database driver jar file to default\lib directory.

I have modified the examples jbosscmp-jdbc.xml as follows:

<jbosscmp-jdbc>

<!-- datasource>java:/DefaultDS
<datasource-mapping>Hypersonic SQL</datasource-mapping -->
java:/MySqlDS
<datasource-mapping>mySQL</datasource-mapping>
<create-table>true</create-table>
<remove-table>true</remove-table>
<pk-constraint>true</pk-constraint>
<preferred-relation-mapping>foreign-key</preferred-relation-mapping>



What else do I need to do?

Gary

[b0howard]

Hi Gary

I assume that your example is the template examples from
the free user doc's page.

I noticed that the
<config-property name="UserName" and
<config-property name="Password"
were not set in your mysql-service.xml file.

I configured the examples to work with mySQL 3.?? version
and I specified UserName with no password.

You might want to check the authentication parameters used to login to mySQL from the mysql.exe (windows) command line tool.

I had some different issues when changing the data source to mysql:
I changed the datasource names and mappings to not
use default names.
I had to change the .ant.properites file (the template
exampeles):

# Set the DB type mapping (Hypersonic SQL, PostgreSQL etc., see XDoclet's attribute "typemapping")
#type.mapping=Hypersonic SQL
type.mapping=mySQL

# Set the DataSource name your are going to use (java:/DefaultDS etc., see XDoclet's attribute "datasource")
# datasource.name=java:/DefaultDS
datasource.name=java:/MySqlDS


XDoclet uses these properties when building the app's
jbosscmp-jdbc.xml file.

You might need to change the .ant.properties "type.mapping" property if your using the template's build.xml file.
Check the jbosscmp-jdbc.xml file in the ejb-test.jar
in the JBoss deployment directory "your_jboss_path/server/default/deploy"
to see what exactly is being generated.

It should look something like this:
<jbosscmp-jdbc>

your_datasource_name
<datasource-mapping>mySQL</datasource-mapping>


your mapping might still be "Hypersonic SQL" which might also be causing the problem.


Cheer's
Bruce

[garymarsh]

Actually Bruce, I was trying to run the cmp2 Ganster program. I modified the jbosscmp-jdbc.xml file found in the cmp2/src/resources/ejb-jar/META-INF directory. The ant build.xml file doesn't address the database directly only the jbosscmp-jdbc.xml file.

again I modified the values found in defaults datasource and datasource-mapping from



java:/DefaultDS
<datasource-mapping>Hypersonic SQL</datasource-mapping>

to


java:/MySqlDS
<datasource-mapping>mySQL</datasource-mapping>


Do you have any perls of wisdom on this example program?

Thanks,

Gary

[garymarsh]

Bruce, I found two bugs, one in the cmp2 examples jbosscmp-jdbc.xml and the other is in the creation of the JMS_MESSAGES table.

The jbosscmp-jdbc.xml contains the following :


<ejb-name>OrganizationEJB</ejb-name>
<table-name>organization</table-name>

<cmp-field>
<field-name>name</field-name>
<column-name>name</column-name>
</cmp-field>
<cmp-field>
<field-name>description</field-name>
<column-name>desc</column-name>
<not-null/>
</cmp-field>


The word "desc" is reserved in MySQL and when JAWS trys to create the table it blows up.

The second error occurs when the "JMS_MESSAGES" table is created by JBoss. JAWS generated the following SQL script:

CREATE TABLE JMS_MESSAGES ( MESSAGEID INTEGER NOT NULL, DESTINATION VARCHAR(50) NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB OBJECT, PRIMARY KEY (MESSAGEID, DESTINATION) )

The problem is that there is no "OBJECT" type in MySQL. If you substitue "BLOB" for "OBJECT" and execute the above SQL script by hand the table is created and everything works.

Gary