SREE transaction isolation
From PTAGISWiki
When using the SREE report designer on a linux desktop, the following error was generated when trying to run the Site Summary by Date report:
ca.edbc.util.EdbcEx: No MST is currently in progress, cannot declare another cursor.
Googling for that error message brings up this link: http://www.cariboulake.com/clsdown/doc/jsql/4.x/jsqlfaq.html Which says:
This JSQL/Ingres error message means that autocommit mode for your Ingres connection is on and you are trying to use two ResultSets at once. Since JDBC ResultSets are implemented as Ingres cursors, and since Ingres only allows ONE open cursor while autocommit is on, an error occurs. To have multiple ResultSets open on the same JSQL/Ingres connection, all you have to do is call the following method after you connect: java.sql.Connection.setAutoCommit( false ); The Sun JDBC specification requires us to set autocommit to on after establishing a connection to the database. Many customers have commented that they CAN have multiple open ResultSets while autocommit is on. This is true only if the data from the SQL select statement can be transmitted to the client in a single operation. In this case, the ResultSet is actually "exhausted" and the implementing cursor is closed. Therefore, even though ResultSet is not completely processed on the client, the server is done with it and thus closes the associated cursor. If the next statement is a SQL select statement, everything functions correctly as the cursor for the first, but still being processed ResultSet, is closed. Remember that with autocommit turned off, you must commit your work at the appropriate points. When to do so depends upon your application. To set autocommit mode back to on, call the setAutoCommit method with the mode parameter set to true. However, you must commit any in-progress transaction before doing so.
Without actually making changes to the SREE code, there still may be a way to control the autocommit setting of the database driver. For every data source in SREE there is transaction isolation setting in datasource.xml. All the current datasources in production are set to "default", but the following options are available:
- READ_UNCOMMITTED (least locks)
- READ_COMMITTED
- REPEATABLE_READ
- SERIALIZABLE (most locks)
Here is a quick description of the levels of transaction isolation.
Changing the transaction isolation for the production datasource to READ_UNCOMMITTED does not change the output: the MST error still prevents the report from running its subqueries.
