Board index » delphi » Query Timeout on MS SQL Server

Query Timeout on MS SQL Server

Hoi,

Voor de internet import van Effect gebruik ik de standaard TQuery van
Delphi. Omdat ik over het internet connect naar een SQL-server en het
internet nou eenmaal onbetrouwbaar is, komt het nogal eens voor dat een
query niets returnt. Omdat Effect dan hangt moet ik dus aan de query een
timeout kunnen opgeven, zodat alles weer redelijk soepeltjes verder gaat.
Ik ben er intussen achter dat je met de volgende ODBC call de timeout van
een ODBC query kunt instellen:

SQLSetStmtAttr(StmtHandle, SQL_ATTR_QUERY_TIMEOUT, value, 0)

Het probleem is dat ik dus een ODBC statement handle moet vinden voor mijn
TQuery. TQuery heeft wel een handle, maar dat is een BDE cursor handle.

Iemand idee?n?

I'm using TQuery's to retrieve data from a SQL Server 6.5 database over the
internet with an ODBC connection. Because the internet is not very
reliable, some queries do not return. It doesn't happen very often, but
when it does it hangs my application.

I guess that the way to prevent this is making sure that the query times
out after a minute or so, but i'm having problems making this work. I've
found two possible solutions:

1.
With the following ODBC call it is possible to set the timeout of an ODBC
statement:

SQLSetStmtAttr(StmtHandle, SQL_ATTR_QUERY_TIMEOUT, @value, 0)

The problem with this approach is that, as far as I can see, I can only get
a valid ODBC Handle (with dbiGetProp) for a query AFTER I open the query.
This of course makes it useless, because there is no point in setting a
timeout when the query is already open.

2.
Iv'e found a parameter in the SQL links driver for SQL Server, MAX QUERY
TIME, that sets the query timeout. In the help file it says that this
parameter is only valid for  a SQL Links connection. I have set this
parameter in my ODBC connection, but I have no way of knowing if it works,
because I cannot 'hang' a query on request.

In short, I would appreciate any information you might have on setting a
query timeout.

Thanks in advance,
Jeroen Cordfunke
Davilex Software bv.

 

Re:Query Timeout on MS SQL Server


Sorry, I was a bit too quick in sending this post. Here is the same post,
but now without a dutch header:

I'm using TQuery's to retrieve data from a SQL Server 6.5 database over the
internet with an ODBC connection. Because the internet is not very
reliable, some queries do not return. It doesn't happen very often, but
when it does it hangs my application.

I guess that the way to prevent this is making sure that the query times
out after a minute or so, but i'm having problems making this work. I've
found two possible solutions:

1.
With the following ODBC call it is possible to set the timeout of an ODBC
statement:

SQLSetStmtAttr(StmtHandle, SQL_ATTR_QUERY_TIMEOUT, @value, 0)

The problem with this approach is that, as far as I can see, I can only get
a valid ODBC Handle (with dbiGetProp) for a query AFTER I open the query.
This of course makes it useless, because there is no point in setting a
timeout when the query is already open.

2.
Iv'e found a parameter in the SQL links driver for SQL Server, MAX QUERY
TIME, that sets the query timeout. In the help file it says that this
parameter is only valid for  a SQL Links connection. I have set this
parameter in my ODBC connection, but I have no way of knowing if it works,
because I cannot 'hang' a query on request.

In short, I would appreciate any information you might have on setting a
query timeout.

Thanks in advance,
Jeroen Cordfunke
Davilex Software bv.

Re:Query Timeout on MS SQL Server


Hi Jeroen,

Why not post this message on the ODBCExpress newsgroup ??

news://www.odbcexpress.com/datasoft.odbcexpress

Check it out,

Rob

Quote
Jeroen wrote in message <01bd53e8$7a342e00$a100000a@jeroen>...
>Sorry, I was a bit too quick in sending this post. Here is the same post,
>but now without a dutch header:

>I'm using TQuery's to retrieve data from a SQL Server 6.5 database over the
>internet with an ODBC connection. Because the internet is not very
>reliable, some queries do not return. It doesn't happen very often, but
>when it does it hangs my application.

>I guess that the way to prevent this is making sure that the query times
>out after a minute or so, but i'm having problems making this work. I've
>found two possible solutions:

>1.
>With the following ODBC call it is possible to set the timeout of an ODBC
>statement:

>SQLSetStmtAttr(StmtHandle, SQL_ATTR_QUERY_TIMEOUT, @value, 0)

>The problem with this approach is that, as far as I can see, I can only get
>a valid ODBC Handle (with dbiGetProp) for a query AFTER I open the query.
>This of course makes it useless, because there is no point in setting a
>timeout when the query is already open.

>2.
>Iv'e found a parameter in the SQL links driver for SQL Server, MAX QUERY
>TIME, that sets the query timeout. In the help file it says that this
>parameter is only valid for  a SQL Links connection. I have set this
>parameter in my ODBC connection, but I have no way of knowing if it works,
>because I cannot 'hang' a query on request.

>In short, I would appreciate any information you might have on setting a
>query timeout.

>Thanks in advance,
>Jeroen Cordfunke
>Davilex Software bv.

Other Threads