JOOQ+Derby: "Schema does not exist"

[rm....@gmail.com]

Hello.

I have the following test program that creates a Derby database, creates

a schema, creates a table in that schema, and then inserts a few values

into it. I'm able to query that table using raw SQL successfully, but JOOQ

seems to generate SQL that Derby doesn't like:

https://gist.github.com/io7m/8fb4d8c8b3905234d837f68f0b6c4320

Exception in thread "main" org.jooq.exception.DataAccessException: SQL [select "x" from "core"."example"]; Schema 'core' does not exist
 at org.jooq_3.12.1.DERBY.debug(Unknown Source)
 at org.jooq.impl.Tools.translate(Tools.java:2717)
 at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:755)
 at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:383)
 at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:501)
 at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:470)
 at org.jooq.impl.AbstractResultQuery.fetchStream(AbstractResultQuery.java:441)
 at org.jooq.impl.SelectImpl.fetchStream(SelectImpl.java:2724)
 at SchemaIssue.main(SchemaIssue.java:54)

Any assistance would be appreciated. I'm not using code generation,

so I'm assuming that I need to tell JOOQ more information than I've

given it.

[Rob Sargent]

Perhaps the jooq user doesn’t have permission to see the core table(s)

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/abde1207-07ea-4686-b5b9-29594a4d8ac0%40googlegroups.com.

[rm....@gmail.com]

On Saturday, September 28, 2019 at 10:04:13 PM UTC, Rob Sargent wrote:

Perhaps the jooq user doesn’t have permission to see the core table(s)

Hello!

I think it might be a bug in the Derby integration... If I set

withRenderNameStyle(RenderNameStyle.AS_IS)

... the problem goes away. I think Derby might be getting upset with the

level of quoting that jooq uses by default.

[Lukas Eder]

That's a feature not a bug! :) jOOQ by default quotes all identifiers. That's useful to support all the edge cases of case sensitive identifiers, or identifiers containing special characters by default (other ORMs have a lot of trouble with these). It also protects against SQL injection, if users forget to sanitise their inputs to DSL.name().

The problem in your case is that you're creating your objects using JDBC directly, using Derby's default case (UPPER CASE), and then request data from quoted "lower case" identifiers. You have several ways to work around this:

- Create the schema using jOOQ API as well. We support a lot of DDL statements that you could use

- Use the code generator

- Use DSL.unquotedName()

- Use RenderNameStyle - as you did

- Use the plain SQL API (DSL.field(String), instead of DSL.field(Name))

I hope this helps,

Lukas

--
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.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/0e98b093-f88e-4829-8b94-8c3e1efd38ba%40googlegroups.com.

[rm....@gmail.com]

On Monday, September 30, 2019 at 6:47:36 AM UTC, Lukas Eder wrote:

That's a feature not a bug! :) jOOQ by default quotes all identifiers. That's useful to support all the edge cases of case sensitive identifiers, or identifiers containing special characters by default (other ORMs have a lot of trouble with these). It also protects against SQL injection, if users forget to sanitise their inputs to DSL.name().

The problem in your case is that you're creating your objects using JDBC directly, using Derby's default case (UPPER CASE), and then request data from quoted "lower case" identifiers. You have several ways to work around this:

Thanks, this makes sense!