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

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

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

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.