Setting schema to use at runtime for generated code

[Ian Clarke]

We have different staging and production database schema, and we use mvn generate-sources to generate the Jooq bindings.  Unfortunately, I discovered that regardless of which database schema is specified in the JDBC URL when I create the SQL Connection, it is overridden by the schema specified in our pom.xml.

Is it possible to set the schema to use at runtime for the generated sources?

Thanks,

Ian.

--
Ian Clarke

Blog: http://blog.locut.us/

[Lukas Eder]

Hello Ian,

> Is it possible to set the schema to use at runtime for the generated
> sources?

Yes, have a look at this section of the manual:
http://www.jooq.org/manual/ADVANCED/SchemaMapping/

You can override schema names both at codegen-time and at runtime, or
make it disappear entirely by specifying a default schema at runtime.

Cheers
Lukas

2012/4/24 Ian Clarke <ian.c...@gmail.com>:

[Ian Clarke]

Ah, thanks Lukas.

So I think my preferred approach would be to use the Schema specified in the JDBC URL that I use to create the Connection objects.  The documentation isn't quite clear on how to do this.  The "Using a Default Schema" section says:

Another option to switch schema names is to use a default schema for the Factory's underlying Connection. Many RDBMS support a USE or SET SCHEMA command, which you can call like this:
// Set the default schema
Schema MY_BOOK_WORLD = ...
create.use(MY_BOOK_WORLD);
// Run queries with factory having a default schema
create.selectFrom(T_AUTHOR).fetch();

I'm confused though - will this use the schema specified for the underlying connection (I assume the one specified in the JDBC URL), or will it use the MY_BOOK_WORLD schema?

Ian.

[Lukas Eder]

> So I think my preferred approach would be to use the Schema specified in the
> JDBC URL that I use to create the Connection objects.  The documentation
> isn't quite clear on how to do this.

Yes, I'm aware of this. It is not really up to date. It should be
improved soon. In the mean time, use something along these lines:

new Factory(connection, dialect,
new Settings().withRenderMapping(
new RenderMapping().withDefaultSchema("MySchema")));

The above Factory will avoid rendering "MySchema". With jOOQ 2.3.0,
there will also be a way to specify Settings.setRenderSchema(false),
to avoid rendering schema names all together. Right now, the default
schema will be your only option.

Cheers
Lukas

[Ian Clarke]

On Tue, Apr 24, 2012 at 1:29 AM, Lukas Eder <lukas...@gmail.com> wrote:

Yes, I'm aware of this. It is not really up to date. It should be
improved soon. In the mean time, use something along these lines:

new Factory(connection, dialect,
   new Settings().withRenderMapping(
   new RenderMapping().withDefaultSchema("MySchema")));

The above Factory will avoid rendering "MySchema". With jOOQ 2.3.0,
there will also be a way to specify Settings.setRenderSchema(false),
to avoid rendering schema names all together. Right now, the default
schema will be your only option.

Ok, so the string "MySchema" is unimportant here?  It could be "blahblah" and would still work?

[Lukas Eder]

>> The above Factory will avoid rendering "MySchema". With jOOQ 2.3.0,
>> there will also be a way to specify Settings.setRenderSchema(false),
>> to avoid rendering schema names all together. Right now, the default
>> schema will be your only option.
>
>
> Ok, so the string "MySchema" is unimportant here?  It could be "blahblah"
> and would still work?

Au contraire. Look at it this way:

- jOOQ by default fully qualifies schemata
- jOOQ is thus capable of running queries against multiple schemata
- Most users probably have only one schema. They want to use that as
the "default schema"
- The default schema can be set in the JDBC string in many databases
- The default schema can be overridden using SQL statements in many
databases. Examples

-- Oracle syntax
ALTER SESSION SET current_schema = my_other_schema;

-- MySQL, Sybase ASE, Sybase SQL Anywhere
USE my_other_schema;

-- DB2, Derby, H2, HSQLDB
SET SCHEMA my_other_schema

- hence, jOOQ can't make any assumption regarding a default schema,
you need to tell jOOQ

So in other words, write this:

new Factory(connection, dialect,
new Settings().withRenderMapping(

new RenderMapping().withDefaultSchema("[the-schema-name-that-you-consider-the-default-schema]")));

As I said, jOOQ 2.3.0 will include a more user-friendly feature for
the simple use-case of not fully qualifying table names...

Hope this helps
Lukas

[Ian Clarke]

Ah, I think I understand, thanks again Lukas!

Ian.