Error: Multiple Connections open ... How to solve?

[diddy]

Hi,
The last 2 weeks I have spend with fixing problems in a more or less complex transformation and I really have fix this now. I have a lot of steps in one transformation and connect to two databases. When I run the transformation on its own, it works in most cases, but when I run it dynamically (I use the same transformation over and over again by feeding in variables - similar to the 'process all tables' example), then I get following error:

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.

So, my question is:
- do I have to split this transformation into more smaller transformations so that I can overcome this problem? (I cannot use 'unique conenctions' in one transformation because I connect to two databases).
What am I doing wrong here?

Here are all my steps:
The main transaction

Process one table

Process all tables


I would really appreciate if somebody could tell me how to fix this!
Thanks,
Diddy

[sboden]

You can use "unique connections" ... the uniqueness only applies to connections to the same database of course (the option was also renamed in 3.2 to something ""transactional transformations" or so): it just means that instead of a step opening its own connection by default, it will check whether a certain connection is already open and reuse that.

You could have done a search on this forum on "No statements may be issued when any streaming result sets are open and in use on a given connection". There are/were some more people with your MySql problem.

You can try to switch off "Use result streaming cursor" on the connection dialog tab of your MySql Connection.

Regards,
Sven

[diddy]

Hi Sven,
Thanks a lot for your reply!
I applied the changes ('unique connection', switch off "Use result streaming cursor"), but now I get the error:

Streaming result set com.mysql.jdbc.RowDataDynamic@18f307 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.
Caused by: java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@18f307 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.

(This happened the last time as well).
So, can you please tell me what else I am doing wrong? I would really be thankful if we could solve this issue.
Best regards,
Diddy

[sboden]

And now you need more of a MySql expert than me ... from memory MySql doesn't implement something they had better implemented.... and this now bites you in the a**.

Regards,
Sven

[diddy]

Ok, thanks Sven! Can anybody else please help me with this specific problem?

[MattCasters]

Well, your only option left is to disable the streaming option for your MySQL connection.

[diddy]

Hi Matt,

WOW!!! IT IS WORKING NOW!!!

Thanks a lot for your answer!
I have set all to
Extra attribute [STREAM_RESULTS] = N
(Use result streaming cursor = No)
Best regards,
Diddy

[diddy]

If somebody else should once have the same problem, here is the summary:

Error Message:
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.

You can avoid this by going the the transaction settings (CTRL+T) /Miscellenious and click on 'Make the transaction database transactional'. This basically creates unique connections to the database, because without this each step would create its own conenction to the database.

Error Message:
Streaming result set com.mysql.jdbc.RowDataDynamic@18f307 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.
Caused by: java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@18f307 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.

You can solve this by switching of 'Use result streaming cursor'. The data will not be streamed anymore, but instead, all loaded/written in one go.

[MattCasters]

Please note that the last bit of information is specific to MySQL.