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 21:52
Message:
Logged In: YES
user_id=9692
Er, I'm not wasting your time. I haven't forced you to
respond to me. Someone(anyone) else is perfectly capable of
doing so.
If this is documented, then why didn't you say so in the
first place? You are the one who decided not to, and went
off on this tangent. A simple RTF(ine)D comment early on
would have kept this bug from getting as long(and, now,
somewhat ugly) as it has.
As to jbosscmp-jdbc.xml supporting remapping the CMR field,
that's most excellent. <rant>Now, one has to wonder what
acid sun was taking, when they didn't make this part of the
spec. I mean, geez, you can map standard entity fields, but
not cmr fields? That's just plain idiotic on sun's part.</rant>
ps: Kevin, where's your patch? I'm interested in looking at
it too, and helping to get it working with newer code bases.
----------------------------------------------------------------------
Comment By: Dain Sundstrom (dsundstrom)
Date: 2002-11-20 21:44
Message:
Logged In: YES
user_id=251431
RTFM
You can you can override teh CMR field mapping today. You
always could. It is in the jbosscmp-jdbc.xml file not the
ejb-jar file which contains no mapping information. Please,
stop wasting my time. This is covered in the for pay docs
that you should bye, and I think it is covered in the free
quick start guide.
-dain
----------------------------------------------------------------------
Comment By: Adam Heath (doogie)
Date: 2002-11-20 21:35
Message:
Logged In: YES
user_id=9692
Hmm. I was talking with my boss about this at lunch, and we
realized what the real problem probably is. For standard
CMP fields, the name used in the code is disconnected from
the column name used in the database. ejb-jar.xml allows
for this change of name.
However, I am not aware of being able to choose the column
name for a CMR field in this same matter. The real solution
to this problem seems to be would be to add such a feature,
maybe in an extended jboss descriptor. This way, it's
completely generic, as it should be.
Is this even possible? If so, if someone could tell me the
name of the doc or howto or whatever(don't bother telling me
where, I can find it from the name), I'd be most grateful.
----------------------------------------------------------------------
Comment By: Dain Sundstrom (dsundstrom)
Date: 2002-11-20 16:48
Message:
Logged In: YES
user_id=251431
Kevin, please post your patch even if you don't have the
time to update it for 3.0.4. Someone will update it.
----------------------------------------------------------------------
Comment By: Dain Sundstrom (dsundstrom)
Date: 2002-11-20 16:44
Message:
Logged In: YES
user_id=251431
Adam,
This is definately a feature request; you are requesting
that JBossCMP support a specifc legacy mapping. This is
equivlent to the feature request for not null foriegn keys.
I am sure people that need not-null foreign key support
consider it a bug. We simply cannot consider all unsuported
mappings bugs.
----------------------------------------------------------------------
Comment By: Kevin Conner (kevinconner)
Date: 2002-11-20 09:17
Message:
Logged In: YES
user_id=598569
Hiya Dain.
I have already got a patch for the 3.0.2 source that fixes
this. I've been meaning to post it for a while but things
have gotten very hectic here :-), personally and
professionally.
I'm going to have a look at moving my patches to the
3.0.4 source this weekend and will post something at
the beginning of next week.
BTW I had to jump through a lot of hoops to get the
metadata consistent, would you expect this?
Kev
----------------------------------------------------------------------
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:
Battle your brains against the best in the Thawte Crypto
Challenge. Be the first to crack the code - register now:
http://www.gothawte.com/rd521.html
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development
