[JBoss-dev] [ jboss-Bugs-555070 ] Incorrect CREATE statement for MySQL ds

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 454-5900

Bugs item #555070, was opened at 2002-05-12 12:49
Message generated for change (Comment added) made by pilhuhn
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=376685&aid=555070&group_id=22866

Category: JBossCMP
Group: v3.2
Status: Open
>Resolution: Later
Priority: 5
Submitted By: Dylan van Iersel (dviersel)
Assigned to: Heiko W.Rupp (pilhuhn)
Summary: Incorrect CREATE statement for MySQL ds

Initial Comment:
The CREATE statement to create a new table in a MySQL datasource does 
not quote the tablename nor the column names. This causes problems 
when tables are created with names corresponding to MySQL reserved 
words. For example: when deploying an entity bean called "Group", 
JBoss trace 
shows:

org.jboss.deployment.DeploymentException: Could 
not deploy njar:file:/C:/Java/jboss-
3.0.0RC2/server/

default/tmp/deploy/server/default/deploy/rcontrol.ear/80.rcontrol.ear^/nntp-
bingrab.jar; - nested th

rowable: 
(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 near

'Group (id VARCHAR(255) BINARY NOT NULL, CONSTRAINT 
pk_Group PRIMARY KEY (id))' at line 1))

When I directly enter 
this CREATE statement on the MySQL command-line, I get the same 
error, whilst when I surround them by quotes (`) the table is created 
correctly.

OS: Win2000

JDK: 1.3.1

JBoss 3.0 RC2

----------------------------------------------------------------------

>Comment By: Heiko W.Rupp (pilhuhn)
Date: 2003-09-08 11:48

Message:
Logged In: YES 
user_id=217112

As the testsuite is currently failing for me, I postpone the

commit until this is ok again.

----------------------------------------------------------------------

Comment By: Heiko W.Rupp (pilhuhn)
Date: 2003-09-08 10:27

Message:
Logged In: YES 
user_id=217112

Fixed in 3.2.2rc4 - can you try?

----------------------------------------------------------------------

Comment By: Alexey Loubyansky (loubyansky)
Date: 2003-08-22 13:54

Message:
Logged In: YES 
user_id=543482

It seems to be ok. To expand it a bit, we could make it to

be read from standardjbosscmp-jdbc.xml from, say,

<reserved-words> Then developers can easily add new words in

jbosscmp-jdbc.xml.

Feel free to re-assign this to yourself, Heiko.

----------------------------------------------------------------------

Comment By: Heiko W.Rupp (pilhuhn)
Date: 2003-08-20 13:49

Message:
Logged In: YES 
user_id=217112

The following escapes reserved words by prepending a 'X':

It works for me (yes, also when entering data into the

tables etc.), but I am not sure if this is the right way to

do. Also more reserved words need to be added.

  Heiko

===================================================================

RCS file:

/cvsroot/jboss/jboss/src/main/org/jboss/ejb/plugins/cmp/jdbc/SQLUtil.java,v

retrieving revision 1.12.4.4

diff -r1.12.4.4 SQLUtil.java

33a34,37

> 	

> 	private final static String[] rwords =

{"CREATE","TABLE","ON","ALTER"};	

> 	

> 	

40a45,54

> 		 //	check for SQL reserved word and escape it with

prepending a "X"

> 		 // IMHO one should reject reserved words and throw a 

> 		 // DeploymentException - pilhuhn

> 		 for (int i = 0; i<rwords.length;i++) {

> 			if (rwords[i].equalsIgnoreCase(tableName)) {

> 				tableName = "X" + tableName;

> 				break;

> 			}         	

> 		 } 

> 

56c70

<          // TODO: check for SQL reserved word

---

>          

----------------------------------------------------------------------

Comment By: Dain Sundstrom (dsundstrom)
Date: 2003-08-18 00:36

Message:
Logged In: YES 
user_id=251431

I am no no longer a JBoss committer, so I am unable to fix

any bugs.

----------------------------------------------------------------------

Comment By: Dain Sundstrom (dsundstrom)
Date: 2002-06-25 01:54

Message:
Logged In: YES 
user_id=251431

This will be fixed during the rewrite of the metadata

package in 3.1.

----------------------------------------------------------------------

Comment By: Dain Sundstrom (dsundstrom)
Date: 2002-05-14 20:09

Message:
Logged In: YES 
user_id=251431

This is unlikely to change any time soon.  I suggest you 

avoid sql reserved words.  In the future, I want to add 

some code that will autogenerate around SQL reserved words.

----------------------------------------------------------------------

Comment By: Stephen Coy (scoy)
Date: 2002-05-13 09:29

Message:
Logged In: YES 
user_id=463096

Further to this, I've added a feature request (555315) regarding this because I think the current behaviour is more of a limitation of the current design/implementation rather than a bug per se.

----------------------------------------------------------------------

Comment By: Stephen Coy (scoy)
Date: 2002-05-13 08:30

Message:
Logged In: YES 
user_id=463096

Further to this, I've added a feature request (555315) regarding this because I think the current behaviour is more of a limitation of the current design/implementation rather than a bug per se.

----------------------------------------------------------------------

Comment By: Stephen Coy (scoy)
Date: 2002-05-13 08:06

Message:
Logged In: YES 
user_id=463096

This issue is not limited to MySQL. It's my understanding that all JDBC 2.0 compliant drivers are supposed to support the quoting syntax.

For now, you need to set up name mappings manually in jbosscmp-jdbc.xml.

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=376685&aid=555070&group_id=22866