Hi Dan, In your original message you state that you use DSLContext#newRecord(Table, Object) to create the record. Since this method returns a Record object (and not an UpdatableRecord or TableRecord) which doesn't declare an insert() method, I am assuming you simplified your code to explain the problem.
You are quite right in that jOOQ currently doesn't explicitly support SQLite's CREATE TABLE ... WITHOUT ROWID clause and will end up executing a SELECT after the INSERT, which expects the _rowid_ column. This is due to the setting Setting.returnIdentityOnUpdatableRecord which defaults to true and causes jOOQ to fetch the record's identity after calling store(), insert(), or update(). So in your case you could simply change this setting to false. But please be aware that this will affect all queries using the DSLContext where you have configured this setting. Hope this helps, Knut On Wednesday, January 1, 2020 at 10:27:16 PM UTC+1, Daniel Clusin wrote: > > 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/a0614733-069c-4151-a4fe-ca8ed8d5aa4e%40googlegroups.com.
