JDBC driver does not work with SQLite database

[cjwomack]

Description: The JDBC driver does not work with SQLite database due to the fact a SQLite file has only 1 database in each file ever and is unnamed and it is referred to as main when connected (As far as I can tell) See stackoverflow re this

"Error Message: ERROR Fatal error while converting the database (Error while exporting structure: dbname cannot be blank)" ...

This fact that this database is unnamed can be seen in the JDBC SQLite driver in the getCatalog() function (https://github.com/xerial/sqlite-jdbc/blob/ada2b147dc858ef557a995e2f4ef25f7c65fc6a7/src/main/java/org/sqlite/jdbc3/JDBC3Connection.java#L27)

The relevant line in the code referring to getCatalog() is getDatabaseName() function

Steps required to reproduce the bug:

1. Get a SQLite DB (such as Chinook DB from www.sqlitetutorial.net) and JDBC driver for SQLite
2. For Windows use: java "-Dfile.encoding=UTF-8" -cp "C:\path\to\dbptk-app-x.y.z.jar;C:\path\to\sqlite-jdbc-3.36.0.3.jar" com.databasepreservation.Main migrate --import=jdbc --import-driver=org.sqlite.JDBC --import-connection="jdbc:sqlite:C:\path\to\database" -e siard-2 -ef C:\path\to\output.siard

Adjust for other operating systems accordingly.

So it looks like the JDBC driver needs to be modified or a specific SQLite driver based off the JDBC driver.

Attach the dbptk-app.log.txt file below. dbptk-app.log.txt

[luis100]

Could you try this? --import-connection="jdbc:sqlite:[path]"

[cjwomack]

Oops (that's from the log) Sorry its evening here.

[luis100]

Sure, but it seems you have add a ":thin:" that is not needed. Your connection string is "jdbc:sqlite:thin:[path]" but it should be "jdbc:sqlite:[path]".

[cjwomack]

My computer crashed last night with the correct working info. So I did a cut and paste from the README.md and from specific sqlite JDBC stuff. I should have created a new file with the correct details.

[cjwomack]

That was before I posted this issue

[cjwomack]

I'll run it again in a nice new file

dbptk-app.log.txt

Thanks for getting back so quickly...

Moral of the story. I should have created this earlier. Sorry.

[luis100]

Hum, yes, I see that the SQLite JDBC client provides and empty database name, which is not permitted in SIARD.

2022-05-24 21:00:47,256 [main] ERROR (c.d.Main) Fatal error while converting the database (Error while exporting structure: dbname cannot be blank)
com.databasepreservation.model.exception.ModuleException: Error while exporting structure: dbname cannot be blank
    at com.databasepreservation.modules.siard.out.metadata.SIARD21MetadataExportStrategy.jaxbSiardArchive(SIARD21MetadataExportStrategy.java:266)
    at com.databasepreservation.modules.siard.out.metadata.SIARD21MetadataExportStrategy.writeMetadataXML(SIARD21MetadataExportStrategy.java:186)
    at com.databasepreservation.modules.siard.out.output.SIARDExportDefault.finishDatabase(SIARDExportDefault.java:175)
    at com.databasepreservation.model.modules.filters.IdentityFilter.finishDatabase(IdentityFilter.java:103)
    at com.databasepreservation.model.modules.filters.ObservableFilter.finishDatabase(ObservableFilter.java:151)
    at com.databasepreservation.modules.jdbc.in.JDBCImportModule.migrateDatabaseTo(JDBCImportModule.java:2143)
    at com.databasepreservation.DatabaseMigration.migrate(DatabaseMigration.java:123)
    at com.databasepreservation.Main.runMigration(Main.java:294)
    at com.databasepreservation.Main.internalMain(Main.java:125)
    at com.databasepreservation.Main.main(Main.java:81)

I guess that for this specific case, we actually need and specialized implementation of a SQLlite import module so it gets the database name from the file name it loads.

PS: No problem, if you'd like to redact your local paths and logs please feel free. We got the jist of the issue in the stacktrace above.

[cjwomack]

So maybe need to create an arbitrary name for the sqlite jdbc driver?

[luis100]

I don't see any work-around this issue without development. Or the generic JDBC module creates a default database name or allows a parameter to override the database name, or a new module is created just for SQLLite to fix this situation. Anyway, code needs to be developed.

[cjwomack]

I agree. So the class name of the sqlite driver is org.sqlite.JDBC and I assume that is in this.driverClassName?? So could an if statement be used in getDatabaseName()???

protected String getDatabaseName() throws SQLException, ModuleException {

return getConnection().getCatalog();

}

[luis100]

I am not an expert in SQLLite, but it seems it does not have the concept of database name and therefore the JDBC client does not provide one. In my view, this information should be replaced by the file name of the SQLLite database, in your example "chinook". But this is a very specific situation for SQLLite, and therefore it should be part of a specialized module for this database, which could also make it easier to load the database from a selected file and skip the requirement of loading the driver jar.

[MartinR4838]

Hello, problem seems not solved in v. 2.10.3 but with sqlite-jdbc-3.39.3.0.jar dbptk-app now reports "ERROR Fatal error while converting the database (Error while Marshalling JAXB)" and not the one with blank name. SIARD file is produced but only with /header folder is inside and metadata.xml file is empty. dbptk_2_10_3_jdbc_sqlite.zip