Schema does not exist with render mapping

[Mark Manders]

I have a Spring Boot application and generating the code works fine from a PostgreSQL database.

When running the Spring Boot integration tests I get an error message saying that the schema 'public' doesn't exist.

I used 'public' as the inputSchema property during code generation (using Gradle).

At runtime the application works as expected but during the integration tests this doesn't work.

After some googling I came to this http://www.jooq.org/doc/3.8/manual/sql-building/dsl-context/runtime-schema-mapping/.

So I created a @Configuration class that has a @Primary annotated @Bean to make sure that the new settings are applied. This is my configuration:

    @Bean
    @Primary
    public org.jooq.Configuration config() {
        org.jooq.Configuration config = new DefaultConfiguration();
        config.set(SQLDialect.valueOf(sqlDialect));
        config.set(connectionProvider);
        config.set(new Settings().withRenderMapping(
            new RenderMapping().withSchemata(
                new MappedSchema().withInput("public").withOutput("")
            )
        ));
        config.set(new DefaultExecuteListenerProvider(
            new DefaultExecuteListener() {
                @Override
                public void exception(ExecuteContext ctx) {
                    if (null != ctx.sqlException()) {
                        SQLDialect dialect = ctx.dialect();
                        SQLExceptionTranslator translator = (null != dialect) ?
                            new SQLErrorCodeSQLExceptionTranslator(dialect.thirdParty().springDbName()) :
                            new SQLStateSQLExceptionTranslator();


                        ctx.exception(translator.translate("jOOQ", ctx.sql(), ctx.sqlException()));
                    }
                }
            }
        ));
        return config;
    }

In my tests I have the @Autowired DSLContext and during execution of the tests I see in the properties of the DSLContext that the rendermapping is used (see attached screenshot).

But somehow I get an Exception saying that the schema 'public' can't be found.

[Lukas Eder]

Hi Mark,

Can you post that exception, including the SQL statement that goes wrong?

Also, in case you don't really want to work with any schemas at all, you can also turn off Settings.renderSchema...

Cheers,
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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Mark Manders]

Hi,

I removed the renderMapping now and use an in-memory H2 database.

The schema is created as my logging says this:

Hibernate: drop table public.user if exists

Hibernate: create table public.user (id binary not null, account_expired boolean not null, account_locked boolean not null, enabled boolean not null, login_count integer, password varchar(100) not null, password_expired boolean not null, role varchar(50) not null, username varchar(100) not null, primary key (id))

Hibernate: alter table public.user add constraint UK_sb8bbouer5wak8vyiiy4pf2bx unique (username)

This is the exception:

org.springframework.jdbc.UncategorizedSQLException: jOOQ; uncategorized SQLException for SQL [insert into "public"."user" ("id", "username", "password", "role") values (cast(? as uuid), cast(? as varchar), cast(? as varchar), cast(? as varchar))]; SQL state [90079]; error code [90079]; Schema "public" not found; SQL statement:
insert into "public"."user" ("id", "username", "password", "role") values (cast(? as uuid), cast(? as varchar), cast(? as varchar), cast(? as varchar)) [90079-192]; nested exception is org.h2.jdbc.JdbcSQLException: Schema "public" not found; SQL statement:
insert into "public"."user" ("id", "username", "password", "role") values (cast(? as uuid), cast(? as varchar), cast(? as varchar), cast(? as varchar)) [90079-192]
 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84) ~[spring-jdbc-4.3.3.RELEASE.jar:4.3.3.RELEASE]
 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.3.3.RELEASE.jar:4.3.3.RELEASE]
 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.3.3.RELEASE.jar:4.3.3.RELEASE]
 at org.springframework.boot.autoconfigure.jooq.JooqExceptionTranslator.translate(JooqExceptionTranslator.java:89) ~[spring-boot-autoconfigure-1.4.1.RELEASE.jar:1.4.1.RELEASE]
 at org.springframework.boot.autoconfigure.jooq.JooqExceptionTranslator.handle(JooqExceptionTranslator.java:78) ~[spring-boot-autoconfigure-1.4.1.RELEASE.jar:1.4.1.RELEASE]
 at org.springframework.boot.autoconfigure.jooq.JooqExceptionTranslator.exception(JooqExceptionTranslator.java:53) ~[spring-boot-autoconfigure-1.4.1.RELEASE.jar:1.4.1.RELEASE]
 at org.jooq.impl.ExecuteListeners.exception(ExecuteListeners.java:244) [jooq-3.8.4.jar:na]
 at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:363) [jooq-3.8.4.jar:na]
 at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:135) [jooq-3.8.4.jar:na]
.....

Caused by: org.h2.jdbc.JdbcSQLException: Schema "public" not found; SQL statement:

insert into "public"."user" ("id", "username", "password", "role") values (cast(? as uuid), cast(? as varchar), cast(? as varchar), cast(? as varchar)) [90079-192]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.192.jar:1.4.192]

at org.h2.message.DbException.get(DbException.java:179) ~[h2-1.4.192.jar:1.4.192]

at org.h2.message.DbException.get(DbException.java:155) ~[h2-1.4.192.jar:1.4.192]

at org.h2.command.Parser.getSchema(Parser.java:679) ~[h2-1.4.192.jar:1.4.192]

at org.h2.command.Parser.getSchema(Parser.java:685) ~[h2-1.4.192.jar:1.4.192]

at org.h2.command.Parser.readTableOrView(Parser.java:5371) ~[h2-1.4.192.jar:1.4.192]

at org.h2.command.Parser.readTableOrView(Parser.java:5365) ~[h2-1.4.192.jar:1.4.192]

at org.h2.command.Parser.parseInsert(Parser.java:1053) ~[h2-1.4.192.jar:1.4.192]

at org.h2.command.Parser.parsePrepared(Parser.java:413) ~[h2-1.4.192.jar:1.4.192]

at org.h2.command.Parser.parse(Parser.java:317) ~[h2-1.4.192.jar:1.4.192]

at org.h2.command.Parser.parse(Parser.java:289) ~[h2-1.4.192.jar:1.4.192]

at org.h2.command.Parser.prepareCommand(Parser.java:254) ~[h2-1.4.192.jar:1.4.192]

at org.h2.engine.Session.prepareLocal(Session.java:560) ~[h2-1.4.192.jar:1.4.192]

at org.h2.engine.Session.prepareCommand(Session.java:501) ~[h2-1.4.192.jar:1.4.192]

at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1202) ~[h2-1.4.192.jar:1.4.192]

at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:73) ~[h2-1.4.192.jar:1.4.192]

at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:290) ~[h2-1.4.192.jar:1.4.192]

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_72]

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_72]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_72]

at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_72]

at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126) ~[tomcat-jdbc-8.5.5.jar:na]

at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108) ~[tomcat-jdbc-8.5.5.jar:na]

at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81) ~[tomcat-jdbc-8.5.5.jar:na]

at com.sun.proxy.$Proxy94.prepareStatement(Unknown Source) ~[na:na]

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_72]

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_72]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_72]

at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_72]

at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:240) ~[spring-jdbc-4.3.3.RELEASE.jar:4.3.3.RELEASE]

at com.sun.proxy.$Proxy130.prepareStatement(Unknown Source) ~[na:na]

at org.jooq.impl.ProviderEnabledConnection.prepareStatement(ProviderEnabledConnection.java:112) ~[jooq-3.8.4.jar:na]

at org.jooq.impl.SettingsEnabledConnection.prepareStatement(SettingsEnabledConnection.java:76) ~[jooq-3.8.4.jar:na]

at org.jooq.impl.AbstractQuery.prepare(AbstractQuery.java:414) [jooq-3.8.4.jar:na]

at org.jooq.impl.AbstractDMLQuery.prepare(AbstractDMLQuery.java:239) ~[jooq-3.8.4.jar:na]

at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:320) [jooq-3.8.4.jar:na]

... 52 common frames omitted

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

Hi Mark,

Oh, interesting caveat, which might have explained things already before:

- jOOQ by default generates all SQL identifiers with quotes, which means they're case sensitive.

- PostgreSQL identifiers are all lower-case, case insensitive by default

- H2 identifiers are all upper-case, case insensitive by default

When you generate your SQL as you did for H2, you're selecting from a case-sensitive schema "public" (lower-case) that does not exist in H2. It is really called "PUBLIC" (case-sensitive) or public/PUBLIC (case-insensitive).

There are several workarounds. With schema mapping, you could manually upper-case your identifiers, but probably you're better off by avoiding jOOQ's quoting of identifiers entirely by specifying Settings.renderNameStyle == RenderNameStyle.AS_IS

I hope this helps,

Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.