connection management and SQLite

[Manuel Rossetti]

I have a class variable that holds my DSL context.

myDSLContext = DSL.using(dataSource, dialect);

The data source is an instance of SQLiteDataSource and the dialect is SQLDialect.SQLITE.

It is my understanding that the default JOOQ connection provider should use the

data source to acquire and then release the connection when executing within the context.

In various methods in my class, I use myDSLContext when building and executing the SQL:

Condition condition = myRowId.between(minRowNum, maxRowNum);

Result<Record> records = myDSLContext.select(myFields).from(myDataTable).where(condition).fetch();

After the program exits, there are shm and wal temporary files left over.

See: https://www.sqlite.org/tempfiles.html

These files should be automatically cleaned up if the database closes correctly:  "if the last connection 

does not shutdown cleanly, the WAL file will remain in the filesystem and will be 

automatically cleaned up the next time the database is opened".

Since all my interaction with the DB is through the same context, what could be causing

the database (connection) to not shutdown cleanly?

By using a data source, I am able to not worry about connection management for this simple 1 user, 1 file database.  In fact, it is my understanding that JOOQ uses automatic closing for me after the query is executed. This is one of the main features.  

Am I missing understanding this feature?

If not, are there issues with SQLite connection management that I am missing?

[Manuel Rossetti]

This may be caused by how SQLite functions in wal journal mode.

config.setJournalMode(SQLiteConfig.JournalMode.WAL)

If I do not turn on WAL journaling, the files are not created (because they are not used).  However, I don't know why turning on WAL journaling would cause them to persist. It may simply be how SQLite works and may have nothing to do with JOOQ.

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/vHxvGapFxfg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/39628a3d-116c-454b-a56e-6589cacfaa8dn%40googlegroups.com.

[Lukas Eder]

Hi Manuel,

That doesn't sound familiar - in any case, itt doesn't sound like something jOOQ does. Maybe you'll be more lucky asking this question on Stack Overflow? Also, check if you can reproduce the issue with JDBC alone, without jOOQ...

Thanks,

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 jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAMbxvHnXU-pD%3DuBcYUW%3DW3k62amnFFEQAsjxKQQP6m4da6enqw%40mail.gmail.com.

[Manuel Rossetti]

Checking via JDBC alone is a great suggestion. Thanks

I just want to confirm my understanding of the default JOOQ operation when given a data source.  JOOQ does automatically close the connection in that case, right?

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO5A0P6JYG%2B%3DB_5SnzzYZp3HuMP5kg1vAm8LY-V2upyimg%40mail.gmail.com.

[Lukas Eder]

Am Dienstag, 27. Juli 2021 schrieb Manuel Rossetti <mdrfo...@gmail.com>:

I just want to confirm my understanding of the default JOOQ operation when given a data source.  JOOQ does automatically close the connection in that case, right?

Yes