setQueryTimeout not work?

[mttc]

I try it with MSSQL 2005. and it's seem the is not work!
swtQueryTimeout is Apply only on lock?
I try with
jTDS 1.2.2
and
sqljdbc_2.0
with loop SP like that:
BEGIN
declare @a int
set @a=1
while @a<>99
set @a=1
END
the query not ended forever!

I copy thread:http://groups.google.com/group/comp.lang.java.programmer/
browse_thread/thread/ce769dddbb2700af?hl=en#
================================

mttc wrote:
> I try it with MSSQL 2005. and it's seem the is not work!
> swtQueryTimeout is Apply only on lock?
...
> the query not ended forever!

If you talk about [1], it claims to be a JDBC 4.0 compliant driver.
The API of java.sql.Statement cleary states that if "the limit is
exceeded, an SQLException is thrown.". This somewhat contradicts the
statement that is given by the JDBC 4.0 Final Release specification
in
13.1.3 ("Limiting the execution time for Statement Objects"):
"A JDBC driver must apply this limit to the execute, executeBatch,
executeQuery and executeUpdate methods. Once the data source has had
an
opportunity to process the request to terminate the running command,
a
SQLException will be thrown to the client [...]"
Obviously, the specification makes an additional requirement for the
SQLException to be thrown, namely the data source must have had an
opportunity to process the request to terminate the running command
(I
filed a bug report due to incomplete API docs).
My interpretation of this is, that it is not guaranteed that a
SQLException will be thrown if the limit is exceeded. But if a
SQLException is thrown, you can be sure that the running command has
been terminated already.
Yet, I think you should report a bug to MS.
Bye
Michael
[1] Microsoft SQL Server JDBC Driver 2.0 Community Technology Preview
(January 2009) -
<http://www.microsoft.com/downloads/details.aspx?
FamilyID=f914793a-6fb...>
========================================

So what we do?

[joe.we...@gmail.com]

The driver may be doing all it can, sending a cancel packet to the
DBMS when the
timeout occurs, but SQLServer has a non-preemptive threading
architecture, which
means the DBMS thread running your procedure will run to completion or
until it
decides at some point to check whether it has any further input, such
as the cancel
message. Typically the DBMS thread will do as much processing as it
can, perhaps
running until it has to do some I/O, giving it the time to check other
things.

[mttc]

I check Above loop with .NET, and I got timeout exception. I not see
any reason (according the JDBC spec) to implement it only by sending
cancel to the server. the app must sure that on any situation
operation cancel after timeout.

I sure that timeout with .NET stop waiting for unplug cable, even
socket not report error. I not think that have app that prefer
behaviour of waiting eternity.

[joe.we...@gmail.com]

I agree with you, in that the driver could/should have a (one) utility
thread that handles all timeouts for any connections, connection
attempts, statements etc. (never have one thread per connection),
but the question is what the driver can do to make the connection
viable after the timeout. Until the DBMS does respond to the original
query or the cancel, the connection is useless...

[mttc]

I not understand you. Why we cannot get timeout for execute, and how
people work with such driver?

[Adam Saxton [MSFT]]

Can you provide the code and driver/server version you are using? I've
been able to get the timeout exception in my own repro:

C:\Java>java TimeoutTest
Driver version is 2.0.1008.2
DBMS version is 9.00.3310
com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeInterruptException(SQLSe
rverException.java:171)
at
com.microsoft.sqlserver.jdbc.TDSCommand.interrupt(IOBuffer.java:4238)
at com.microsoft.sqlserver.jdbc.TimeoutTimer.run(IOBuffer.java:3885)
at java.lang.Thread.run(Unknown Source)

My Code:
PreparedStatement ps = cn.prepareCall("EXEC ap_TightLoop");

ps.setQueryTimeout(10);

ps.executeQuery();