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 2016-09-28 13:30 GMT+02:00 Mark Manders <[email protected]>: > 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.AbstractFallbackSQLExceptio > nTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84) ~[ > spring-jdbc-4.3.3.RELEASE.jar:4.3.3.RELEASE] > at org.springframework.jdbc.support.AbstractFallbackSQLExceptio > nTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[ > spring-jdbc-4.3.3.RELEASE.jar:4.3.3.RELEASE] > at org.springframework.jdbc.support.AbstractFallbackSQLExceptio > nTranslator.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 > > > On Tuesday, September 27, 2016 at 10:52:03 AM UTC+2, Lukas Eder wrote: >> >> 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 >> >> 2016-09-27 8:31 GMT+02:00 Mark Manders <[email protected]>: >> >>> 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. >>> >>> >>> <https://lh3.googleusercontent.com/-YfLcc2jHV6U/V-oSMNV7_CI/AAAAAAAAAps/VP-K4-T8_oY4vFEQF-FC9QYJf05VMN8tgCLcB/s1600/Screen%2BShot%2B2016-09-27%2Bat%2B08.11.58.png> >>> >>> >>> -- >>> 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 [email protected]. >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> -- > 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 [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
