Bugs item #640991, was opened at 2002-11-19 18:15 You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=376685&aid=640991&group_id=22866
Category: JBossCMP Group: v3.0 Rabbit Hole >Status: Closed >Resolution: Duplicate Priority: 5 Submitted By: Adam Heath (doogie) >Assigned to: Dain Sundstrom (dsundstrom) Summary: field names are not quoted Initial Comment: Certain databases have certain keywords reserved. To prevent this, *all* table names, and *all* column names must be quoted. To observe this very broken behaviour, do the following: 1. Download ITracker(www.sf.net/projects/itracker, version 1.4.0) 2. Convert the ear to an unpacked form(unzip, rename, unpack, etc) 3. Change standardjbosscmp-jdbc.xml to use a type mapping of PostgreSQL. 4. Disable(or remove) server/default/deploy/hsqldb-service.xml 5. Add postgresql.jar to server/default/deploy 6. Add postgresql-service.xml to server/default/deploy 7. Add itracker.ear to server/default/deploy 8. bin/run.sh Postgres will have an error parsing the generated sql, because this code uses a CMR of 'user', which is a reserved word. If I cut and paste the generated CREATE TABLE command into psql, and use "" to quote user, it will eventually deploy. But then, later on, it'll fail as it tries to query and set the values. Since JBoss can have no way of knowing this kind of information, *all* such non-sql keywords should be quoted. This bug first discovered on 3.0.1, and I just downloaded a brand new 3.0.4, to verify it's existance(which is how I knew the exact steps to duplicate it). I'm running postgres 7.2.1. Debian unstable(sid), i386. ---------------------------------------------------------------------- >Comment By: Dain Sundstrom (dsundstrom) Date: 2002-11-20 00:26 Message: Logged In: YES user_id=251431 Adam please read the feature list before posting. This has been a know issue for a long time (see feature request 555315). The reason I didn't code it that way, was I personally would never use any name that had to be quoted in a database. Anyway, it is not true that all databases support quoted identifiers. We actually need to check the database metadata first. Also I think we should not use quotes unless absolutely necessary as it makes the SQL harder to read. -dain ---------------------------------------------------------------------- Comment By: Jeremy Boynes (jboynes) Date: 2002-11-20 00:07 Message: Logged In: YES user_id=378919 Another consequence of quoting is to make name matching case sensitive (normally it is case blind). As least that what SQL-92 says, not all databases do it. I believe this would lead to confusion as JBoss would be using case-sensitive quoted identifiers and most other applications and tools would not. Quoting some of the time (e.g. only keywords) would be even more confusing. A simple workaround might be to quote the identifier in jbosscmp-jdbc.xml (e.g. <table-name>"user"</table-name> or <column-name>"user"</column-name> ) as I believe this is passed directly into the SQL text. Of course, you could also set a table/column name here that didn't conflict and save everyone some confusion. ---------------------------------------------------------------------- Comment By: Adam Heath (doogie) Date: 2002-11-19 19:32 Message: Logged In: YES user_id=9692 Also, wouldn't it be easier to always quote, instead of having to maintain a list of common keywords, and merging that with each particular jdbc driver's list? ---------------------------------------------------------------------- Comment By: Adam Heath (doogie) Date: 2002-11-19 19:28 Message: Logged In: YES user_id=9692 So, this is a valid bug? I looked briefly at the JDBCEJBQLCompiler code in 3.0.1, and didn't see anything that looked like field name escaping going on. ---------------------------------------------------------------------- Comment By: David Jencks (d_jencks) Date: 2002-11-19 19:17 Message: Logged In: YES user_id=60525 It is certainly unnecessary to quote all column names. Lists of sql-92 keywords exist, and drivers are required to provide a list of their other keywords in DatabaseMetaData.getSQLKeywords(). ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=376685&aid=640991&group_id=22866 ------------------------------------------------------- This sf.net email is sponsored by: To learn the basics of securing your web site with SSL, click here to get a FREE TRIAL of a Thawte Server Certificate: http://www.gothawte.com/rd524.html _______________________________________________ Jboss-development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development
