Problems with HSQLDB and HSQLDBFactory using double quotes

[Ubersoldat]

Hi guys,

First time poster and recent user of JOOQ (loving it so far!)

For testing purposes, I'm creating a database in HSQLDB 2.2.8 with,
for example:

CREATE TABLE market (
id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1
INCREMENT BY 1) PRIMARY KEY,
version TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
name VARCHAR(2),
lang VARCHAR(10),
country VARCHAR(2)
);

INSERT INTO market (name, lang, country) VALUES ('es', 'es', 'ES');

This works OK and with direct JDBC I have had no problems so far. But
when my factory extends from a JOOQ HSQLDBFactory I get the following
query in the debug messages:

select "market"."id", "market"."version", "market"."name",
"market"."lang", "market"."country" from "market" where "market"."id"
= 1

And this breaks the whole thing for HSQLDB:

org.jooq.exception.DataAccessException: AbstractQuery.execute;
SQL [select "market"."id", "market"."version", "market"."name",
"market"."lang", "market"."country" from "market" where "market"."id"
= cast(? as bigint)];
user lacks privilege or object not found: market

Now, when I switch and extend my factory from SQLiteFactory everything
works so far:

select market.id, market.version, market.name, market.lang,
market.country from market where market.id = 1

Seems clear that the problem is with the double quotes, so my question
is, can I turn those off?

BTW, I've tested this in HSQLDB 2.2.4 & 2.2.8 with the same results.

Thanks for the help, and thanks for JOOQ.

[Lukas Eder]

Hello,

> user lacks privilege or object not found: market

That is curious. jOOQ supports case-sensitivity of identifiers in SQL.

> Now, when I switch and extend my factory from SQLiteFactory everything
> works so far:

Yes, jOOQ doesn't apply quotes to SQL generated for SQLite, as there
are some open issues with that.

> Seems clear that the problem is with the double quotes, so my question
> is, can I turn those off?

By default, this is turned on, but you can turn it off using the
Factory's Settings' RenderNameStyle:
http://www.jooq.org/javadoc/latest/org/jooq/conf/Settings.html

What's curious is that you seem to have defined a case-insensitive
table name in lower case letters, which seems to be promoted by HSQLDB
dictionary views as being lower-case, when in fact it is
upper-case...? What does the following query return on your HSQLDB
database?

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE LOWER(TABLE_NAME) = 'market'

2012/5/7 Ubersoldat <alejand...@gmail.com>:

[Ubersoldat]

Hi Lukas, thanks for the quick reply.

This did the trick:

Settings s = getSettings();
s.setRenderNameStyle(RenderNameStyle.AS_IS);

> Yes, jOOQ doesn't apply quotes to SQL generated for SQLite, as there
> are some open issues with that.

It was simply a lucky shot trying different Factories.

> What's curious is that you seem to have defined a case-insensitive
> table name in lower case letters, which seems to be promoted by HSQLDB
> dictionary views as being lower-case, when in fact it is
> upper-case...?

Maybe this was an error on my side by declaring my tables in lower-
case?

>What does the following query return on your HSQLDB
> database?
>
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE LOWER(TABLE_NAME) = 'market'

MARKET

It's working fine now, thanks again.
Regards.

On May 7, 1:36 pm, Lukas Eder <lukas.e...@gmail.com> wrote:
> Hello,
>
> > user lacks privilege or object not found: market
>
> That is curious. jOOQ supports case-sensitivity of identifiers in SQL.
>
> > Now, when I switch and extend my factory from SQLiteFactory everything
> > works so far:
>
> Yes, jOOQ doesn't apply quotes to SQL generated for SQLite, as there
> are some open issues with that.
>
> > Seems clear that the problem is with the double quotes, so my question
> > is, can I turn those off?
>
> By default, this is turned on, but you can turn it off using the
> Factory's Settings' RenderNameStyle:http://www.jooq.org/javadoc/latest/org/jooq/conf/Settings.html
>
> What's curious is that you seem to have defined a case-insensitive
> table name in lower case letters, which seems to be promoted by HSQLDB
> dictionary views as being lower-case, when in fact it is
> upper-case...? What does the following query return on your HSQLDB
> database?
>
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE LOWER(TABLE_NAME) = 'market'
>

> 2012/5/7 Ubersoldat <alejandroay...@gmail.com>:

[Lukas Eder]

> I'll further investigate this issue. There's still a bug somewhere,
> even if it works for you, now

This is tracked as #1401:
https://sourceforge.net/apps/trac/jooq/ticket/1401

[Lukas Eder]

Hello,

Thanks for the feedback.

> Maybe this was an error on my side by declaring my tables in lower-
> case?

No, that should be alright. Without quotes, you can declare tables in
any case. Maybe you're running HSQLDB in some compatibility mode?
MySQL, for instance, has some subtle differences as far as casing is
concerned, between table names and column names.

I'll further investigate this issue. There's still a bug somewhere,
even if it works for you, now

Cheers
Lukas

2012/5/7 Ubersoldat <alejand...@gmail.com>: