jdbctemplate.setTimeout(time_in_millisec) not working

Moderator: NorbertKrupa

Post Reply
Milan Shrestha
Newbie
Newbie
Posts: 4
Joined: Mon Jul 22, 2013 7:24 pm

jdbctemplate.setTimeout(time_in_millisec) not working

Post by Milan Shrestha » Mon Jul 22, 2013 8:27 pm

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.

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: jdbctemplate.setTimeout(time_in_millisec) not working

Post by scutter » Mon Jul 22, 2013 10:01 pm

You can use SET SESSION RUNTIMECAP to change the cap dynamically.
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

Milan Shrestha
Newbie
Newbie
Posts: 4
Joined: Mon Jul 22, 2013 7:24 pm

Re: jdbctemplate.setTimeout(time_in_millisec) not working

Post by Milan Shrestha » Tue Jul 23, 2013 2:28 am

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?

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: jdbctemplate.setTimeout(time_in_millisec) not working

Post by id10t » Tue Jul 23, 2013 1:32 pm

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

Milan Shrestha
Newbie
Newbie
Posts: 4
Joined: Mon Jul 22, 2013 7:24 pm

Re: jdbctemplate.setTimeout(time_in_millisec) not working

Post by Milan Shrestha » Tue Jul 23, 2013 4:50 pm

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.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: jdbctemplate.setTimeout(time_in_millisec) not working

Post by id10t » Tue Jul 23, 2013 7:36 pm

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 :-)

Milan Shrestha
Newbie
Newbie
Posts: 4
Joined: Mon Jul 22, 2013 7:24 pm

Re: jdbctemplate.setTimeout(time_in_millisec) not working

Post by Milan Shrestha » Tue Jul 23, 2013 9:50 pm

Thanks sKwa . This was a great help and saved my time. :)

Post Reply

Return to “JDBC”