Bugs item #640991, was opened at 2002-11-20 00: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: Adam Heath (doogie) Date: 2002-11-20 07:01 Message: Logged In: YES user_id=9692 Well, I still consider this a bug, that should remain open. 'user' is a very common name, both in database implementations and in user code. If you can tell me that the only place sql is generated is in the compiler classes, then I can have a go at adding code to do this escaping. My question then becomes how best to export this feature into the configuration space. Should it be part of standardjbosscmp-jdbc.xml, globally, or inside each ejb, per field/entity, or a combination of all? As to making the sql harder to read, why is that a problem? JBoss is supposed to hide all this database complexity, no one should ever need to read the sql, *ever*. If everything works, then all a user/developer needs to be concerned with is calilng methods on entities. This kind of bug exposes problems with the underlying database, which is one of the main driving forces j2ee container systems are supposed to hide. The note about case sensitvity is a valid point, however. It's such a shame sql is so shitty in this regard. ---------------------------------------------------------------------- Comment By: Dain Sundstrom (dsundstrom) Date: 2002-11-20 06: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 06: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-20 01: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-20 01: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-20 01: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
