Unable to set query timeout

[GoogleCodeExporter]

Hi,

Trying to set query timeout to hard limit script execution time.

Quick patch just for testing

--- pypyodbc.py.orig    2014-03-07 16:08:39.208447167 +0200
+++ pypyodbc.py 2014-03-07 16:28:40.035181023 +0200
@@ -89,6 +89,7 @@
 SQL_MODE_DEFAULT = SQL_MODE_READ_WRITE = 0; SQL_MODE_READ_ONLY = 1
 SQL_AUTOCOMMIT_OFF, SQL_AUTOCOMMIT_ON = 0, 1
 SQL_IS_UINTEGER = -5
+SQL_ATTR_QUERY_TIMEOUT = 0
 SQL_ATTR_LOGIN_TIMEOUT = 103; SQL_ATTR_CONNECTION_TIMEOUT = 113
 SQL_COMMIT, SQL_ROLLBACK = 0, 1

@@ -2424,6 +2425,8 @@
             self.settimeout(timeout)
             ret = ODBC_API.SQLSetConnectAttr(self.dbc_h, SQL_ATTR_LOGIN_TIMEOUT, timeout, SQL_IS_UINTEGER);
             check_success(self, ret)
+            ret = ODBC_API.SQLSetConnectAttr(self.dbc_h, 
SQL_ATTR_QUERY_TIMEOUT, timeout, SQL_IS_UINTEGER);
+            check_success(self, ret)

         # Create one connection with a connect string by calling SQLDriverConnect

And test script

#!/usr/bin/python
import pypyodbc
DSN="Driver=FreeTDS;TDS_Version=8.0;Server=10.0.0.9;Port=1433;Database=test;UID=
test;PWD=secret"
sql = pypyodbc.connect(DSN, timeout=10).cursor()
sql.execute("WAITFOR DELAY '00:00:30'");

Unfortunately timeout does not apply

> time ./test.py
real    0m30.335s
user    0m0.026s
sys     0m0.011s

Any ideas?

Regards,
Aleksey

Original issue reported on code.google.com by aleksey....@gmail.com on 7 Mar 2014 at 2:42

[GoogleCodeExporter]

Finally I changed query timeout using the following patch

--- pypyodbc.py.orig    2014-03-07 16:08:39.208447167 +0200
+++ pypyodbc.py 2014-03-14 22:06:55.174814529 +0200
@@ -89,6 +89,7 @@
 SQL_MODE_DEFAULT = SQL_MODE_READ_WRITE = 0; SQL_MODE_READ_ONLY = 1
 SQL_AUTOCOMMIT_OFF, SQL_AUTOCOMMIT_ON = 0, 1
 SQL_IS_UINTEGER = -5
+SQL_ATTR_QUERY_TIMEOUT = 0
 SQL_ATTR_LOGIN_TIMEOUT = 103; SQL_ATTR_CONNECTION_TIMEOUT = 113
 SQL_COMMIT, SQL_ROLLBACK = 0, 1

@@ -1145,7 +1146,7 @@

 # The Cursor Class.
 class Cursor:
-    def __init__(self, conx, row_type_callable=None):
+    def __init__(self, conx, row_type_callable=None, timeout=0):
         """ Initialize self.stmt_h, which is the handle of a statement
         A statement is actually the basis of a python"cursor" object
         """
@@ -1170,6 +1171,11 @@
         check_success(self, ret)
         self._PARAM_SQL_TYPE_LIST = []
         self.closed = False
+        self.timeout = timeout
+
+        # Set the cursor's attribute of "timeout" (Actully QUERY_TIMEOUT)
+        if timeout != 0:
+            ret = ODBC_API.SQLSetStmtAttr(self.stmt_h, SQL_ATTR_QUERY_TIMEOUT, 
self.timeout, 0);

     def prepare(self, query_string):
@@ -2514,11 +2520,11 @@
         self.connected = 1

-    def cursor(self, row_type_callable=None):
+    def cursor(self, row_type_callable=None, timeout=0):
         #self.settimeout(self.timeout)
         if not self.connected:
             raise ProgrammingError('HY000','Attempt to use a closed connection.')
-        cur = Cursor(self, row_type_callable=row_type_callable)
+        cur = Cursor(self, row_type_callable=row_type_callable, 
timeout=timeout)
         # self._cursors.append(cur)
         return cur

Please consider including this patch in main version.

Regards,
Aleksey

Original comment by aleksey....@gmail.com on 14 Mar 2014 at 8:09

[GoogleCodeExporter]

Above patch tested using the following script

#!/usr/bin/python
import pypyodbc
DSN="Driver=FreeTDS;TDS_Version=8.0;Server=10.0.0.9;Port=1433;Database=test;UID=
test;PWD=secret"
sql = pypyodbc.connect(DSN, timeout=10).cursor(timeout=3)
sql.execute("WAITFOR DELAY '00:00:30'");

> time ./test.py
Traceback (most recent call last):
...
pypyodbc.OperationalError: (u'HYT00', u'[HYT00] [FreeTDS][SQL Server]Timeout 
expired')

real    0m3.585s
user    0m0.042s
sys     0m0.010s

Original comment by aleksey....@gmail.com on 14 Mar 2014 at 8:13

[GoogleCodeExporter]

Hi! Thanks for providing the way to solve the problem. I have used what you 
have suggested.

In version 1.3.2, the QUERY_TIMEOUT will be set default with the value 
connection.timeout (default to 0, which means forever), this is the way how 
pyodbc is doing with the query timeout and I think it's good to be compliant 
with that.

Or you can set it by calling cursor.set_timeout(timeout).

CONNECTION_TIMEOUT will default be same as LOGIN_TIMEOUT utill I can think of a 
more elegant way to set them.

Original comment by jiangwen...@gmail.com on 24 May 2014 at 3:26

• Changed state: Fixed