release 2.1.5 timeout option

[jjah...@googlemail.com]

I have tried to use the timeout option in 2.1.5 but without success.

The setup is:
WindowsXPSP3
Python 2.5.4

I thought I could use it as an argument in the connection string
('DSN=database',timeout=30)
But it fails saying 'timeout' is not a string or unicode value.

I then set it with pyodbc.timeout = 30

However, it seems not to have any effect in my case.

What I was hoping is that an update I am testing with a lock on would
cause the update to timeout and allow me to rollback and retry.

I have tried setting timeout before doing the connection and just
after doing the connection. But nothing happens. I still sit and wait
until the other connection commits the transaction.

I assume I am doing something wrong or I have misunderstood how this
timeout options is supposed to work.

It would be good if I could have the timeout as an option in the
connection string along with autocommit.

Ideas welcome as to what I am missing out.

Appreciate the work going on here. And glad we got a fix for 2.4 and
the Exceptions error

John Aherne

[mkleehammer]

On May 12, 3:46 pm, "jjahe...@googlemail.com"

<jjahe...@googlemail.com> wrote:
> I have tried to use the timeout option in 2.1.5 but without success.
>
> The setup is:
> WindowsXPSP3
> Python 2.5.4
>
> I thought I could use it as an argument in the connection string
> ('DSN=database',timeout=30)
> But it fails saying 'timeout' is not a string or unicode value.
>
> I then set it with pyodbc.timeout = 30
>
> However, it seems not to have any effect in my case.

Actually neither of those are what you want. The timeout attribute is
on the Connection object: http://code.google.com/p/pyodbc/wiki/Connection

You want something like:

cnxn = pyodbc.connect(...)
cnxn.timeout = 30
cursor = cnxn.cursor()
...

This performs the ODBC call: SQLSetConnectAttr(cnxn->hdbc,
SQL_ATTR_CONNECTION_TIMEOUT, (SQLPOINTER)timeout, SQL_IS_UINTEGER)

You can clear the timeout by setting it to 0.

When a timeout occurs, it will raise an OperationalError with
SQLSTATE HYT00 or HYT01.

See if that helps.

[jjah...@googlemail.com]

I guessed I had it wrong. Thanks for that. I'll check it out and see
what happens.

John Aherne

[jjah...@googlemail.com]

OK.Tried it. Just my luck. The database driver does not support this
option.

Thanks

John Aherne

On May 14, 4:59 pm, "jjahe...@googlemail.com"

[jjah...@googlemail.com]

On May 14, 4:59 pm, "jjahe...@googlemail.com"

[alexander...@gmx.net]

> OK.Tried it. Just my luck. The database driver does not support this
> option.

John, could you please post the exception you get?

SQL_ATTR_CONNECTION_TIMEOUT can be set at connection or at statement
level.

I usually access the Teradata RDBMS, and Teradata's ODBC driver does
not support SQL_ATTR_CONNECTION_TIMEOUT in SQLSetConnectAttr()
[connection level]. Instead, it returns "HYC00 - Optional feature not
implemented". According to http://msdn.microsoft.com/en-us/library/ms713605(VS.85).aspx
this means that "The value specified for the argument Attribute was a
valid ODBC connection or statement attribute for the version of ODBC
supported by the driver but was not supported by the driver."

*but* in the Teradata case SQLSetStmtAttr() - which may get called in
Cursor_New() [cursor.cpp] - supports SQL_ATTR_QUERY_TIMEOUT [statement
level]. Today, cnxn->timeout keeps its default value of 0 because of
the HYC00 described above and SQLSetStmtAttr() is not called.

I really wonder whether your RDBMS displays the same behaviour.

It might make sense to modify Connection_settimeout() in
connection.cpp to set cnxn->timeout even in the case of an HYC00. This
would trigger Cursor_New() in cursor.cpp to set the query timeout at
statement level.

Regards,
Alexander

[mkleehammer]

> It might make sense to modify Connection_settimeout() in
> connection.cpp to set cnxn->timeout even in the case of an HYC00. This
> would trigger Cursor_New() in cursor.cpp to set the query timeout at
> statement level.

I like this, but it doesn't give an indication that the act of
connecting would no longer have the timeout the user requested. Am I
understanding this correctly?

Perhaps two timeouts are called for? One on the Connection for
handling the connect and one on the Cursor itself. It might also be
handy to have a default Cursor timeout attached to the connection --
it is common to have a function that connects and sets attributes
appropriately to keep configuration in a single place.

Connection.timeout -> applies only to connection
Cursor.timeout -> applies only to statement
Connection.default_cursor_timeout or cursor_timeout -> Starting
timeout applied to new cursors.

Thoughts?

[Erik Myllymaki]

mkleehammer wrote:
> Perhaps two timeouts are called for? One on the Connection for
> handling the connect and one on the Cursor itself. It might also be
> handy to have a default Cursor timeout attached to the connection --
> it is common to have a function that connects and sets attributes
> appropriately to keep configuration in a single place.
>
> Connection.timeout -> applies only to connection
> Cursor.timeout -> applies only to statement
> Connection.default_cursor_timeout or cursor_timeout -> Starting
> timeout applied to new cursors.
>

Yes, this is the way to go.

I want a pretty short timeout on connect so I can move on to try my
secondary server quickly, or return an *unavailable* message quickly.

I want a long timeout on my cursor for those crazy slow queries I
haven't found time to optimize.

[Alexander]

mkleehammer wrote:
> I like this, but it doesn't give an indication that the act of
> connecting would no longer have the timeout the user requested. Am I
> understanding this correctly?

Well, you could still throw the exception after setting cnxn->timeout.
But...

> Perhaps two timeouts are called for? One on the Connection for
> handling the connect and one on the Cursor itself. It might also be
> handy to have a default Cursor timeout attached to the connection --
> it is common to have a function that connects and sets attributes
> appropriately to keep configuration in a single place.
>
> Connection.timeout -> applies only to connection
> Cursor.timeout -> applies only to statement
> Connection.default_cursor_timeout or cursor_timeout -> Starting
> timeout applied to new cursors.

... you are right, an explicit way to tell pyodbc the intended timeout
level is much better.

Alexander

[Alexander]

Erik Myllymaki wrote:

> I want a pretty short timeout on connect so I can move on to try my
> secondary server quickly, or return an *unavailable* message quickly.

Erik,

are you sure that SQL_ATTR_CONNECTION_TIMEOUT works for that purpose?

I thought that SQL_ATTR_LOGIN_TIMEOUT limits the amount of time spent
connecting.
I tried to check this, but currently our system is not busy enough.

Alexander