MY_SCHEMA working for DDL but not DML statements

Greg Nielsen

unread,

Oct 7, 2020, 1:03:02 AM10/7/20

to jOOQ User Group

Hello,

Using jOOQ Professional, we are writing to a SQL Server instance. We're finding that only the DDL statements (eg create tables) seem to honor the MY-SCHEMA mapped schema, while the DML (eg inserts) is not.

We're initializing our Settings object with:

@Bean
public Settings settings() {
return new Settings()
.withRenderCatalog(true)
.withRenderSchema(true)
.withRenderMapping(new RenderMapping()
.withSchemata(new MappedSchema()
.withInputExpression(Pattern.compile(".*+"))
.withOutput("MY_SCHEMA")) );
}

I believe we're runing 3.13 - is there an additional setting we need to be configuring?

Let me know what other info might be helpful.

Thanks,

Greg

Lukas Eder

unread,

Oct 7, 2020, 3:48:33 AM10/7/20

to jOOQ User Group

Hi Greg,

Thanks for your message. There's nothing wrong with your setup. This should work for DDL and DML alike. There is one caveat I can think of, though. How do you create your DML statements? If you're using plain SQL templating API (https://www.jooq.org/doc/latest/manual/sql-building/plain-sql-templating/), then schema mapping does not apply, because we don't parse the templates. You can recognise plain SQL templating API as it is annotated with org.jooq.PlainSQL. In that case, you probably should use DSL.name() to construct your identifiers instead.

Note, I would recommend using "^.*$" as a pattern to prevent generating MY_SCHEMAMY_SCHEMA as can be shown here:

jshell> "abc".replaceAll(".*+", "xyz")
$1 ==> "xyzxyz"

jshell> "abc".replaceAll("^.*$", "xyz")
$2 ==> "xyz"

Thanks,

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/f1597c43-6dc6-41d8-afc5-a957d9cd4b76n%40googlegroups.com.

Megan A

unread,

Oct 7, 2020, 2:06:14 PM10/7/20

to jOOQ User Group

Hi Lukas,

I'm working on this issue. Thanks to Greg for starting his conversation. I switched from:

context.mergeInto(table(tableName), columnNames)

to

context.mergeInto(table(name(tableName)), columnNames)

And it seems to be working now! Is there a reason table() would not do a name()?

Two things:

1. There is actually no schema set by default. So the replacement was trying to match "empty", is there a better way of doing this?
2. Is this the best way to get support? We have a licence and are using SQLServer and I'm having issues around temp tables.

Thanks so much for your help,

Megan

Lukas Eder

unread,

Oct 8, 2020, 3:53:32 AM10/8/20

to jOOQ User Group

Hi Megan,

DSL.table(String) is part of the plain SQL templating APIs, whereas DSL.table(Name) constructs a table from an identifier. The purpose of the plain SQL templating API is to be able to construct templates for features that jOOQ doesn't support, such as e.g.

context.selectFrom(table("some_table_valued_function(x <fancy_operator> y)")).fetch();

More information about plain SQL templating can be found here:

https://www.jooq.org/doc/latest/manual/sql-building/plain-sql/

https://www.jooq.org/doc/latest/manual/sql-building/plain-sql-templating/

We cannot assume that the template is simply an identifier. And because we don't know what's in there, we cannot parse it either, we might not understand its syntax. Now, there are some APIs, mainly the DDL APIs, where templates never make sense. In those cases, the String overloads of the DSL correspond to passing a Name instance, not a template (org.jooq.SQL) instance.

This blog post here wraps it up nicely. What’s a “String” in the jOOQ API?

https://blog.jooq.org/2020/04/03/whats-a-string-in-the-jooq-api/

Regarding your questions:

1. There is actually no schema set by default. So the replacement was trying to match "empty", is there a better way of doing this?

You can also match the empty string, using

new MappedSchema().withInput("").withOutput("x")

In your case, the mapping will obviously apply to all schemas, which might not be desired depending on how you aim to implement this. Of course, if you're constructing identifiers throughout your application, there might be a way to abstract over these identifiers already from within your application and automatically add the right schema name to the identifier, e.g. by using

table(name(schemaName, tableName))

2. Is this the best way to get support? We have a licence and are using SQLServer and I'm having issues around temp tables.

This is a good way to get support. A lot of people read this mailing list, and if you have a question that is more about how to best put jOOQ into use in some scenarios, there might be someone who has an idea of how they built things. Other alternatives for community support (where we also reply) are:

- Stack Overflow https://stackoverflow.com/questions/tagged/jooq

- Github https://github.com/jOOQ/jOOQ/issues/new/choose

- Reddit https://www.reddit.com/r/jOOQ

An advantage of the above community support channels is that it's public, so as in your case, a coworker can continue the discussion easily.

You can also send an email to our commercial support email address, which gives you access to our professional support. Benefits of that are guaranteed reaction times, although we usually respond quite fast on the community support channels as well, and 1:1 support, in case you need to share confidential information, for which the community support channel is not a good place.

Pick the one that you feel suits you the most.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/e5ce7303-78c4-4066-b618-1d2ef1123ad7n%40googlegroups.com.

Megan A

unread,

Oct 12, 2020, 12:25:11 PM10/12/20

to jOOQ User Group

Hi Lukas,

So I've run into an NPE when doing the following:

context.informationSchema(table(name("MY_TABLE_NAME"))).getColumns();

The NPE seems to be coming from lines 98 -99 in InformationSchemaExport

for (Schema s : includedSchemas)
includedCatalogs.add(s.getCatalog());

If I force in a schema:

context.informationSchema(table(name("MY_HARD_CODED_SCHEMA", "MY_TABLE_NAME"))).getColumns();

I get an NPE just a few lines later.

for (Catalog c : includedCatalogs)
exportCatalog0(result, c);

Are my Settings (above) incorrect? Changing "render catalog" above does nothing to change the behavior?

I have the same issues using meta().

Thanks :)

Lukas Eder

unread,

Oct 12, 2020, 12:53:40 PM10/12/20

to jOOQ User Group

Hi Megan,

Thanks for reporting those NPE. I'll look into this ASAP. There should obviously be more meaningful error messages.

In any case, what are you trying to do with those method calls? DSLContext::informationSchema turns jOOQ meta data into the JAXB annotated information schema format, which can be exported as XML. Since you're calling InformationSchema::getColumns on it, I'd say you might be more interested in calling something like

for (Table table : context.meta().getTables("MY_TABLE_NAME")) {
Field<?>[] fields = table.fields();
}

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/b747ddcb-cdf3-4f90-9c53-86b3c1f1cc82n%40googlegroups.com.

Megan A

unread,

Oct 12, 2020, 1:54:31 PM10/12/20

to jOOQ User Group

Yes! That's what I wanted. I must have been confused because to do this manually I had to query the "INFORMATION_SCHEMA" table.

In the grand scheme of things though what I really need is this: https://blog.jooq.org/category/migrations/

So I can do to something like this:

ctx.meta(

"create table t (i int)"

).diff(ctx.meta(

"create table t ("

+ "i int not null, "

+ "j int null, "

+ "primary key (i))"

))

But it looks like that is still in development. So I have to look at what columns are in the DB and add the ones that are missing.

Rob Sargent

unread,

Oct 12, 2020, 4:40:02 PM10/12/20

to jooq...@googlegroups.com

I’m curious about the actual context in which such a comparison is being performed. In particular, is the comparison between two active databases across separate connections?, separate vendors?. Or is this a check that the jooQ connected db matches some static definition?

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/465bb79b-f1e9-414c-acdd-e182218dcb3an%40googlegroups.com.

Megan A

unread,

Oct 12, 2020, 4:58:24 PM10/12/20

to jOOQ User Group

So my exact circumstances are pretty convoluted. I think good equivalent would be "I get a json file with new fields, put the fields in the DB". In my case I also know type information so it's a little bit more reasonable.

Rob Sargent

unread,

Oct 12, 2020, 5:07:50 PM10/12/20

to jooq...@googlegroups.com

"put the fields in the db" ... deduce DDL, apply to dev database,
regenerate the java code, compile, run all tests, migrate production
database?

>> <http://gmail.com/>wrote:

>> - Githubhttps://github.com/jOOQ/jOOQ/issues/new/choose

>> - Reddit https://www.reddit.com/r/jOOQ
>>
>> An advantage of the above community support channels
>> is that it's public, so as in your case, a coworker
>> can continue the discussion easily.
>>
>> You can also send an email to our commercial support
>> email address, which gives you access to our
>> professional support. Benefits of that are guaranteed
>> reaction times, although we usually respond quite fast
>> on the community support channels as well, and 1:1
>> support, in case you need to share confidential
>> information, for which the community support channel
>> is not a good place.
>>
>> Pick the one that you feel suits you the most.
>>
>> On Wed, Oct 7, 2020 at 8:06 PM Megan A
>> <megan....@gmail.com <http://gmail.com/>> wrote:
>>
>> Hi Lukas,
>>
>> I'm working on this issue. Thanks to Greg for
>> starting his conversation. I switched from:
>>
>> context.mergeInto(table(tableName), columnNames)
>>
>> to
>>

>> context.mergeInto(table(*name*(tableName)), columnNames)

>>
>> And it seems to be working now! Is there a
>> reasontable()would not do aname()?
>>
>> Two things:
>>
>> 1. There is actually no schema set by default. So
>> the replacement was trying to match "empty", is
>> there a better way of doing this?
>> 2. Is this the best way to get support? We have a
>> licence and are using SQLServer and I'm having
>> issues around temp tables.
>>
>> Thanks so much for your help,
>>
>> Megan
>>
>> On Wednesday, October 7, 2020 at 12:48:33 AM

>> UTC-7lukas...@gmail.com <http://gmail.com/>wrote:

>> <http://googlegroups.com/>.

>> To view this discussion on the web
>> visithttps://groups.google.com/d/msgid/jooq-user/f1597c43-6dc6-41d8-afc5-a957d9cd4b76n%40googlegroups.com

>> <https://groups.google.com/d/msgid/jooq-user/f1597c43-6dc6-41d8-afc5-a957d9cd4b76n%40googlegroups.com?utm_medium=email&utm_source=footer>.

>>
>>
>> --
>> 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
>> tojooq-user+...@googlegroups.com

>> <http://googlegroups.com/>.

>>
>> To view this discussion on the web

>> visithttps://groups.google.com/d/msgid/jooq-user/e5ce7303-78c4-4066-b618-1d2ef1123ad7n%40googlegroups.com
>> <https://groups.google.com/d/msgid/jooq-user/e5ce7303-78c4-4066-b618-1d2ef1123ad7n%40googlegroups.com?utm_medium=email&utm_source=footer>.

>>
>>
>> --
>> 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 tojooq-user+...@googlegroups.com.
>>
>> To view this discussion on the web

>> visithttps://groups.google.com/d/msgid/jooq-user/b747ddcb-cdf3-4f90-9c53-86b3c1f1cc82n%40googlegroups.com
>> <https://groups.google.com/d/msgid/jooq-user/b747ddcb-cdf3-4f90-9c53-86b3c1f1cc82n%40googlegroups.com?utm_medium=email&utm_source=footer>.

>>
>>
>> --
>> 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 tojooq-user+...@googlegroups.com.
>> To view this discussion on the web

>> visithttps://groups.google.com/d/msgid/jooq-user/465bb79b-f1e9-414c-acdd-e182218dcb3an%40googlegroups.com
>> <https://groups.google.com/d/msgid/jooq-user/465bb79b-f1e9-414c-acdd-e182218dcb3an%40googlegroups.com?utm_medium=email&utm_source=footer>.

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

> <mailto:jooq-user+...@googlegroups.com>.

> To view this discussion on the web visit

> https://groups.google.com/d/msgid/jooq-user/f53269b8-9351-4d93-9805-31107e64c30en%40googlegroups.com
> <https://groups.google.com/d/msgid/jooq-user/f53269b8-9351-4d93-9805-31107e64c30en%40googlegroups.com?utm_medium=email&utm_source=footer>.

Megan A

unread,

Oct 12, 2020, 5:19:33 PM10/12/20

to jOOQ User Group

I get an object, with metadata. If the metadata doesn't match the DB, evolve the DB, put the data in. There is no traditional Java bean representing this object, so no code gen. It's more like a Map with field type information. And yes I evolve the target DB, even if that DB is production. The target DB is expressly for the purpose of depositing transformed and normalized data from possibly hydrogenous sources.

There is no code for me to break, so unit tests are more around manipulating the metadata and type/name mapping.

Megan

Rob Sargent

unread,

Oct 12, 2020, 5:39:47 PM10/12/20

to jooq...@googlegroups.com

Interesting. You're only using jOOQ for DML then? (And I take it you
mean heterogeneous sources.)

> <http://stackoverflow.com/questions/tagged/jooq>
> >> - Githubhttps://github.com/jOOQ/jOOQ/issues/new/choose
> <http://github.com/jOOQ/jOOQ/issues/new/choose>

> <http://groups.google.com/d/msgid/jooq-user/f1597c43-6dc6-41d8-afc5-a957d9cd4b76n%40googlegroups.com>

>
> >>
> <https://groups.google.com/d/msgid/jooq-user/f1597c43-6dc6-41d8-afc5-a957d9cd4b76n%40googlegroups.com?utm_medium=email&utm_source=footer>.
>
> >>
> >>
> >> --
> >> 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
> >> tojooq-user+...@googlegroups.com
> >> <http://googlegroups.com/>.
> >>
> >> To view this discussion on the web
> >>
> visithttps://groups.google.com/d/msgid/jooq-user/e5ce7303-78c4-4066-b618-1d2ef1123ad7n%40googlegroups.com

> <http://groups.google.com/d/msgid/jooq-user/e5ce7303-78c4-4066-b618-1d2ef1123ad7n%40googlegroups.com>

>
> >>
> <https://groups.google.com/d/msgid/jooq-user/e5ce7303-78c4-4066-b618-1d2ef1123ad7n%40googlegroups.com?utm_medium=email&utm_source=footer>.
>
> >>
> >>
> >> --
> >> 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 tojooq-user+...@googlegroups.com.
> >>
> >> To view this discussion on the web
> >>
> visithttps://groups.google.com/d/msgid/jooq-user/b747ddcb-cdf3-4f90-9c53-86b3c1f1cc82n%40googlegroups.com

> <http://groups.google.com/d/msgid/jooq-user/b747ddcb-cdf3-4f90-9c53-86b3c1f1cc82n%40googlegroups.com>

>
> >>
> <https://groups.google.com/d/msgid/jooq-user/b747ddcb-cdf3-4f90-9c53-86b3c1f1cc82n%40googlegroups.com?utm_medium=email&utm_source=footer>.
>
> >>
> >>
> >> --
> >> 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 tojooq-user+...@googlegroups.com.
> >> To view this discussion on the web
> >>
> visithttps://groups.google.com/d/msgid/jooq-user/465bb79b-f1e9-414c-acdd-e182218dcb3an%40googlegroups.com

> <http://groups.google.com/d/msgid/jooq-user/465bb79b-f1e9-414c-acdd-e182218dcb3an%40googlegroups.com>

>
> >>
> <https://groups.google.com/d/msgid/jooq-user/465bb79b-f1e9-414c-acdd-e182218dcb3an%40googlegroups.com?utm_medium=email&utm_source=footer>.
>
> >
> > --
> > 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
> > <mailto:jooq-user+...@googlegroups.com>.
> > To view this discussion on the web visit
> >
> https://groups.google.com/d/msgid/jooq-user/f53269b8-9351-4d93-9805-31107e64c30en%40googlegroups.com
>
> >
> <https://groups.google.com/d/msgid/jooq-user/f53269b8-9351-4d93-9805-31107e64c30en%40googlegroups.com?utm_medium=email&utm_source=footer>.
>
>
> --
> 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
> <mailto:jooq-user+...@googlegroups.com>.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/jooq-user/209314ab-47ec-40e0-9b21-220f252ba84dn%40googlegroups.com
> <https://groups.google.com/d/msgid/jooq-user/209314ab-47ec-40e0-9b21-220f252ba84dn%40googlegroups.com?utm_medium=email&utm_source=footer>.

Megan A

unread,

Oct 12, 2020, 5:46:32 PM10/12/20

to jOOQ User Group

Luckily I code better then I spell.

But yes, jOOQ for DML and DDL via things like context.alterTable().

Lukas Eder

unread,

Oct 13, 2020, 5:13:56 AM10/13/20

to jOOQ User Group

On Mon, Oct 12, 2020 at 7:54 PM Megan A <megan....@gmail.com> wrote:

Yes! That's what I wanted. I must have been confused because to do this manually I had to query the "INFORMATION_SCHEMA" table.

I see, that's a bit confusing, indeed. For background info: We chose to re-use the name information_schema, because that's a SQL standard schema which has been implemented by a few RDBMS, including SQL Server. Our own version of it also reflects the specification, but in XML format:

https://www.jooq.org/xsd/jooq-meta-3.13.3.xsd

In the grand scheme of things though what I really need is this: https://blog.jooq.org/category/migrations/

So I can do to something like this:

ctx.meta(
    "create table t (i int)"
  ).diff(ctx.meta(
    "create table t ("
  + "i int not null, "
  + "j int null, "
  + "primary key (i))"
  ))

But it looks like that is still in development. So I have to look at what columns are in the DB and add the ones that are missing.

Indeed, there's a lot in this area that is still in development. However, from how you described your use-cases to Rob, I think you can already use what's there for many of your purposes. The question is: How do you want to describe your dynamic metadata?

jOOQ can create org.jooq.Meta types from various information sources:

- Generated code (which you don't have). Relevant methods are DSLContext.meta(Catalog...), meta(Schema...), meta(Table...)

- JDBC DatabaseMetaData. The relevant method is DSLContext.meta(DatabaseMetaData), or just DSLContext.meta() (if you don't change the Configuration.metaProvider())

- XML or the InformationSchema classes (both are the same, via JAXB). The relevant method is DSLContext.meta(InformationSchema)

- DDL in string form, file form, or jOOQ DSL form. The relevant methods are DSLContext.meta(String...), meta(Source...), meta(Query...)

The Meta.diff() method can then produce DDL for any pair of meta types.

Among all the input formats, perhaps the XML format (or its equivalent Java version, the InformationSchema) might be the most manageable. You could maintain an InformationSchema (which you can also extract from other Meta sources, e.g. via Meta::informationSchema), manipulate it, e.g. by adding columns to it, and then generate a diff.

The devil is in the details. For example, the XML format might not be able to handle some vendor specific data types, such as arrays, nor do we support altering all vendor specific data types, should you need that. But I think this approach could already get you quite far with jOOQ 3.13 and soon 3.14. We're always open to feature requests in this area, as this will be of high strategic value to us in the near future.

Thanks,

Lukas

Lukas Eder

unread,

Oct 13, 2020, 5:34:05 AM10/13/20

to jOOQ User Group

On Monday, October 12, 2020 at 6:53:40 PM UTC+2 Lukas Eder wrote:

Thanks for reporting those NPE. I'll look into this ASAP. There should obviously be more meaningful error messages.

For the record, I can reproduce the NPEs. They will be tracked here:

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

Megan A

unread,

Oct 13, 2020, 11:46:19 AM10/13/20

to jOOQ User Group

Humm I don't seem to have access to Meta.diff(). Is that in 3.13.5? I'm on 3.13.4.

Lukas Eder

unread,

Oct 13, 2020, 12:15:24 PM10/13/20

to jOOQ User Group

The method has been renamed to Meta::migrateTo for clarity, because with "diff", it's unclear in which direction the diff is going. I'll update the outdated blog post

Thanks,

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/2ab9672e-5436-4eee-a989-c41feff2d371n%40googlegroups.com.

Megan A

unread,

Oct 15, 2020, 3:44:05 PM10/15/20

to jOOQ User Group

Thanks Lukas,

I'm facing a new issue. Sometimes when SQL Server has a problem and an exception is throw I see an NPE.

In JooqExceptionTranslator around line 80:

private void handle(ExecuteContext context, SQLExceptionTranslator translator, SQLException exception) {
     DataAccessException translated = translate(context, translator, exception);
     if (exception.getNextException() == null) {
         context.exception(translated);
     }
     else {
         logger.error("Execution of SQL statement failed.", translated);
     }

}

Translated = null and triggers an NPE, but exception is not and has a message. In this one case it's "Column names in each table must be unique. Column name 'last_modified_date' in table 'THE_TABLE_NAME' is specified more than once."

Is this a known issue or something that I haven't configured?

Thanks,

Megan

Lukas Eder

unread,

Oct 16, 2020, 4:41:54 AM10/16/20

to jOOQ User Group

Hi Megan,

Thanks for your message.

I think that's JooqExceptionTranslator from Spring Boot? I'm not aware of any such NullPointerException. I'd need some help to reproduce this. Do you have a minimal test case showing how a NullPointerException can be reproduced?

Alternatively, what's the stack trace of the NPE? Does it lead to jOOQ code, Spring Boot code, or your own?

Thanks,

Lukas

Megan A

unread,

Oct 19, 2020, 12:09:42 PM10/19/20

to jOOQ User Group

Good point I'll investigate the spring boot integration.

Reply all

Reply to author

Forward