Hi Lukas, I've responded to your comments in line as well.
>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) > Setting the render name style to "AS_IS" fixed the issue of `n_p_c` table not being available for problem 1. 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? > The JOOQ code was generated from MySQL 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. Good catch on this one. I need two instances of my application running for the integration test. One of the application's dialect was not configured to HSQLDB. That's why I was seeing Problem 3 with "DUAL" table. I updated the dialect for the second instance and that problem is resolved. Thank you very much for your advice on using MySQL for the integration tests. I will look into that as a next step after discussing it w/ the bigger team. Thank you for your reply. On Monday, January 14, 2019 at 4:45:42 AM UTC-8, Lukas Eder wrote: > > 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] > <javascript:>> 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.
