Thread: ADO CommandTimeout vs Postgres statement_timeout parameter
I understand that the statement_timeout config parameter in postgres can be configured at the server level, user level and query level. I have tested each and they work as expected. What I wasn't expecting was for my CommandTimeout to be ignored / not passed through.
e.g.
Set p_objConn = Server.CreateObject("ADODB.Connection")
p_objConn.ConnectionString = "SOME DSN or DSNLess Connection String"
' e.g. DB.ConnectionString = "Driver={PostgreSQL UNICODE};Server=localhost;Port=5432;UID=someuser;PWD=somepassword;Database=somedatabase;"
p_objConn.ConnectionTimeout = 10
p_objConn.Open
p_objConn.CommandTimeout = 30 ' This has no effect on queries run against Postgres !!
If The ADO CommandTimeout value is set, could this be detected, check that the SQL Query hasn't already got a statement_timeout value and if not prepend the SQL Query as follows:
SET statement_timeout = 30000;
SELECT somedata FROM somedatabasetable WHERE somevalue=anothervalue,
Many thanks in advance.
Feel free to contact me if you need any further information.
RICHARD HETHERINGTON
From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Richard Hetherington
Hi, I have been debugging why the ADO CommandTimeout property has no effect on Postgres Queries and I believe that it is being ignored. Is this something that could be modified in a future ODBC Driver release. What I would like to see is an auto prepending of this value to a SQL Query being passed in if set.
I guess you are right. The current psqlODBC does not implement SQLSetConnectAttr/SQLSetStmtAttr(SQL_QUERY_TIMEOUT) and do nothing when it is called, as follows:
case SQL_QUERY_TIMEOUT: /* ignored */
mylog("SetStmtOption: SQL_QUERY_TIMEOUT, vParam = " FORMAT_LEN "\n", vParam);
/* "0" returned in SQLGetStmtOption */
break;
But I don't know whether setting ADO's CommandTimeout leads to setting SQL_QUERY_TIMEOUT. Could you show us the MyLog output of psqlODBC to confirm that?
Regards
Takayuki Tsunakawa
Hello, Richard, Inoue-san,
Richard, please post to pgsql-odbc ML as much as possible, because I’d like Inoue-san to have a look. He is currently the only committer of psqlODBC, and I’m still an apprentice.
I implemented SQL_QUERY_TIMEOUT, which seems to work. Please find the attached patch.
Inoue-san,
At first, I used CC_send_settings() to execute “SET statement_timeout” and refactored it. After that, I realized I can use CC_send_query() and used it instead. However, I’d like the refactored code to be applied too.
Regards
Takayuki Tsunakawa
From: Richard Hetherington [mailto:hetheringtonrichard@hotmail.com]
Sent: Friday, May 20, 2016 7:09 PM
To: Tsunakawa, Takayuki/綱川 貴之
Subject: RE: [ODBC] ADO CommandTimeout vs Postgres statement_timeout parameter
Many thanks for you advice. I realised after receiving your email that I had made a fundamental error - I was logging from a website to my desktop folder which clearly isn't going to be writable by a Web Identity Account - oops.
Attached are 2 log files which I have cleaned up to remove all UID, PWD, Server values etc.
You should see that I set the SQL_QUERY_TIMEOUT via the ADO CommandTimeout = 60 seconds
This value has been logged but has no effect on the long running query.
[7212-0.181]SetStmtOption: SQL_QUERY_TIMEOUT, vParam = 60
I hope this is useful.
If you need anything esle, let me know.
Many thanks and have a good weekend,
RICHARD
From: tsunakawa.takay@jp.fujitsu.com
To: hetheringtonrichard@hotmail.com
Subject: RE: [ODBC] ADO CommandTimeout vs Postgres statement_timeout parameter
Date: Fri, 20 May 2016 02:39:30 +0000
Hello,
If SQL_QUERY_TIMEOUT is set on the connection or statement, mylog_xxx.log will have entries like this:
[140168204601088]PGAPI_SetConnectAttr for 0x8d5360: 0 0xa
[140168204601088]PGAPI_SetConnectOption: entering fOption = 0 vParam = 10
[140168204601088]SetStmtOption: SQL_QUERY_TIMEOUT, vParam = 10
...
[140168204601088][[SQLSetStmtAttr]] Handle=0x8e2110 0,10
[140168204601088]PGAPI_SetStmtAttr Handle=0x8e2110 0,10(0xa)
[140168204601088]PGAPI_SetStmtOption: entering...
[140168204601088]SetStmtOption: SQL_QUERY_TIMEOUT, vParam = 10
I'm not sure about the cause of your logging failure, but perhaps you will be able to log to another directory by creating the following registry value:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\<your ODBC driver name>\Logdir
type: REG_SZ
value: directory path
Regards
Takayuki Tsunakawa
Attachment
What does SQL_QUERY_TIMEOUT means for e.g.SQLBulkOperations()?
regards,
Hiroshi Inoue
Hello, Richard, Inoue-san,
Richard, please post to pgsql-odbc ML as much as possible, because I’d like Inoue-san to have a look. He is currently the only committer of psqlODBC, and I’m still an apprentice.
I implemented SQL_QUERY_TIMEOUT, which seems to work. Please find the attached patch.
Inoue-san,
At first, I used CC_send_settings() to execute “SET statement_timeout” and refactored it. After that, I realized I can use CC_send_query() and used it instead. However, I’d like the refactored code to be applied too.
Regards
Takayuki Tsunakawa
From: Richard Hetherington [mailto:hetheringtonrichard@hotmail.com]
Sent: Friday, May 20, 2016 7:09 PM
To: Tsunakawa, Takayuki/綱川貴之
Subject: RE: [ODBC] ADO CommandTimeout vs Postgres statement_timeout parameter
Many thanks for you advice. I realised after receiving your email that I had made a fundamental error - I was logging from a website to my desktop folder which clearly isn't going to be writable by a Web Identity Account - oops.
Attached are 2 log files which I have cleaned up to remove all UID, PWD, Server values etc.
You should see that I set the SQL_QUERY_TIMEOUT via the ADO CommandTimeout = 60 seconds
This value has been logged but has no effect on the long running query.
[7212-0.181]SetStmtOption: SQL_QUERY_TIMEOUT, vParam = 60
I hope this is useful.
If you need anything esle, let me know.
Many thanks and have a good weekend,
RICHARDFrom: tsunakawa.takay@jp.fujitsu.com
To: hetheringtonrichard@hotmail.com
Subject: RE: [ODBC] ADO CommandTimeout vs Postgres statement_timeout parameter
Date: Fri, 20 May 2016 02:39:30 +0000Hello,
If SQL_QUERY_TIMEOUT is set on the connection or statement, mylog_xxx.log will have entries like this:
[140168204601088]PGAPI_SetConnectAttr for 0x8d5360: 0 0xa
[140168204601088]PGAPI_SetConnectOption: entering fOption = 0 vParam = 10
[140168204601088]SetStmtOption: SQL_QUERY_TIMEOUT, vParam = 10
...
[140168204601088][[SQLSetStmtAttr]] Handle=0x8e2110 0,10
[140168204601088]PGAPI_SetStmtAttr Handle=0x8e2110 0,10(0xa)
[140168204601088]PGAPI_SetStmtOption: entering...
[140168204601088]SetStmtOption: SQL_QUERY_TIMEOUT, vParam = 10
I'm not sure about the cause of your logging failure, but perhaps you will be able to log to another directory by creating the following registry value:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\<your ODBC driver name>\Logdir
type: REG_SZ
value: directory path
Regards
Takayuki Tsunakawa
From: Inoue, Hiroshi [mailto:h-inoue@dream.email.ne.jp]
What does SQL_QUERY_TIMEOUT means for e.g.SQLBulkOperations()?
The ODBC spec doesn’t seem to specify it. The SQL_ATTR_QUERY_TIMEOUT description in the SQLSetStmtAttr() reference page mentions “SQL Statement”, whereas SQLBulkOperations() talks about “operations.” And the relationship between SQL statements and operations is not specified. I think that depends on the driver, and it’s legal that the query timeout applies to each operation.
Apart from that, I forgot to call QR_Destructor() after CC_send_query(). Revised patch attached.
Regards
Takayuki Tsunakawa