Page 1 of 1
jdbctemplate.setTimeout(time_in_millisec) not working
Posted: Mon Jul 22, 2013 8:27 pm
by Milan Shrestha
Hi,
We are trying to set the execution time out for a query and we use the spring jdbctempalte to do that as below:
Code: Select all
jdbctemplate.setTimeout(time_in_miilis)
This is working perfectly fine in oracle but not in veritca. It seems like vertica does not support query time out this way. So I used RUNTIMECAP feature of vertica to define a runtime cap for a USER and its working fine.
However, my purpose is to set it for only one query but not the whole user/schema. So, is there any way i can do it through the query or using jdbc api programatically within java code.
Thanking all in advance for your help.
Re: jdbctemplate.setTimeout(time_in_millisec) not working
Posted: Mon Jul 22, 2013 10:01 pm
by scutter
You can use SET SESSION RUNTIMECAP to change the cap dynamically.
Re: jdbctemplate.setTimeout(time_in_millisec) not working
Posted: Tue Jul 23, 2013 2:28 am
by Milan Shrestha
Hi ,
I am new to vertica and I believe SET SESSION RUNTIMECAP is command that is to be executed on the console. So can I execute this from jdbc connection from java and how? Do I need to execute this using dbaadmin user?
Re: jdbctemplate.setTimeout(time_in_millisec) not working
Posted: Tue Jul 23, 2013 1:32 pm
by id10t
Hi!
>> is to be executed on the console
and JDBC too
>> So can I execute this from jdbc connection from java and how?
Yes, here is
snippet (just apply your db properties - user, password, db name).
Code: Select all
daniel@synapse:~$ pwd
/home/daniel
daniel@synapse:~$ javac -cp /opt/vertica/java/lib/:`pwd` Runtimecap.java
daniel@synapse:~$ java -cp `pwd`:/opt/vertica/java/lib/vertica_jdk_5.jar Runtimecap
Query reports that RUNTIMECAP is set to: 00:10
daniel@synapse:~$
>> Do I need to execute this using dbaadmin user?
* This command requires superuser privileges if the RUNTIMECAP is being increased over the user's RUNTIMECAP limit.
* Normal users can change the RUNTIMECAP of their own sessions to any value below their own RUNTIMECAP. They cannot increase the RUNTIMECAP beyond any limit set for them by the superuser.
link
Re: jdbctemplate.setTimeout(time_in_millisec) not working
Posted: Tue Jul 23, 2013 4:50 pm
by Milan Shrestha
Thanks sKwa. This works. However, is there any way besides the exception message to identity that the exception has been caused by runtimecap. I see the error code to be 100081 even if i run the code with wrong query syntax.
Re: jdbctemplate.setTimeout(time_in_millisec) not working
Posted: Tue Jul 23, 2013 7:36 pm
by id10t
Hi!
>> However, is there any way besides the exception message to identity that the exception has been caused by runtimecap.
I don't think so, because it should satisfy JDBC API and I believe it always throws a standard API java.sql.SQLException with appropriate error code (that implemented as enums).
Try to get all error codes and messages with reflection and filter them for "runtimecap" or decompile driver (
online service) however its restricted by EULA
Re: jdbctemplate.setTimeout(time_in_millisec) not working
Posted: Tue Jul 23, 2013 9:50 pm
by Milan Shrestha
Thanks sKwa . This was a great help and saved my time.