hsqldb and case sensitivity

[Darren S]

I've ran into an issue with HSQLDB and curious if there is any solution you know of.  According to the HSQLDB docs "all parts of SQL statements are converted to upper case before processing, except identifiers in double quotes and strings in single quotes".  So what is happening is select * from book works but select * from "book" does not work as the table name is actually BOOK and HSQLDB seems to check case.  Since jooq always quotes fields this basically means jooq doesn't work with HSQLDB at all for me.  Any thoughts?

I'm thinking that nobody has probably hit this issue because they have mostly likely generated the records from HSQLDB and the meta data has uppercase table names.  What I've done is generated the records from mysql and then tried to use them against hsqldb.  I would like to stick with that approach as mysql is my production DB, but hsqldb is what I use for testing and small setups.  I'm going to try H2 and see if it works better.

Darren

[Darren S]

Wow, found a setting just for this!  setRenderNameStyle(RenderNameStyle.UPPER)  Sorry I didn't see that before.  This is about the 10th time I've ran into an issue and found out there was a some API or setting to address it.  Can't tell you how much I enjoy using jOOQ.

Darren

[Darren S]

I think I ran into a real issue now.  In org.jooq.impl.AbstractStoreQuery.prepare(ExecuteContext) for HSQLDB and other is the following code

                    for (Field<?> field : returning) {
                        names.add(field.getName());
                    }

It seems that field.getName() should follow the renderKeywordStyle parameter.  I hacked up the code to do .toUpperCase() and it resolved my issue.  The issue being that HSQLDB can't find the "id" column on insert.  It wasn't really obvious to me on how to get to RenderContext.keyword() from that point in the code.

Darren

[Lukas Eder]

Hi Darren,

Thanks for your feedback. Glad you've found the appropriate Setting for this issue.

Since you like jOOQ so much, I might talk you into doing some advertising of jOOQ within the Apache Foundation (and elsewhere). I might send you some geeky merchandise. Mornings (and coffee) are just so much better, this way :-)

Cheers

Lukas

2013/11/13 Darren S <darren.s...@gmail.com>

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Lukas Eder]

Hi Darren,

Hmm, yes that's interesting! There might be a couple of other cases, where the RenderKeywordStyle should be used but isn't. This is certainly a bug which should be fixed. I have registered #2845:

https://github.com/jOOQ/jOOQ/issues/2845

Thanks for reporting!

Lukas

2013/11/14 Darren S <darren.s...@gmail.com>

--

[Lukas Eder]

I have fixed this issue for jOOQ 3.3 and will probably merge it to jOOQ 3.2.2. The fix can be seen here:

https://github.com/jOOQ/jOOQ/commit/b863d43e8c1974f5988a2d87c3d33450a2df1507

While adding test cases for this issue, I have noticed once more how delicate case-sensitivity is in SQL. Generally, your best option is to always stay in control of case sensitivity when trying to be SQL agnostic, and to force all your schemas to have the same casing already in DDL

Cheers

Lukas

2013/11/14 Lukas Eder <lukas...@gmail.com>

[Darren Shepherd]

I'll have to double check what I was doing, but it seemed like hsqldb forced the schema names to be uppercase even though the DDL was lowercase.  So maybe my best bet is to change my DDL to be all uppercase. 

Darren

You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/UA2AkO0yFVQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

HSQLDB:

CREATE TABLE T (

  CaseInsensitive INT,

  "CaseSensitive" INT

);

Reported in the INFORMATION_SCHEMA as T.CASEINSENSITIVE, T.CaseSensitive

 

PostgreSQL:

 

CREATE TABLE T (

  CaseInsensitive INT,

  "CaseSensitive" INT

);

Reported in the information_schema as t.caseinsensitive, t.CaseSensitive

The point I'm trying to make is that your best choice to get database agnostic behaviour with respect to casing right is by forcing all DDL to be case-sensitive through quoting names.

 

[Darren Shepherd]

Oh, thanks.  It didn't don on me to quote the names in the DDL.

Darren

--