Hi Sajid, Thank you very much for your message. I will comment inline
On Sun, Jan 13, 2019 at 7:23 PM Sajid Nasir <[email protected]> wrote: > We are using JOOQ 3.11.1 > > Our java DB stack was implemented using hibernate, but now we're trying to > use JOOQ. We are trying to flip to our JOOQ production code for a small set > of entities at a time. We have tests that run on local MYSQL DB instances > and work successfully. Our code works in our production MYSQL environments > as well. We have integration tests on HSQLDB. > In general, I strongly advise against this practice. If you're using MySQL in production and in development environments, I suggest using MySQL for your integration tests as well. With Docker and/or https://www.testcontainers.org, and similar tools, this has become comparatively easy to do. Regardless if you're using Hibernate or jOOQ or both, using HSQLDB in your integration test environment will cause many problems that you would not have otherwise. > These tests run using the hibernate production stack (which use sessions > and entity objects for CRUD operations). We run into some issues when using > JOOQ for these integration tests. I'm currently just trying to use JOOQ for > a couple of entities to insert, select, delete, and list while the other > entities still interact using hibernate. > > *I have run into three types issues so far*: > 1. "user lacks privilege or object not found: n_p_c". n_p_c is a table > in our DB > 2. "integrity constraint violation: NOT NULL check constraint; table: > n_p_c column: c_s_date" > 3. "user lacks privilege or object not found: DUAL" > > *Problem 1* > - Problem 1. hides problem 2. When problem 1. is fixed, problem 2. > appears. I was able to fix problem 1. by adding the RenderNameStyle to > AS_IS in our integration test. NOTE: This render style is not in production > because we have not run into issues in production. > > org.jooq.exception.DataAccessException: SQL [insert into `n_p_c` > (`f_number`, `t_number`) values (?, ?)]; user lacks privilege or object not > found: n_p_c > at org.jooq_3.11.1.MYSQL.debug(Unknown Source) > at org.jooq.impl.Tools.translate(Tools.java:2380) > at > org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:802) > at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:364) > at > org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:127) > > > Integration testing settings: > new Settings().withRenderMapping(renderMapping) // renderMapping is just > new RenderMapping(); > .withExecuteLogging(true) > .withRenderFormatted(true) > .withFetchWarnings(true) > .withRenderSchema(false); > .withRenderNameStyle(RenderNameStyle.AS_IS); // Adding this > SPECIFICALLY for integration testing. > // This is not needed in production. I found solution to this > here: > https://groups.google.com/forum/#!searchin/jooq-user/user$20lacks$20privilege$20or$20object$20not$20found%7Csort:date/jooq-user/qDKCmMI9-14/9AXYs-wuHnUJ > This is one of the more frequent problems you might experience when using several RDBMS without actually needing them. MySQL and HSQLDB have different understandings of what is an identifier, especially with respect to casing. This can be worked around with jOOQ as you've seen. >From your message, I cannot tell why the `n_p_c` table has not been found. It could be due to: - Lacking qualification. You've removed the schema name from the generated SQL. Perhaps the table is in a different schema? - Case sensitivity. Perhaps the table is not in lower case, but in upper case? Depending on your MySQL settings and the operating system you're using, this may or may not be relevant, in MySQL - Wrong SQLDialect (as seen below). If this is also being executed on HSQLDB, then setting the SQLDialect to HSQLDB will generate "n_p_c" (with double quotes rather than backticks) > *Problem 2* > - Problem 2 fails to insert a row because my JOOQ query does not > explicitly set the c_s_date. c_s_date is a Timestamp that the database sets > on creation. > > This is the JOOQ generated field for c_s_date: > public final TableField<NPCRecordRecord, Timestamp> C_S_DATE = > createField("C_S_DATE", > org.jooq.impl.SQLDataType.TIMESTAMP.nullable(false).defaultValue(org.jooq.impl.DSL.field("CURRENT_TIMESTAMP", > org.jooq.impl.SQLDataType.TIMESTAMP)), this, ""); > > > org.jooq.exception.DataAccessException: SQL [insert into n_p_c > (f_number, t_number) values (?, ?)]; integrity constraint violation: NOT > NULL check constraint; table: n_p_c column: c_s_date > at org.jooq_3.11.1.MYSQL.debug(Unknown Source) > at org.jooq.impl.Tools.translate(Tools.java:2380) > at > org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:802) > at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:364) > at > org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:127) > Caused by: java.sql.SQLIntegrityConstraintViolationException: integrity > constraint violation: NOT NULL check constraint; table: n_p_c column: > c_s_date > at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) > at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) > at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source) > at org.hsqldb.jdbc.JDBCPreparedStatement.execute(Unknown Source) > at > com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) > at > com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) > at > org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:209) > at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:432) > at org.jooq.impl.AbstractDMLQuery.execute(AbstractDMLQuery.java:612) > at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:350) > ... 64 more > Caused by: org.hsqldb.HsqlException: integrity constraint violation: NOT > NULL check constraint; table: n_p_c column: c_s_date > at org.hsqldb.error.Error.error(Unknown Source) > at org.hsqldb.Table.enforceRowConstraints(Unknown Source) > at org.hsqldb.Table.insertSingleRow(Unknown Source) > at org.hsqldb.StatementDML.insertSingleRow(Unknown Source) > at org.hsqldb.StatementInsert.getResult(Unknown Source) > at org.hsqldb.StatementDMQL.execute(Unknown Source) > at org.hsqldb.Session.executeCompiledStatement(Unknown Source) > at org.hsqldb.Session.execute(Unknown Source) > ... 72 more > Your generated code seems to indicate that a default expression exists on the n_p_c table. But does it exist in your MySQL database as well? Or did you generate the jOOQ code from HSQLDB and the default expression exists only there? Notice also how you're using the MySQL dialect in jOOQ (see synthetic stack trace element at the top), but you're connecting to an HSQLDB database. This doesn't work of course. You have to use SQLDialect.HSQLDB when connecting to HSQLDB. > *Problem 3* > - Problem 3 stems from trying to query this "DUAL" table. I am not too > familiar with this. > > org.jooq.exception.DataAccessException: SQL [select 1 as one from dual > where exists (select n_p_c.id, n_p_c.f_number, n_p_c.t_number, > n_p_c.c_s_date from n_p_c where (n_p_c.t_number = ? and n_p_c.f_number = > ?))]; user lacks privilege or object not found: DUAL > at org.jooq_3.11.1.MYSQL.debug(Unknown Source) > at org.jooq.impl.Tools.translate(Tools.java:2380) > at > org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:802) > at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:364) > at > org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:393) > at > org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:380) > at org.jooq.impl.AbstractResultQuery.fetchOne(AbstractResultQuery.java:545) > at org.jooq.impl.SelectImpl.fetchOne(SelectImpl.java:2879) > at org.jooq.impl.DefaultDSLContext.fetchExists(DefaultDSLContext.java:4345) > Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or > object not found: DUAL > at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) > at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) > at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source) > at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source) > at > com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:311) > at > com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java) > at > org.jooq.impl.ProviderEnabledConnection.prepareStatement(ProviderEnabledConnection.java:109) > at > org.jooq.impl.SettingsEnabledConnection.prepareStatement(SettingsEnabledConnection.java:73) > at org.jooq.impl.AbstractResultQuery.prepare(AbstractResultQuery.java:239) > at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:322) > ... 103 more > Caused by: org.hsqldb.HsqlException: user lacks privilege or object not > found: DUAL > at org.hsqldb.error.Error.error(Unknown Source) > at org.hsqldb.error.Error.error(Unknown Source) > at org.hsqldb.SchemaManager.getTable(Unknown Source) > at org.hsqldb.ParserDQL.readTableName(Unknown Source) > at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source) > at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source) > at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source) > at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source) > at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source) > at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source) > at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source) > at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source) > at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source) > at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source) > at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source) > at org.hsqldb.ParserCommand.compilePart(Unknown Source) > at org.hsqldb.ParserCommand.compileStatement(Unknown Source) > at org.hsqldb.Session.compileStatement(Unknown Source) > at org.hsqldb.StatementManager.compile(Unknown Source) > at org.hsqldb.Session.execute(Unknown Source) > ... 111 more > Same as before. You're generating your SQL statement for MySQL, but you're executing it on HSQLDB I hope this helps, 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
