BIRT Report Designer empty DataSet scripting

Question

I am using BIRT Report Designer 4.4.0 and I kept getting the error message

org.eclipse.birt.report.engine.api.EngineException: Cannot fetch the next data row. org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot move down to next row in the result set. SQL error #1:The result set is closed ; org.firebirdsql.jdbc.FBSQLException: The result set is closed

when BIRT tried to fetch a row from a dataset which I knew was empty. I found that this is a common issue with BIRT and tried to prevent the empty dataset with a beforeOpen script on the dataset is in which I probably did wrong because it didn't work. I created a vars["item"] and wanted to count how often OnFetch was called.

BeforeOpen:

vars["item"] = 0;

OnFetch:

vars["item"]++;

BeforeClose:

if (vars["item"] == 0)
{
  row[0] = "0";
}

which doesn't work because there is no row[0] I guess.

When I edited the queryText of the dataset to just

SELECT "0" from kontrolle

which should definitely produce a dataset, I still got the same "Cannot fetch the next data row" error!

The design looks roughly like this:

<table>
   <table>
      <table> here is the dataset in question
      </table>
   </table>
</table>

The second table is bound to the first table by only one parameter, however, the third table has two parameter bindings to the second one.

Hope the explanation was good enough and someone can provide a solution.

Answer 1

As commented before, add defaultResultSetHoldable=true to the connection properties (eg in the JDBC url). This instructs Jaybird to create connections with holdable result sets by default (the equivalent to calling connection.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT) in code).

I don't really know BIRT, so this is nothing more than an educated guess to the cause:

1. It could expect holdable result sets without explicitly asking or checking for them, and commits after opening the result sets (in Jaybird by default result sets are closed on a commit)
2. BIRT could use autoCommit=true (the default), but doesn't expect result sets to be closed on execution of another statement on the same connection (this causes a close of resources like result sets, and a commit as specified by the JDBC standard; in some aspects this is a specialization of point 1).