Anonymous procedure blocks under continuous execution

[sanjeev.gour]

Hello-

We attempting to run an anonymous procedure block under continuous execution but there seems to be a transaction related limitation which is causing some problem.If we don't run the  SQL under transaction, it forces us to do it under a transaction, and if we do, it complains that continuous execution cannot run under a transaction which looks reasonable.

Is it mandatory to run the anonymous procedure blocks in transaction?

We have a translator that returns data each 30 seconds, using that the functionality we are trying to achieve is this-

Create a procedure block like the following-

BEGIN

SELECT * from time_translator WHERE period = 30 WITHOUT RETURN; //this will return data every 30 seconds

SELECT * from some_other_table;

END

Run the above block continuously to retrieve data from some_other_table every 30 seconds.

Is this achievable using continuous queries and the anonymous procedure blocks?

Thanks,

Sanjeev.

[shawkins]

Are you running with autoCommitTxn=DETECT (the default)?  You may need to use autoCommitTxn=OFF.

Steve

[sanjeev.gour]

Thanks Steve. Where do I need to set the option? I am running it in a embedded test with a mock transaction manager.

[shawkins]

You set it as a connection property, or as an execution property.  See river Connection - Teiid 8.7 (draft) - Project Documentation Editor

[sanjeev.gour]

That did it.

Thank you, Steve.