| Issue 270: | connect function timeout not working with SQL Server using SSPI or documentation incorrect | |
| 2 people starred this issue and may be notified of changes. | Back to list |
What steps will reproduce the problem?
1. Attempt to connect to incorrect server name or instance using following code:
pyodbc.connect('DRIVER={SQL Server};SERVER=\\servername\inst01;', timeout=10)
What is the expected output?
Exception to be raised in 'timeout' settings value (10 seconds in this case)
What do you see instead?
Default timeout reached and then pyodbc.Error raised
What version of the product are you using?
python 2.7 | pyodbc 3.0.5
On what operating system?
Windows 7 64bit | SQL Server 2008 R2
Please provide any additional information below.
I believe the problem may be an incorrect setting is being applied to the SQLSetConnectAttr function in connection.cpp (line 75). The setting being applied is SQL_ATTR_LOGIN_TIMEOUT; which the spec says, "The query timeout period expired before the connection to the data source completed. The timeout period is set through SQLSetConnectAttr, SQL_ATTR_LOGIN_TIMEOUT."
There is another setting that is for the data source connection timeout named "SQL_ATTR_CONNECTION_TIMEOUT"; which the spec says, "The connection timeout period expired before the data source responded to the request. The connection timeout period is set through SQLSetConnectAttr, SQL_ATTR_CONNECTION_TIMEOUT."
Jun 12, 2012
#1
dkleeham...@gmail.com
Feb 16, 2013
Any update on that? I am setting timeout=10 as above and some connections timeout after 2 minutes. Connecting from SuSE Linux using FreeTDS driver version 0.82 SQLServer 2008R2
Sep 6, 2013
There is Connection_settimeout function that set SQL_ATTR_CONNECTION_TIMEOUT on PyObject but it is not possible to use it before Connect as PyObject does not exist. So quick solution is simply set timeout directly in Connect function and update timeout attribute of PyObject.
--- connection.cpp.orig 2013-09-07 10:12:48.000000000 +0400
+++ connection.cpp 2013-09-07 10:51:16.000000000 +0400
@@ -72,6 +72,12 @@
if (timeout > 0)
{
Py_BEGIN_ALLOW_THREADS
+ ret = SQLSetConnectAttr(hdbc, SQL_ATTR_CONNECTION_TIMEOUT, (SQLPOINTER)timeout, SQL_IS_UINTEGER);
+ Py_END_ALLOW_THREADS
+ if (!SQL_SUCCEEDED(ret))
+ RaiseErrorFromHandle("SQLSetConnectAttr(SQL_ATTR_CONNECTION_TIMEOUT)", hdbc, SQL_NULL_HANDLE);
+
+ Py_BEGIN_ALLOW_THREADS
ret = SQLSetConnectAttr(hdbc, SQL_ATTR_LOGIN_TIMEOUT, (SQLPOINTER)timeout, SQL_IS_UINTEGER);
Py_END_ALLOW_THREADS
if (!SQL_SUCCEEDED(ret))
@@ -197,7 +203,7 @@
cnxn->hdbc = hdbc;
cnxn->nAutoCommit = fAutoCommit ? SQL_AUTOCOMMIT_ON : SQL_AUTOCOMMIT_OFF;
cnxn->searchescape = 0;
- cnxn->timeout = 0;
+ cnxn->timeout = timeout;
cnxn->unicode_results = fUnicodeResults;
cnxn->conv_count = 0;
cnxn->conv_types = 0;
Indeed more elegant solution is separate timeouts:
SQL_ATTR_CONNECTION_TIMEOUT
SQL_ATTR_LOGIN_TIMEOUT
SQL_ATTR_QUERY_TIMEOUT
Mar 7, 2014
I tried this patch in connection.cpp but did not work form me. timeout still is unpredictable. |