connection management and SQLite

5 views
Skip to first unread message

Manuel Rossetti

unread,
Jul 26, 2021, 3:23:08 PMJul 26
to jOOQ User Group
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.


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

unread,
Jul 26, 2021, 4:12:17 PMJul 26
to jooq...@googlegroups.com
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

unread,
Jul 26, 2021, 4:46:32 PMJul 26
to jOOQ User Group
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

unread,
Jul 26, 2021, 6:08:16 PMJul 26
to jooq...@googlegroups.com
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?

Lukas Eder

unread,
Jul 27, 2021, 1:39:39 AMJul 27
to jooq...@googlegroups.com


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
 
Reply all
Reply to author
Forward
0 new messages