Timeout in SQLDatabaseMBS

How do you set the length of the timeout (presumably in seconds) for connections and queries in a SQLDatabaseMBS database — I couldn’t find it online? Is it an option? Is it different per database type?

what kind of timeout?

For MySQL you can set the options on the connection: MYSQL_OPT_CONNECT_TIMEOUT, MYSQL_OPT_READ_TIMEOUT and MYSQL_OPT_WRITE_TIMEOUT.

Other database types have similar properties, I think.

Mainly connection. I let the use put in their host, port, password settings then click the ‘Test’ button to check if they’ve put them in properly. I want to return a response in less than the current ~30 seconds.

Are you implying I need to set this option within the database, rather than within SQLDatabaseMBS (e.g. MYSQL_OPT_CONNECT_TIMEOUT)?

[code] dim db as new SQLDatabaseMBS

// set 10 seconds timeout
db.Option(“MYSQL_OPT_CONNECT_TIMEOUT”) = “10”
[/code]

like this for example.

just wondering if you know how to specify connection timeout for MSSQL ?

would be VERY handy!!

for ODBC, it would be “SQL_ATTR_QUERY_TIMEOUT” for queries (on command).
for OLE DB, it would be “DBPROP_INIT_TIMEOUT” for connecting (on connection).

To set a new value, assuming you have privileges:

SET GLOBAL connect_timeout=60;

Or to see the current value:

SHOW VARIABLES LIKE 'connect_timeout';

Sorry Russ, I didn’t see you said MSSQL. A quick search suggests it is through the server computer menus.

hi, Im just getting round to testing all this again.

my problem seems to be that if the server IP / Name is incorrect (or unreachable) the connect timeout takes ages to return (around 45 seconds)

the connection timeouts seem to have no effect at all!

details?
What do you set on options?
MySQL or other?

the connection string is ODBC:DRIVER={FREETDS};Server=" + myHost + “;UId=” + myUserName + “;PWD=” + myPassword + “;Database=” + myDatabaseName + “;TDS_VERSION=7.2;Port=” + str(myPort)

i tried adding “connection timeout=5;” to the end, which is the usual method in a sql server connection string.
however, it seems it doesn’t work.

if you use a working connection to a server, then change the server ip to one that doesn’t exist. the connection takes around 45 seconds to timeout.

i’m trying to bring that time down to a few seconds.

db.Option("SQL_ATTR_LOGIN_TIMEOUT") = "10" db.Option("SQL_ATTR_QUERY_TIMEOUT") = "10"

did you try something like this?

hmm.
doesn’t seem to make a difference. I still get the spinning pizza for 45 seconds.

Strange. Maybe not implemented in freetds, I wonder?

i found mention of this on a python forum (discussing the same issue freetds). i don’t know if makes sense. also its from 2005!

[quote]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.”[/quote]

it looks like there is another option that controls the connection, rather than the login.

Well, the SQL Plugin checks if you have set a Connection option named “SQL_ATTR_LOGIN_TIMEOUT” and passes the value as number to the ODBC driver.

I see there is also a SQL_ATTR_CONNECTION_TIMEOUT, which I can add for my plugin here.

what is equivalent for timeout in CubeSQL??

db.Option(“ConnectionTimeout”) = “30”

or similar can be used. Otherwise it uses default timeout.