Connection.getMetaData().getTables() statement issue

[amolna...@gmail.com]

Hi Mark!

After calling conn.getMetaData().getTables(), close the ResultSet, and commit on connection, the SQL statement executed internally by Jaybird is still open. (mon$statements still contains the statement).

(Running the internal statement by IBExpert and commiting, the statement disappears from  mon$statements)

Tested with Jaybird 5.0.9.java8.

public static void main(
String[] args)
throws Exception {

try(BasicDataSource ds = new BasicDataSource()) {
ds.setDriverClassName(org.firebirdsql.jdbc.FBDriver.class.getName());
ds.setUrl(
"jdbc:firebirdsql:172.16.173.71/3053:C:\\Program Files\\Firebird\\Firebird_3_0\\examples\\empbuild\\employee.fdb?charSet=UTF-8&isc_dpb_sql_dialect=1&authPlugins=Legacy_Auth");
ds.setUsername("SYSDBA");
ds.setPassword("masterkey");
ds.setMaxIdle(-1);
ds.setDefaultAutoCommit(false);
ds.setAutoCommitOnReturn(false);
ds.setRollbackOnReturn(true);
ds.setDefaultReadOnly(true);
ds.setDefaultTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
try(Connection conn = ds.getConnection()) {
try(ResultSet rs = conn.getMetaData().getTables(null, null, null, null)) {
while (rs.next()) {
System.out.println(rs.getString("TABLE_NAME"));
}
System.out.println("");
}
conn.commit();
}
try(Connection conn = ds.getConnection()) {
try(ResultSet rs =
conn.createStatement().executeQuery("SELECT ma.mon$remote_process, ms.mon$sql_text, ms.mon$transaction_id\n" +
"FROM mon$statements ms\n" +
"    JOIN mon$attachments ma ON ma.mon$attachment_id = ms.mon$attachment_id\n" +
"WHERE ms.mon$sql_text LIKE '%trim(trailing from RDB$RELATION_NAME)%' AND " +
"    ms.mon$sql_text NOT LIKE '%mon$statements%'")) {
//this should be empty
while (rs.next()) {
System.out.println("------------------------------");
System.out.println(rs.getString(1));
System.out.println(rs.getString(2));
System.out.println(rs.getString(3));
}
}
}
}

[Mark Rotteveel]

On 09/09/2025 16:43, amolna...@gmail.com wrote:
> After calling conn.getMetaData().getTables(), close the ResultSet, and
> commit on connection, the SQL statement executed internally by Jaybird
> is still open. (mon$statements still contains the statement).
> (Running the internal statement by IBExpert and commiting, the statement
> disappears from mon$statements)
> Tested with Jaybird 5.0.9.java8.

That is intentional and expected behavior: the database metadata
implementation in Jaybird maintains a LRU cache of up to 12 prepared
statement so they can be reused. The statement will only be closed once
it is evicted from the cache.

If you really want to get rid of the cache, you can call close()
(defined in FirebirdDatabaseMetaData, so you'd need to unwrap or cast
first).

Mark
--
Mark Rotteveel