SQLite database table without rowid throwing exception

[Daniel Clusin]

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", "fo...@example.com");

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', 'fo...@example.com')

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

[Daniel Clusin]

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

[Knut Wannheden]

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

[Lukas Eder]

On Wed, Jan 1, 2020 at 10:27 PM Daniel Clusin <dcl...@gmail.com> 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();

An additional, perhaps more convenient workaround is to use this synthetic syntax here: 

  context.insertInto(WORKER).set(worker).execute();  

Or even shorter

  context.executeInsert(worker);

See

https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/InsertSetStep.html#set(org.jooq.Record) 

https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/DSLContext.html#executeInsert(org.jooq.TableRecord)