Connection timeout, on opening a connection itself.

[michael h]

I see that you can set the overall timeout with cnxn.timeout = n --
but as I understood it SQL_ATTR_CONNECTION_TIMEOUT is supposed to set
the timeout on opening the connection... Looking in the source it
seems like the timeout attribute is used for the connection timeout
and query timeout (SQL_ATTR_QUERY_TIMEOUT). There's no way to set the
connection timeout before creating a Connection object.

I liked the idea being kicked around before that would let us pass
arbitrary SQL_ATTR_*

You were wondering previously about how everyone would like these to
be passed, I think keyword args would be best, and if someone prefers
setting up a dict, they can always use **dict..

Hopefully I'm not overlooking some other way to set this attribute.

Thanks..

- Michael

[mkleehammer]

To clarify since it has been awhile, my only concern with this
approach is that most keywords are simply appended to the connection
string. These would have to be recognized and handled differently,
which seems error prone.

If they all start with SQL_ATTR_, it seems safe enough. If it is just
SQL_, I would guess some database would have an ODBC keyword that
starts with that.

Are there any that don't start with SQL_ATTR that would be useful?

[michael h]

I didn't consider that. If something like:

pyodbc.connect(connstr, attr={SQL_ATTR_1: 1, SQL_ATTR_2: 2})

like suggested before will allow arbitrary attributes to be set from
here on out then I'm for it instead...
After searching there are a number more obscure driver specific
attributes that don't start with SQL_ATTR_ (http://msdn.microsoft.com/
en-us/library/aa177892%28SQL.80%29.aspx).

- Michael

[michael h]

Unfortunately as I just noticed, that page also reads: "The Microsoft®
SQL Server™ ODBC driver ignores the setting of
SQL_ATTR_CONNECTION_TIMEOUT. The SQL Server ODBC driver will not time
out on any operations other than login and query processing."

(which is the DBMS my current project uses)

I'd still like to have this ability in the future though... If anyone
can think of a workaround I'd be interested to hear it.

- Michael

[Grumpy]

Bottom line: Is it possible to set connection timeout? and how?

[Michael Kleehammer]

Yes, pass the timeout keyword to pyodbc.connect().  If provided, it is the number of seconds to wait for a login request to complete, set via a call to SQLSetConnectAttr(SQL_ATTR_LOGIN_TIMEOUT).

See http://msdn.microsoft.com/en-us/library/windows/desktop/ms713605(v=vs.85).aspx

Note that this is different than the Connection.timeout attribute, which sets SQL_ATTR_CONNECTION_TIMEOUT.  This is used for query timeouts and defaults to 0, which means "no timeout".

[Neil]

Hi,

This doesn't appear to work.  Setting the parameter has no effect and the connection attempt eventually times out after a minute or so if a server is offline.

Neil