Bug 77048

I sent a mail yesterday to webmaster to know whom we may contact.

From http://www.firebirdsql.org/file/documentation/drivers_documentation/java/2.2.3/release_notes.html#__RefHeading__3761_663895651 :
"
Issues addressed by this protocol:

    ResultSets are not closed when a statements is finished (eg fully read ResultSet or when creating a new Statement in autoCommit mode)

    DatabaseMetaData#getTablePrivileges(...) reports privileges granted to PUBLIC and to the current role (as reported by CURRENT_ROLE) as being granted to the user (after Jaybird 2.2.0 beta 1).
"

On this same url:
"
Please note, that each instance of FBEventManager will open a new socket connection to the Firebird server on the port specified by Firebird.

Similar to other JDBC extensions in Jaybird, the interfaces are released under the modified BSD license, the implementation of the code is released under LGPL license.

Default holdable result sets (closed ResultSet in auto-commit mode)

This connection property allows to create holdable result sets by default. This is needed as a workaround for the applications that do not follow JDBC specification in regard to the auto-commit mode.

Specifically, such applications open a result set and, while traversing it, execute other statements using the same connection. According to JDBC specification the result set has to be closed if another statement is executed using the same connection in auto-commit mode. Among others the OpenOffice/LibreOffice Base users have problems with the JDBC compatibility in Jaybird.

The property is called:

    defaultResultSetHoldable as connection property for JDBC URL or for java.sql.DriverManager class and no or empty value should be assigned to it; it has an alias defaultHoldable to simplify the typing;

    isc_dpb_result_set_holdable as a DPB member;

    FirebirdConnectionProperties interface methods isDefaultResultSetHoldable() and setDefaultResultSetHoldable(boolean)

Note, the price for using this feature is that each holdable result set will be fully cached in memory. The memory occupied by it will be released when the statement that produced the result set is either closed or re-executed.
"

Looking at Opengrok, "close" method is called in "disposing" methods, (see http://opengrok.libreoffice.org/search?q=disposing&project=core&defs=&refs=&path=connectivity%2Fsource%2Fdrivers%2Fjdbc&hist=)
But which methods call "disposing" methods (and so would close properly the ResultSet for example)?

I have sent a notice to Firebird-Java and Mark Rotteveel will join to this bug discussion 

https://groups.yahoo.com/neo/groups/Firebird-Java/conversations/topics/11041

I will go over the modifications that are in the Open Office/LibreOffice specific changes in Jaybird to see if they are still relevant and report back.

Unfortunately, my predecessor did not explicitly document the problems with OpenOffice (and probably LibreOffice) which lead to the introduction of the separate protocol.

Looking at the OpenOffice specific protocol in Jaybird, the issues were:

1. DatabaseMetaData.getSchemas() expects at least one schema to be reported (Firebird doesn't have schemas, so reports none)
2. Various metadata is queried with a specific schema instead of null or ""
3. Expectation that resultset remains open after commit
4. When rights are assigned through a role (either a connection specific role, or the PUBLIC role), tables are read-only as the GRANTEE column is expected to have the same value as the current user (http://tracker.firebirdsql.org/browse/JDBC-226, http://tracker.firebirdsql.org/browse/JDBC-252)

With Jaybird 2.2.4 in LibreOffice 4.2.2.1 I tried to reproduce these issues:

Ad 1. No longer occurs
Ad 2. No longer occurs (and a later change in Jaybird ignored schema in metadata queries)
Ad 3. Either this no longer occurs or I can't find the specific conditions to trigger it (I will dig deeper); another possibility is that this was actually caused by a bug in Jaybird: http://tracker.firebirdsql.org/browse/JDBC-304 and/or http://tracker.firebirdsql.org/browse/JDBC-305 seem likely candidates
Ad 4. This problem still exists, but can be worked around by disabling the privilege check in the advanced options (which might have some negative user experience effects like error messages when the update is not allowed) or using the jdbc:firebirdsql:oo: protocol.

For most metadata methods JDBC implies (a variant of) the INFORMATION_SCHEMA or DEFINITION_SCHEMA defined in the SQL standard. Looking at the SQL:2011 Schemata book (section 5.62 TABLE_PRIVILEGES view and 6.52 TABLE_PRIVILEGES base table) the default implementation in Jaybird uses the right values for the GRANTEE column (namely the username for user-assigned rights and the rolename for role assigned rights), but Jaybird reports all privileges of all users and roles (as in the 6.52 table definition) instead of only those available to the current user under his currently enabled roles (as in the 5.62 view definition). The wording in the JDBC API documentation doesn't make clear what is actually expected.

Either LibreOffice would need to also check the privileges for GRANTEE values PUBLIC and the current role but there is no standard mechanism in JDBC to query what the current role is, and/or Jaybird should only report the privileges of the current user (both user- and role-assigned); but then LibreOffice would have to assume that all reported privileges are for the current user which isn't true for existing Jaybird versions and might not hold with other JDBC driver implementations either.

So to summarize: the issues that lead to the introduction to the separate protocol either no longer exist (1+2, at least not in LibreOffice), are not (yet) reproducible or might have been a bug in Jaybird itself (3), or are simply a problem that given the definition in JDBC are not really solvable.

I have updated the crass wording in the jaybirdwiki that the expectations by LibreOffice do not comply with the JDBC specification to "the interpretation of the JDBC standard by Jaybird differs from the interpretation by OpenOffice / LibreOffice" and as far as I can tell that is only really relevant for item 4.

I will take a closer look at issue 3 to see if I can still reproduce that, but at this point I'd say that there is nothing that needs to be fixed in LibreOffice.

Perhaps we may put this one to WFM according to last Mark's comment and reopen it if needed?
Lionel/Mark: what do you think?

(In reply to comment #7)
> Perhaps we may put this one to WFM according to last Mark's comment and
> reopen it if needed?

OK

Fine with me as well.