SQLite problems - need tSQLiteConnection and tSQLiteCommit components.

One Star

SQLite problems - need tSQLiteConnection and tSQLiteCommit components.

There's a problem using SQLite components in certain combinations which I think are due to the inherent "single-user update" nature of SQLite.

This applies to Java projects under TOS 2.3.0 (Windows).

I'm having problems with both the SQlite specific components (which uses V037 "Nested" pure Java driver) and with the JDBC Generic component (using the V040 JNI Windows driver). All drivers supplied by http://www.zentus.com/sqlitejdbc/

I get different errors messages, both the same problem.

Usually ..

java.sql.SQLException: SQL logic error or missing database
at org.sqlite.DB.throwex(DB.java:252)
at org.sqlite.DB.exec(DB.java:31)
at org.sqlite.Conn.commit(Conn.java:146)

on pure Java "nested" components but also sometimes

database lockedException in component tSQLiteRow_1
java.sql.SQLException: SQL logic error or missing database
at org.sqlite.DB.throwex(DB.java:252)
at org.sqlite.DB.exec(DB.java:31)
at org.sqlite.Conn.commit(Conn.java:146)

This is also the message I'm getting on the Windows JNI version when using JDBC generic.

What appears to be happening is:

There are three nested Talend SQlite components, which when saved, and the code generated,will result in three nested loops.

Each component generates its own connection to the same SQLite database (Not ideal for a single user database, Talend needs to offer a "shared" connection for SQLite). The outer loop iterates on one table, for each row control is sent to the second inner loop which itself loops on another table and sends control to a third inner loop, which performs a database update.

This third inner loop's connection is set to .autoCommit=false and for each iteration executes a SQL statement on another table (remember all 3 loops operate on same database). All works dandy until this inner loop's .close (implicit commit) statement which is issued AFTER the 2ND loop's connection is closed but BEFORE the outer loop's connection is closed. And there I think lies the problem.

I'm not sure where the "fault" lies but I'm guessing this scenario would not have been high on the list of use cases for the developer of SQLite, so I think the "solution" within Talend lies with developing a "shared" SQLite (or Generic JDBC) connection component (and associated tSQLiteCommit component).

To get around this problem in the meantime, create a sub job fo the outer loop and pass the data back using tBufferOutput, thus ensuring that the outer connection is closed (might not be ideal for very large datasets, in that case use a temp file/database).


Tom
Employee

Re: SQLite problems - need tSQLiteConnection and tSQLiteCommit components.

Excellent investigation and I shared your point of view. I strongly encourage you to create a feature request in the bugtracker so that we can share an SQLite connection.