I've found a workaround by explicitly executing an insert state a-la: context.insertInto(WORKER).columns(WORKER.ID, WORKER.EMAIL).values(worker.getId(), worker.getEmail()).execute();
Doing some additional digging it looks like the SQLite implementation always assumes that _rowid_ table is available after insert queries which isn't always the case, as my example above shows: https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/impl/AbstractDMLQuery.java#L897 https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/impl/DefaultDSLContext.java#L3797 On Wednesday, January 1, 2020 at 12:56:23 PM UTC-8, Daniel Clusin wrote: > > Greetings, > > I have a database table that looks like: > CREATE TABLE worker > ( > id TEXT NOT NULL PRIMARY KEY, > email TEXT NOT NULL > ) WITHOUT ROWID; > > I execute some insertion code using a new record like: > Worker worker = new WorkerImpl("blast", "[email protected]"); > var record = context.newRecord(WorkerTable.WORKER, worker); > record.insert(); > > From the debug output the insertion seems to execute okay: > 12:45:09.008 [Test worker] DEBUG org.jooq.tools.LoggerListener - Executing > query : insert into worker (id, email) values (?, ?) > 12:45:09.009 [Test worker] DEBUG org.jooq.tools.LoggerListener - -> with > bind values : insert into worker (id, email) values ('blast', ' > [email protected]') > 12:45:09.021 [Test worker] DEBUG org.jooq.tools.LoggerListener - Affected > row(s) : 1 > > However, after the insert, it attempts to fetch the last record by rowid, > which I have disabled on the table: > 12:45:09.162 [Test worker] DEBUG org.jooq.tools.LoggerListener - Executing > query : select last_insert_rowid() > 12:45:09.172 [Test worker] DEBUG org.jooq.tools.LoggerListener - Fetched > result : +-------------------+ > 12:45:09.172 [Test worker] DEBUG org.jooq.tools.LoggerListener - > : |last_insert_rowid()| > 12:45:09.172 [Test worker] DEBUG org.jooq.tools.LoggerListener - > : +-------------------+ > 12:45:09.172 [Test worker] DEBUG org.jooq.tools.LoggerListener - > : | 0| > 12:45:09.172 [Test worker] DEBUG org.jooq.tools.LoggerListener - > : +-------------------+ > 12:45:09.172 [Test worker] DEBUG org.jooq.tools.LoggerListener - Fetched > row(s) : 1 > 12:45:09.176 [Test worker] DEBUG org.jooq.tools.LoggerListener - Executing > query : select worker.id from worker where _rowid_ = ? > 12:45:09.176 [Test worker] DEBUG org.jooq.tools.LoggerListener - -> with > bind values : select worker.id from worker where _rowid_ = 0 > 12:45:09.179 [Test worker] DEBUG org.jooq.tools.LoggerListener - > Exception > org.jooq.exception.DataAccessException: SQL [select worker.id from worker > where _rowid_ = ?]; [SQLITE_ERROR] SQL error or missing database (no such > column: _rowid_) > at org.jooq_3.12.3.SQLITE.debug(Unknown Source) > at org.jooq.impl.Tools.translate(Tools.java:2717) > at > org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:755) > at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:382) > at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:353) > at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2693) > at org.jooq.impl.AbstractDMLQuery.execute(AbstractDMLQuery.java:899) > at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:368) > at org.jooq.impl.TableRecordImpl.storeInsert0(TableRecordImpl.java:206) > at org.jooq.impl.TableRecordImpl$1.operate(TableRecordImpl.java:177) > at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:130) > at org.jooq.impl.TableRecordImpl.storeInsert(TableRecordImpl.java:173) > at org.jooq.impl.TableRecordImpl.insert(TableRecordImpl.java:161) > at org.jooq.impl.TableRecordImpl.insert(TableRecordImpl.java:156) > (cropped) > > My dependency versions are: > implementation group: 'org.jooq', name: 'jooq', version: '3.12.3' > implementation group: 'org.xerial', name: 'sqlite-jdbc', version: '3.28.0' > > Is there anyway to disable this query by rowid? I haven't been able to > find much discussion about the rowid concerning SQLite specifically. > > Thank you in advance, > Dan > -- 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]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/9917701e-355a-4172-85ed-6c856cc36ad3%40googlegroups.com.
