Report true for ResultSetMetaData.isAutoIncrement for identity columns

[mrotteveel]

Implement returning true from ResultSetMetaData.isAutoIncrement for identity columns. This will require querying the metadata tables, as this information is not available through the statement info API.

Split off from #792

[prrvchr]

Yes I have this need to run Jaybird under LibreOffice Base. When it is in table edition mode it performs an SQL query on the table: SELECT * FROM "Table1" WHERE 0 = 1.
Normally such a query allows access to the table columns from ResultSetMetaData and Base can determines whether a column is autoincrement.

[mrotteveel]

For LibreOffice, I would recommend using its built-in Firebird support instead of continuing to use Jaybird for that.

[prrvchr]

I think it would be more appropriate to rejoice in this port. It has already made it possible to identify numerous malfunctions in a short time. It's my opinion.

[mrotteveel]

Backported to Jaybird 5.0.5

[prrvchr]

Thanks Mark, I will integrate it into the next version of jdbcDriverOOo.

[mrotteveel]

@prrvchr If you want, you can test it with the 5.0.5.java8-SNAPSHOT or 5.0.5.java11-SNAPSHOT from the Sonatype OSS snapshot repository (https://oss.sonatype.org/content/repositories/snapshots).

[prrvchr]

I just tried 5.0.5. This works perfectly, the auto increment columns are clearly seen by Base. I thank you.

[prrvchr]

On the other hand, I get a message when I first open it telling me that the ResultSet is closed, then if I insist it starts working... Weird...

[mrotteveel]

Do you have a stacktrace for that?

[mrotteveel]

You might be confronted with the problem that LibreOffice expects (or expected) holdable result sets by default. The OpenOffice/LibreOffice specific protocol (deprecated in Jaybird 5, removed in Jaybird 6) would explicitly call setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT) on the connection to address this. You may need to do the same, or set connection property defaultResultSetHoldable to true (see also Default holdable result sets in the Jaybird manual).

[prrvchr]

Hi Mark,

I just tried again with the defaultResultSetHoldable option in the connection URL and in this case I got another error message during the connection:

SQL Status: 01000
Error code: 337248265

Holdable result set must be scrollable, upgrading to TYPE_SCROLL_INSENSITIVE [SQLState:01000, ISC error code:337248265]

---What surprises me is that I had no problems of this type with version 5.0.4. Has anything changed?---

I just retested in 5.0.4 and I actually have the same problem, I don't know where my regression comes from...

[prrvchr]

Ok the problem is solved it is imperative to create the PreparedStatement with the option java.sql.ResultSet.HOLD_CURSORS_OVER_COMMIT otherwise Base cannot open the ResultSet.

I don't understand why I could get Jaybird to work in Base without this option??? Sometimes you shouldn't try to understand...

[mrotteveel]

I just tried again with the defaultResultSetHoldable option in the connection URL and in this case I got another error message during the connection:

SQL Status: 01000
Error code: 337248265

Holdable result set must be scrollable, upgrading to TYPE_SCROLL_INSENSITIVE [SQLState:01000, ISC error code:337248265]

That is a warning, which - per the JDBC specification - is added to the connection when the result set type is upgraded. It is not thrown as an exception. I'm not sure why LibreOffice would report that as an error.

Did you try this with 6.0.0-SNAPSHOT or with 5.0.5?

[mrotteveel]

Ok the problem is solved it is imperative to create the PreparedStatement with the option java.sql.ResultSet.HOLD_CURSORS_OVER_COMMIT otherwise Base cannot open the ResultSet.

I don't understand why I could get Jaybird to work in Base without this option??? Sometimes you shouldn't try to understand...

I guess you initially used prepareStatement("query..."), and then switched to prepareStatement("query...", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT). The prepareStatement(String) method will unconditionally use ResultSet.TYPE_FORWARD_ONLY, so it will trigger that warning when defaultResultSetHoldable is set, but then again, LibreOffice should not report a warning as an error.

[prrvchr]

To be exact, there is a difference in the implementation of JDBC and UNO SDBC APIs at the PreparedStatement level.

With UNO, the ResultSet type and concurrency are a property of the PreparedStatement, which requires me to create the JDBC PreparedStatement only when executed for the first time at the UNO level (since only then do I know if the type and concurrency options have been modified or not)
By default, the ResultSet options are:

• java.sql.ResultSet.TYPE_FORWARD_ONLY
• java.sql.ResultSet.CONCUR_READ_ONLY

When executing a PreparedStatement under the UNO API there are two possibilities:

• If one of the two options has been modified then I create the PreparedStatement during execution with both options: type and concurrency (since at least one of the two has been modified)
• If neither option has been modified, I create the PreparedStatement with the option: java.sql.Statement.RETURN_GENERATED_KEYS

This is what has worked so far, but I think this is what is causing me the problem.

I have not tried with version 6.0.0-SNAPSHOT. Where can I find it?

[mrotteveel]

I have not tried with version 6.0.0-SNAPSHOT. Where can I find it?

Same place as the 5.0.5-SNAPSHOT, the Sonatype OSS snapshot Maven repository: https://oss.sonatype.org/content/repositories/snapshots/ (Jaybird can be found in https://oss.sonatype.org/content/repositories/snapshots/org/firebirdsql/jdbc/jaybird/ )

[prrvchr]

I will try with version 6.

I seem to have read that the option java.sql.ResultSet.HOLD_CURSORS_OVER_COMMIT was the default for JDBC. Why do I need to change this option with Jaybird?

[mrotteveel]

The JDBC 4.3 specification (section 15.1.3.1) says:

The default holdability of ResultSet objects is implementation defined.

This has been the case since their introduction in JDBC 3.0 (Java 1.4).

It also says (since JDBC 4.0/Java 6):

It is the application’s responsibility, not the JDBC driver implementation, to validate the holdability of the returned ResultSet.

The reason it is not the default in Jaybird is because holdable result sets either require Jaybird to fiddle with transaction lifetimes using commit/rollback retain and terminate the actual transaction later (which is complex and error prone), or requires materializing the entire result set in memory by default instead of now only in the exceptional cases (which is inefficient, so best avoided if not needed), or materialize the remainder of the result set at transaction end.

[prrvchr]

Well everything is back to normal, I don't really know where it came from. I suspect too much change to release this new version.

The new version 5.0.5 is available in the latest version of jdbcDriverOOo 1.3.1.

It allows the administration of users, roles and privileges on tables and views.

Thank you for your help.

[mrotteveel]

To be clear, 5.0.5 is not yet released, what I linked was a snapshot for testing purposes. You shouldn't depend on it for released versions IMHO.

[prrvchr]

You're right, but it's done... I will update it as soon as 5.0.5 is available...