You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "roger.keays" <ro...@ninthavenue.com.au> on 2006/11/16 11:41:35 UTC
Escaping reserved words in generated sql?
Some of the field names on my entities are reserved words in Oracle (comment,
type, level, size, index) and Mysql (index). I noticed that although the
DBDictionaries for these database include some of these names, the generated
SQL does not quote those tokens appropriately:
Caused by: <2|false|0.9.6-incubating>
org.apache.openjpa.util.StoreException: ORA-01747: invalid
user.table.column, table.column, or column specification
{prepstmnt 17689439 INSERT INTO FB_BLOBS (contentType, filename, size)
VALUES (?, ?, ?)
If I execute the statement manually, quoting the "size" token, it is okay.
Why doesn't openjpa do this for me? Am I doing something wrong?
Thanks,
Roger
--
View this message in context: http://www.nabble.com/Escaping-reserved-words-in-generated-sql--tf2642125.html#a7375274
Sent from the open-jpa-dev mailing list archive at Nabble.com.
Re: Escaping reserved words in generated sql?
Posted by Marc Prud'hommeaux <mp...@apache.org>.
I'm amazed that I never knew that you could quote reserved column
names. I just tested on Oracle, SQL Server, and Derby, and it seems
to work for both of them.
Could you make an enhancement request at http://issues.apache.org/
jira/browse/OPENJPA so we can track this? It'd be a very useful
feature to have.
On Nov 16, 2006, at 9:40 AM, Patrick Linskey wrote:
> We've historically always taken the approach of avoiding the word
> altogether, rather than escaping the word.
>
> It certainly wouldn't be difficult to modify DBDictionary to escape
> reserved words instead of avoiding them, though.
>
> -Patrick
>
> --
> Patrick Linskey
> BEA Systems, Inc.
>
> ______________________________________________________________________
> _
> Notice: This email message, together with any attachments, may
> contain
> information of BEA Systems, Inc., its subsidiaries and
> affiliated
> entities, that may be confidential, proprietary, copyrighted
> and/or
> legally privileged, and is intended solely for the use of the
> individual
> or entity named in this message. If you are not the intended
> recipient,
> and have received this message in error, please immediately return
> this
> by email and then delete it.
>
>> -----Original Message-----
>> From: roger.keays [mailto:roger.keays@ninthavenue.com.au]
>> Sent: Thursday, November 16, 2006 3:07 AM
>> To: open-jpa-dev@incubator.apache.org
>> Subject: Re: Escaping reserved words in generated sql?
>>
>>
>>
>> roger.keays wrote:
>>>
>>> {prepstmnt 17689439 INSERT INTO FB_BLOBS (contentType,
>> filename, size)
>>> VALUES (?, ?, ?)
>>>
>>> If I execute the statement manually, quoting the "size"
>> token, it is okay.
>>>
>> Not the best example I suppose, since 'size' isn't in the
>> default oracle
>> DBdictionary. The following query, however, has the same
>> behaviour, even
>> though 'comment' is in the OracleDictionary:
>>
>> INSERT INTO FB_PRIVILEGES (comment, inheritable, level, role, type,
>> username, item_uuid) VALUES (?, ?, ?, ?, ?, ?, ?)
>>
>> --
>> View this message in context:
>> http://www.nabble.com/Escaping-reserved-words-in-generated-sql
>> --tf2642125.html#a7375647
>> Sent from the open-jpa-dev mailing list archive at Nabble.com.
>>
>>
RE: Escaping reserved words in generated sql?
Posted by Patrick Linskey <pl...@bea.com>.
We've historically always taken the approach of avoiding the word
altogether, rather than escaping the word.
It certainly wouldn't be difficult to modify DBDictionary to escape
reserved words instead of avoiding them, though.
-Patrick
--
Patrick Linskey
BEA Systems, Inc.
_______________________________________________________________________
Notice: This email message, together with any attachments, may contain
information of BEA Systems, Inc., its subsidiaries and affiliated
entities, that may be confidential, proprietary, copyrighted and/or
legally privileged, and is intended solely for the use of the individual
or entity named in this message. If you are not the intended recipient,
and have received this message in error, please immediately return this
by email and then delete it.
> -----Original Message-----
> From: roger.keays [mailto:roger.keays@ninthavenue.com.au]
> Sent: Thursday, November 16, 2006 3:07 AM
> To: open-jpa-dev@incubator.apache.org
> Subject: Re: Escaping reserved words in generated sql?
>
>
>
> roger.keays wrote:
> >
> > {prepstmnt 17689439 INSERT INTO FB_BLOBS (contentType,
> filename, size)
> > VALUES (?, ?, ?)
> >
> > If I execute the statement manually, quoting the "size"
> token, it is okay.
> >
> Not the best example I suppose, since 'size' isn't in the
> default oracle
> DBdictionary. The following query, however, has the same
> behaviour, even
> though 'comment' is in the OracleDictionary:
>
> INSERT INTO FB_PRIVILEGES (comment, inheritable, level, role, type,
> username, item_uuid) VALUES (?, ?, ?, ?, ?, ?, ?)
>
> --
> View this message in context:
> http://www.nabble.com/Escaping-reserved-words-in-generated-sql
> --tf2642125.html#a7375647
> Sent from the open-jpa-dev mailing list archive at Nabble.com.
>
>
Re: Escaping reserved words in generated sql?
Posted by "roger.keays" <ro...@ninthavenue.com.au>.
roger.keays wrote:
>
> {prepstmnt 17689439 INSERT INTO FB_BLOBS (contentType, filename, size)
> VALUES (?, ?, ?)
>
> If I execute the statement manually, quoting the "size" token, it is okay.
>
Not the best example I suppose, since 'size' isn't in the default oracle
DBdictionary. The following query, however, has the same behaviour, even
though 'comment' is in the OracleDictionary:
INSERT INTO FB_PRIVILEGES (comment, inheritable, level, role, type,
username, item_uuid) VALUES (?, ?, ?, ?, ?, ?, ?)
--
View this message in context: http://www.nabble.com/Escaping-reserved-words-in-generated-sql--tf2642125.html#a7375647
Sent from the open-jpa-dev mailing list archive at Nabble.com.