You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Mark Stobbe <ma...@gmail.com> on 2020/11/10 13:49:52 UTC

Query timeout in QueryMetadata

Hi all,

Can we have an option to set query timeout on the metadata of the query,
similar to the statementFetchSize?

The workaround seems to involve a lot of subclasses via the adapter; or via
the connection but then I need to have the same timeout for all statements
or parse SQL to identify query..

Best regards,
Mark

Re: Query timeout in QueryMetadata

Posted by Mark Stobbe <ma...@gmail.com>.
Thank you for the answer. I was more leaning towards being able to set the timeout on the statement (i.e. https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#setQueryTimeout(int)) instead of wrapping around my own timeout mechanism.

Also referring to an early post: http://cayenne.195.n3.nabble.com/JDBC-Statement-Timeout-td4026414.html

Or 

http://cayenne.195.n3.nabble.com/Database-timeout-questions-td4024912.html

I can try creating a PR, although I would assume someone else can do it a lot quicker/better..

Mark

> On 10 Nov 2020, at 16:26, John Huss <jo...@gmail.com> wrote:
> 
> The answer might vary depending on your specific use case. One way to do it
> would be to:
> 
> 1) start an explicit transaction,
> 2) run a query to set the timeout
>            SQLExec.*query*("set statement_timeout = 60000").execute(context
> );
> 3) do your work
> 4) run a query to reset or unset the timeout
> 5) commit
> 
> TransactionFactory txFactory = CayenneRuntime.*getThreadInjector*
> ().getInstance(TransactionFactory.*class*);
> 
> Transaction tx = txFactory.createTransaction();
> 
> tx.begin();
> 
> *try* {
> 
> ... // do stuff
> 
> 
> tx.commit();
> 
> } *catch* (Exception e) {
> 
> tx.rollback();
> 
> *throw* e;
> 
> }
> 
> 
>> On Tue, Nov 10, 2020 at 7:50 AM Mark Stobbe <ma...@gmail.com> wrote:
>> 
>> Hi all,
>> 
>> Can we have an option to set query timeout on the metadata of the query,
>> similar to the statementFetchSize?
>> 
>> The workaround seems to involve a lot of subclasses via the adapter; or via
>> the connection but then I need to have the same timeout for all statements
>> or parse SQL to identify query..
>> 
>> Best regards,
>> Mark
>> 

Re: Query timeout in QueryMetadata

Posted by John Huss <jo...@gmail.com>.
The answer might vary depending on your specific use case. One way to do it
would be to:

1) start an explicit transaction,
2) run a query to set the timeout
            SQLExec.*query*("set statement_timeout = 60000").execute(context
);
3) do your work
4) run a query to reset or unset the timeout
5) commit

TransactionFactory txFactory = CayenneRuntime.*getThreadInjector*
().getInstance(TransactionFactory.*class*);

Transaction tx = txFactory.createTransaction();

tx.begin();

*try* {

... // do stuff


tx.commit();

} *catch* (Exception e) {

tx.rollback();

*throw* e;

}


On Tue, Nov 10, 2020 at 7:50 AM Mark Stobbe <ma...@gmail.com> wrote:

> Hi all,
>
> Can we have an option to set query timeout on the metadata of the query,
> similar to the statementFetchSize?
>
> The workaround seems to involve a lot of subclasses via the adapter; or via
> the connection but then I need to have the same timeout for all statements
> or parse SQL to identify query..
>
> Best regards,
> Mark
>