I'm accessing a Postgres database via an ODBC connection string in an old program (HotDocs Document Assembly). This is the only way to access databases in HotDocs
I keep getting an error with a query I'm running that takes more than 30 seconds to execute:
ERROR: canceling statement due to statement timeout; Error while executing the query (80004005 - Unspecified error) Source: Microsoft OLE DB Provider for ODBC Drivers
Apparently postgres introduced a default query timeout of 30 seconds in psqlodbc v 9+, and I have not found a way to get around this.
Many similar questions I find online are either referring to the non-ODBC timeout, which can be resolved by running a query to update the timeout setting, or editing postgresql.conf. But my query isn't timing out in PGAdmin or other non-ODBC clients. It's only timing out with the ODBC connection.
Other posts I've found are using a different environment to access the ODBC driver (like VBA) and they seem to be able to pass different parameters to the ODBC via an ODBC API (https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlsetstmtattr-function?view=sql-server-ver16), which I don't have in HotDocs. In my case, I need a solution that I can apply to the ODBC driver settings (in the Windows ODBC manager / Registry) or to the connection string I'm using in HotDocs.
I've tried messing around with the connection string to add a Ignore Timeout=1 parameter (both within the "Extended Properties" parameter as well as its own separate parameter). Also tried a QueryTimeout=3600000 or QueryTimeout=1 parameter in the connection string and in the registry (based on this post: https://groups.google.com/g/ensemble-in-healthcare/c/8dRv1n-DwpU?pli=1). Also tried CommandTimeout=3600.
I tried adding SET statement_timeout=3600000 to the "ConnSettings" parameter (based on this link https://pgsql-odbc.postgresql.narkive.com/tq9IPYmq/odbc-sql-query-timeout)
I tried adding SET statement_timeout=3600000 directly in the postgres function code that I'm running from HotDocs.
It seems crazy to me that there isn't an easy way to set a different statement timeout (or no timeout) in an ODBC connection string.
The user in this post (https://postgrespro.com/list/thread-id/2448747) suggested that he was able to edit the actual DLL file for the ODBC driver to set statement_timeout=0. But I'm not sure how I would edit a binary DLL file to change that?
if you can be able to connect via DSN on ODBC Data Sources, you can check this box, it worked for me.