Routines Don't Respect SchemaMapping

[dacc]

I have a custom function in the database that I incorporate into queries. I use my Routine class to create instances of it as a Field. When I run my queries, however, the routine doesn't respect my schema mapping, and instead always accesses the routine at its codegen-time schema.

[lukaseder]

Hmm, the integration tests don't explicitly cover routines. This worked before, but there's probably a regression that has been introduced with version 1.6.8:
https://sourceforge.net/apps/trac/jooq/ticket/852

Can you provide me with an example of how you're using routines with schema mappings? Are you calling them as standalone routines or embedded in SQL?

Cheers
Lukas

[lukaseder]

This is filed as Trac ticket #995
https://sourceforge.net/apps/trac/jooq/ticket/995

[dacc]

We're calling them embedded in SQL by composing them in as Field, in this case. I traced into the Routine class and couldn't see anywhere to supply a Configuration, except in the usage where it actually calls the function in the DB and returns a value.

[lukaseder]

Hmm, yes. The bug is here:
https://github.com/lukaseder/jOOQ/blob/ee409d83fbdbd0401f37926967b027937167134e/jOOQ/src/main/java/org/jooq/impl/AbstractRoutine.java#L540

The schema name is hard-wired in the routine's Field representation at SQL build time, not at SQL render time. Unfortunately, at SQL build time, the SchemaMapping is not available.

A workaround that might work for you before I fix this, is to replace the call to getQualifiedName() by getName() and omit the schema?

Are you using jOOQ 2.0 or 1.x?

[dacc]

We're using jOOQ 2.0.0-SNAPSHOT.

We have a work-around right now, which is simply to create a database with only the function in it so that jOOQ finds it where expected.

Thanks!

[lukaseder]

This issue is fixed on SVN Trunk, SVN 1.x branch, Github and will be included in the next 2.0.1-SNAPSHOT

[dacc]

Awesome, thank you.