You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@openmeetings.apache.org by "seba.wagner@gmail.com" <se...@gmail.com> on 2013/02/09 06:15:44 UTC

OpenJPA has issues with table name "user" and Apache Derby database

Hi,

we face an issue with OpenJPA 2.2.1 and Derby 10.8.2.2.
When there is a table "user" the schema is correctly created
with SynchronizeMappings.

However making a query in the that database results in:
org.apache.openjpa.persistence.PersistenceException: Syntax error:
Encountered "user" at line 1, column 888. {SELECT t0.id, t0.activatehash,
t1.id, t1.additionalname, t1.comment_field, t1.deleted, t1.email, t1.fax,
t1.phone, t1.starttime, t2.id, t2.code, t2.deleted, t2.name, t2.shortName,
t2.starttime, t2.updatetime, t1.street, t1.town, t1.updatetime, t1.zip,
t0.age, t0.availible, t0.deleted, t0.externalUserId, t0.externalUserType,
t0.firstname, t0.forceTimeZoneCheck, t0.language_id, t0.lastlogin,
t0.lastname, t0.lasttrans, t0.level_id, t0.login, t3.id, t3.ical,
t3.inserted, t3.jname, t3.label, t3.order_id, t0.pictureuri, t0.regdate,
t0.resethash, t0.salutations_id, t0.sendSMS, t0.show_contact_data,
t0.show_contact_data_to_contacts, t4.id, t4.context, t4.defaultuser,
t4.fullcontact, t4.host, t4.ipaddr, t4.lastms, t4.md5secret, t4.name,
t4.port, t4.regseconds, t4.regserver, t4.secret, t4.type, t4.useragent,
t0.starttime, t0.status, t0.updatetime, t0.user_offers, t0.user_searchs
FROM user t0 LEFT OUTER JOIN address t1 ON t0.adresses_id = t1.id LEFT
OUTER JOIN om_timezone t3 ON t0.omtimezoneId = t3.id LEFT OUTER JOIN
sipusers t4 ON t0.sip_user_id = t4.id LEFT OUTER JOIN state t2 ON
t1.state_id = t2.id WHERE (t0.deleted = ?)} [code=30000, state=42X01]
    at
org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4958)
~[openjpa-bundle-2.2.1.jar:2.2.1]
    at
org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4918)
~[openjpa-bundle-2.2.1.jar:2.2.1]
    at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
~[openjpa-bundle-2.2.1.jar:2.2.1]
    at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:118)
~[openjpa-bundle-2.2.1.jar:2.2.1]
    at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:70)
~[openjpa-bundle-2.2.1.jar:2.2.1]

I guess this happens because "user" needs to be correctly escaped.
Is that a known issue in OpenJPA 2.2.1 ?

Sebastian

-- 
Sebastian Wagner
https://twitter.com/#!/dead_lock
http://www.webbase-design.de
http://www.wagner-sebastian.com
seba.wagner@gmail.com

Re: OpenJPA has issues with table name "user" and Apache Derby database

Posted by Rick Curtis <cu...@gmail.com>.

No, I never found the time to look into this issue.

Thanks,
Rick


On Mon, Jun 3, 2013 at 9:37 PM, Maxim Solodovnik <so...@gmail.com>wrote:

> Hello Rick,
>
> were you able to take a look at this issue?
>
>
> On Tue, Feb 12, 2013 at 9:30 PM, Rick Curtis <cu...@gmail.com> wrote:
>
> > Sebastian -
> >
> > Yes, I believe this is a bug. That being said, there might be already
> > support for enabling escaping / delimiting table names in DBDictionary...
> > I'd look myself, but I don't have a dev environment on this machine. Let
> us
> > know what you find out.
> >
> > Thanks,
> > Rick
> >
> >
> > On Mon, Feb 11, 2013 at 1:35 AM, seba.wagner@gmail.com <
> > seba.wagner@gmail.com> wrote:
> >
> > > I might be willing to look into the OpenJPA source code to find out the
> > > root of the issue,
> > > but it would be good if somebody could confirm that this is a bug that
> > > needs to be fixed.
> > >
> > > Sebastian
> > >
> > >
> > > 2013/2/10 seba.wagner@gmail.com <se...@gmail.com>
> > >
> > > > Btw: The same issue exists with Postgres. We can't choose a table
> name
> > > > "user" cause the SQL is not quoted in the sql for string "user".
> > > >
> > > > Sebastian
> > > >
> > > >
> > > > 2013/2/10 seba.wagner@gmail.com <se...@gmail.com>
> > > >
> > > > Yes,
> > > >>
> > > >> actually you have those reserved keywords in all database. A subset
> of
> > > >> those is common across all, some are special in each.
> > > >> But of course it does not mean that those strings are "forbidden" to
> > > use.
> > > >> It simply means you have to escape them! (Or double quoting as the
> > link
> > > >> to the derby 10.8 docs say).
> > > >>
> > > >> For example the word "open" is also a reserved keyword according to
> > the
> > > >> list. But of course you can name a column "open" in OpenJPA, because
> > it
> > > is
> > > >> somehow correctly escaped when the SQL is generated. "user" seems to
> > be
> > > not
> > > >> correctly escaped. That is the problem.
> > > >>
> > > >> Sebastian
> > > >>
> > > >>
> > > >>
> > > >> 2013/2/10 Maxim Solodovnik <so...@gmail.com>
> > > >>
> > > >>> Hello Jesse,
> > > >>> Thanks for the link!
> > > >>>
> > > >>> Is there any option to tell OpenJPA to generate derby compatible
> SQL?
> > > >>>
> > > >>>
> > > >>> On Sat, Feb 9, 2013 at 8:11 PM, Jesse Farinacci <ji...@gmail.com>
> > > >>> wrote:
> > > >>>
> > > >>> > Greetings,
> > > >>> >
> > > >>> > On Sat, Feb 9, 2013 at 12:15 AM, seba.wagner@gmail.com
> > > >>> > <se...@gmail.com> wrote:
> > > >>> > > we face an issue with OpenJPA 2.2.1 and Derby 10.8.2.2.
> > > >>> > > When there is a table "user" the schema is correctly created
> > > >>> > > with SynchronizeMappings.
> > > >>> > >
> > > >>> > > However making a query in the that database results in:
> > > >>> > > org.apache.openjpa.persistence.PersistenceException: Syntax
> > error:
> > > >>> > > Encountered "user" at line 1, column 888.
> > > >>> >
> > > >>> > http://db.apache.org/derby/docs/10.8/ref/rrefkeywords29722.html
> > > >>> >
> > > >>> > -Jesse
> > > >>> >
> > > >>> > --
> > > >>> > There are 10 types of people in this world, those
> > > >>> > that can read binary and those that can not.
> > > >>> >
> > > >>>
> > > >>>
> > > >>>
> > > >>> --
> > > >>> WBR
> > > >>> Maxim aka solomax
> > > >>>
> > > >>
> > > >>
> > > >>
> > > >> --
> > > >> Sebastian Wagner
> > > >> https://twitter.com/#!/dead_lock
> > > >> http://www.webbase-design.de
> > > >> http://www.wagner-sebastian.com
> > > >> seba.wagner@gmail.com
> > > >>
> > > >
> > > >
> > > >
> > > > --
> > > > Sebastian Wagner
> > > > https://twitter.com/#!/dead_lock
> > > > http://www.webbase-design.de
> > > > http://www.wagner-sebastian.com
> > > > seba.wagner@gmail.com
> > > >
> > >
> > >
> > >
> > > --
> > > Sebastian Wagner
> > > https://twitter.com/#!/dead_lock
> > > http://www.webbase-design.de
> > > http://www.wagner-sebastian.com
> > > seba.wagner@gmail.com
> > >
> >
> >
> >
> > --
> > *Rick Curtis*
> >
>
>
>
> --
> WBR
> Maxim aka solomax
>



-- 
*Rick Curtis*

Re: OpenJPA has issues with table name "user" and Apache Derby database

Posted by Maxim Solodovnik <so...@gmail.com>.

Hello Rick,

were you able to take a look at this issue?


On Tue, Feb 12, 2013 at 9:30 PM, Rick Curtis <cu...@gmail.com> wrote:

> Sebastian -
>
> Yes, I believe this is a bug. That being said, there might be already
> support for enabling escaping / delimiting table names in DBDictionary...
> I'd look myself, but I don't have a dev environment on this machine. Let us
> know what you find out.
>
> Thanks,
> Rick
>
>
> On Mon, Feb 11, 2013 at 1:35 AM, seba.wagner@gmail.com <
> seba.wagner@gmail.com> wrote:
>
> > I might be willing to look into the OpenJPA source code to find out the
> > root of the issue,
> > but it would be good if somebody could confirm that this is a bug that
> > needs to be fixed.
> >
> > Sebastian
> >
> >
> > 2013/2/10 seba.wagner@gmail.com <se...@gmail.com>
> >
> > > Btw: The same issue exists with Postgres. We can't choose a table name
> > > "user" cause the SQL is not quoted in the sql for string "user".
> > >
> > > Sebastian
> > >
> > >
> > > 2013/2/10 seba.wagner@gmail.com <se...@gmail.com>
> > >
> > > Yes,
> > >>
> > >> actually you have those reserved keywords in all database. A subset of
> > >> those is common across all, some are special in each.
> > >> But of course it does not mean that those strings are "forbidden" to
> > use.
> > >> It simply means you have to escape them! (Or double quoting as the
> link
> > >> to the derby 10.8 docs say).
> > >>
> > >> For example the word "open" is also a reserved keyword according to
> the
> > >> list. But of course you can name a column "open" in OpenJPA, because
> it
> > is
> > >> somehow correctly escaped when the SQL is generated. "user" seems to
> be
> > not
> > >> correctly escaped. That is the problem.
> > >>
> > >> Sebastian
> > >>
> > >>
> > >>
> > >> 2013/2/10 Maxim Solodovnik <so...@gmail.com>
> > >>
> > >>> Hello Jesse,
> > >>> Thanks for the link!
> > >>>
> > >>> Is there any option to tell OpenJPA to generate derby compatible SQL?
> > >>>
> > >>>
> > >>> On Sat, Feb 9, 2013 at 8:11 PM, Jesse Farinacci <ji...@gmail.com>
> > >>> wrote:
> > >>>
> > >>> > Greetings,
> > >>> >
> > >>> > On Sat, Feb 9, 2013 at 12:15 AM, seba.wagner@gmail.com
> > >>> > <se...@gmail.com> wrote:
> > >>> > > we face an issue with OpenJPA 2.2.1 and Derby 10.8.2.2.
> > >>> > > When there is a table "user" the schema is correctly created
> > >>> > > with SynchronizeMappings.
> > >>> > >
> > >>> > > However making a query in the that database results in:
> > >>> > > org.apache.openjpa.persistence.PersistenceException: Syntax
> error:
> > >>> > > Encountered "user" at line 1, column 888.
> > >>> >
> > >>> > http://db.apache.org/derby/docs/10.8/ref/rrefkeywords29722.html
> > >>> >
> > >>> > -Jesse
> > >>> >
> > >>> > --
> > >>> > There are 10 types of people in this world, those
> > >>> > that can read binary and those that can not.
> > >>> >
> > >>>
> > >>>
> > >>>
> > >>> --
> > >>> WBR
> > >>> Maxim aka solomax
> > >>>
> > >>
> > >>
> > >>
> > >> --
> > >> Sebastian Wagner
> > >> https://twitter.com/#!/dead_lock
> > >> http://www.webbase-design.de
> > >> http://www.wagner-sebastian.com
> > >> seba.wagner@gmail.com
> > >>
> > >
> > >
> > >
> > > --
> > > Sebastian Wagner
> > > https://twitter.com/#!/dead_lock
> > > http://www.webbase-design.de
> > > http://www.wagner-sebastian.com
> > > seba.wagner@gmail.com
> > >
> >
> >
> >
> > --
> > Sebastian Wagner
> > https://twitter.com/#!/dead_lock
> > http://www.webbase-design.de
> > http://www.wagner-sebastian.com
> > seba.wagner@gmail.com
> >
>
>
>
> --
> *Rick Curtis*
>



-- 
WBR
Maxim aka solomax