The following error message "Streaming result set com.mysql.jdbc.RowDataDynamic@XXXXXX is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries" comes from MySQL.
The reason for this lies in the streaming mode of MySQL. Streaming mode is the fastest way to get data from MySQL, and Data Virtuality uses this mode to transfer the data.
However, streaming mode has one limitation: When a resultset is open in streaming mode on a MySQL connection, you cannot use that same database connection to run other queries. This is exactly what the error message above tells. The solution to the problem would be to change the MySQL connection settings to use CursorFetch mode instead of Streaming mode.
To do this, you need to edit the MySQL connection in Data Virtuality to do the following
Press Edit on your MySQL datasource in DV Studio and:
- Set the translator parameter UseStreamingResults=false
- Add the following parameters to JDBC parameters: useCursorFetch=true&defaultFetchSize=10000
- Press OK
CursorFetch is the second quickest read method for MySQL. The difference is that with Streaming mode the server tries to start getting the data to the consumer immediately, on CursorFetch it fills the buffer first and then serves the data from the buffer. On very large data sets there will be some initial delay until MySQL starts getting you the data in CursorFetch mode, but the difference is usually quite negligible for any practical purpose.
