H2 in LibreOffice Base

[prrvchr]

Hi all,

I'm trying to write a JDBC driver for LibreOffice Base and I'm having trouble getting autoincrement keys working with H2  in Base.

I managed to make it work with Derby and HsqlDB but not with H2...

Maybe someone has some ideas for me?

Thank you.

[Evgenij Ryazanov]

Hello!

Recent versions of H2 fully support identity columns from the SQL Standard and their retrieving functionality from the JDBC specification.

What exactly doesn't work for you? How these columns are defined? How rows are inserted? How exactly LibreOffice tries to read generated values with your driver?

For example, an application that uses own driver of H2 should execute commands like these:

Statement stat = connection.createStatement();
stat.execute("CREATE TABLE TEST(ID BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, V INTEGER)");
PreparedStatement prep = connection.prepareStatement("INSERT INTO TEST(V) VALUES (?)",
        Statement.RETURN_GENERATED_KEYS);
prep.setInt(1, 10);
prep.executeUpdate();
try (ResultSet rs = prep.getGeneratedKeys()) {
    rs.next();
    System.out.println(rs.getLong("ID"));
}

Actually you can run into some unrelated issue of LibreOffice that most likely is not going to be fixed, because they don't really want to spend time on additional database systems.

For example, there is a problem with tables from JDBC metadata:
https://bugs.documentfoundation.org/show_bug.cgi?id=146673

I also don't understand why you need to create an alternative driver, but you may have own reasons.

[prrvchr]

Hi Evgenij Ryazanov,

Thank you for taking the time to respond.

LibreOffice/OpenOffice Base seems very limited in its compatibility with JDBC 3.0 and unfortunately does not know JDBC 4.X.

Here is for example a command that Base executes when creating a table with an autoincrement key:

CREATE TABLE "PUBLIC"."PUBLIC"."Table1" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, "Name" NVARCHAR(100), PRIMARY KEY  ("ID"))

It can be noted that the declaration of the keys can only be done by the instruction: PRIMARY KEY  ("ID", ...)

I don't think Base is able to retrieve the keys generated by the command: rs = prep.getGeneratedKeys(), but uses an AutoRetrievingStatement property with the value: CALL IDENTITY()

I know that LibreOffice wants to disengage from Java and won't make any effort to improve the integrated JDBC driver, that's why I had to write a new driver for my LibreOffice extensions I need a cross-platform database (java) supporting the Temporal System-Versioned Tables (HsqlDB 2.5x or 2.6x)

This driver gives me complete satisfaction at the level of the UNO API (use of the database by the smtpMailerOOo extension written in Python for example) but it seems important to me to make the effort to make Base capable of supporting these databases.

I recently integrated into the driver the archives: hsqldb.jar, h2.jar, derby.jar and smallsql.jar in order to make debugging in Base easier and think that it is welcome to have access to all these databases in LibreOffice.

I have the possibility in my driver to modify the DatabaseMetaData and ResulsetMetaData in order to make Base work correctly, the problem is not to correct the malfunctions but to find them...

For exemple, to have H2 functional in Base I had to translate the name of the table types ("BASE TABLE" into "TABLE") in the DatabaseMetaData.

In fact, I'm interested in any information that can help me.