Can you give an example of how you would use this with Teiid?
We're using jasperserver from Jaspersoft to generate some reports. These use a jdbc connection to the Teiid server.
We'd like to create some temporary tables before executing the final select statement. However jasperserver can't handle this since it expects only one resultset.
I'm still not quite sure how this fits in as the usage here implies it's for use within a stored procedure. Are you talking about issuing statements directly from the client or from within a stored procedure? Note that a stored procedure can only ever return a single result set - and will only return an update count if it's a procedure that defines an instead of trigger.
I'm talking about issuing statements from a client not within a stored procedure.
So if I understand you correctly Jasper server is issuing a CREATE LOCAL TEMPORARY TABLE ... statement and getting back an update count and then failing?
Can the whole sequence of operations be done as an anonymous procedure issued as a single statement?
This works! Thank you Steven!
Just a quick side note on this. Today we had an example of an anonymous procedure that did not want to work. We always got the exception that using multiple last resources is not allowed. I know that configuring the datasources as xa-datasources resolves this issue. And indeed the procedure did work with xa-datasources.