Thread: Postgresql and VBA - Connection Timeout

Postgresql and VBA - Connection Timeout

From

Wayne Mell

Date:

11 July 2019, 23:04:34

Can someone help me with executing a postgres function from VBA? No matter what function I call, it always times out after exactly 30 seconds. I've tried to use the statement_timeout a million different ways and always get an error that states "Cancelling statement due to statement timeout" in Access.

Here is the code I've been using to test:

Dim cnn As ADODB.Connection

Dim cnnCmd As ADODB.Command

Set cnn = New ADODB.Connection

cnn.ConnectionString = "Driver={PostgreSQL Unicode(x64)};Server=postgres.office.server;Port=5432;Database=DBS;UID=postgresuser;PWD=postgrespwd;"

cnn.ConnectionTimeout = 0

cnn.Open

Set cnnCmd = New ADODB.Command

cnnCmd.CommandTimeout = 0

Set cnnCmd = cnn.Execute("select pg_sleep(50);")

There are other functions that I've tried to execute, and they all work as long as they take less than 30 seconds to run. I just started running pg_sleep to test.

If I log into pgAdmin with the same user name and password, and on the same machine, then the functions will execute no matter how long they take. It's only through VBA and the psqlODBC driver that I have the 30 second timeout limit.

Has anyone found a way to make this work? Thanks.

Re: Postgresql and VBA - Connection Timeout

From

Adrian Klaver

Date:

11 July 2019, 23:21:29

On 7/11/19 1:04 PM, Wayne Mell wrote:
> Can someone help me with executing a postgres function from VBA?  No 
> matter what function I call, it always times out after exactly 30 
> seconds.  I've tried to use the statement_timeout a million different 
> ways and always get an error that states "Cancelling statement due to 
> statement timeout" in Access.
> 
> Here is the code I've been using to test:
> 
> Dim cnn As ADODB.Connection
> Dim cnnCmd As ADODB.Command
> 
>   Set cnn = New ADODB.Connection
>         cnn.ConnectionString = "Driver={PostgreSQL 
> Unicode(x64)};Server=postgres.office.server;Port=5432;Database=DBS;UID=postgresuser;PWD=postgrespwd;"
>         cnn.ConnectionTimeout = 0
>         cnn.Open
> 
> Set cnnCmd = New ADODB.Command
> cnnCmd.CommandTimeout = 0
> 
> Set cnnCmd = cnn.Execute("select pg_sleep(50);")
> 
> There are other functions that I've tried to execute, and they all work 
> as long as they take less than 30 seconds to run.  I just started 
> running pg_sleep to test.
> 
> If I log into pgAdmin with the same user name and password, and on the 
> same machine, then the functions will execute no matter how long they 
> take.  It's only through VBA and the psqlODBC driver that I have the 30 
> second timeout limit.
> 
> Has anyone found a way to make this work?  Thanks.


https://www.postgresql-archive.org/BUG-odbc-statement-timeout-gets-set-to-30000-td5992591.html

> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Postgresql and VBA - Connection Timeout

From

George Weaver

Date:

11 July 2019, 23:24:52

On 11/07/2019 3:04 p.m., Wayne Mell wrote:

P {margin-top:0;margin-bottom:0;}
Can someone help me with executing a postgres function from VBA? No matter what function I call, it always times out after exactly 30 seconds. I've tried to use the statement_timeout a million different ways and always get an error that states "Cancelling statement due to statement timeout" in Access.

Here is the code I've been using to test:

Dim cnn As ADODB.Connection
Dim cnnCmd As ADODB.Command

Set cnn = New ADODB.Connection
   cnn.ConnectionString = "Driver={PostgreSQL Unicode(x64)};Server=postgres.office.server;Port=5432;Database=DBS;UID=postgresuser;PWD=postgrespwd;"
   cnn.ConnectionTimeout = 0
     cnn.Open

Set cnnCmd = New ADODB.Command
cnnCmd.CommandTimeout = 0

I found that the default of 0 meaning no timeout doesn't seem to work.

What happens if you set cnnCmd.CommandTimeout = 60 for the sleep test of 50?

George

Set cnnCmd = cnn.Execute("select pg_sleep(50);")

There are other functions that I've tried to execute, and they all work as long as they take less than 30 seconds to run. I just started running pg_sleep to test.

If I log into pgAdmin with the same user name and password, and on the same machine, then the functions will execute no matter how long they take. It's only through VBA and the psqlODBC driver that I have the 30 second timeout limit.

Has anyone found a way to make this work? Thanks.

-- 
Cleartag Software, Inc.
972 McMillan Avenue
Winnipeg, MB
R3M 0V7
(204) 284-9839 phone/cell
(204) 284-9838 fax
gweaver@cleartagsoftware.com

Fast. Accurate. Easy.

Re: Postgresql and VBA - Connection Timeout

From

Adrian Klaver

Date:

11 July 2019, 23:30:19

On 7/11/19 1:04 PM, Wayne Mell wrote:
> Can someone help me with executing a postgres function from VBA?  No 
> matter what function I call, it always times out after exactly 30 
> seconds.  I've tried to use the statement_timeout a million different 
> ways and always get an error that states "Cancelling statement due to 
> statement timeout" in Access.
> 
> Here is the code I've been using to test:
> 
> Dim cnn As ADODB.Connection
> Dim cnnCmd As ADODB.Command
> 
>   Set cnn = New ADODB.Connection
>         cnn.ConnectionString = "Driver={PostgreSQL 
> Unicode(x64)};Server=postgres.office.server;Port=5432;Database=DBS;UID=postgresuser;PWD=postgrespwd;"
>         cnn.ConnectionTimeout = 0
>         cnn.Open
> 
> Set cnnCmd = New ADODB.Command
> cnnCmd.CommandTimeout = 0
> 
> Set cnnCmd = cnn.Execute("select pg_sleep(50);")
> 
> There are other functions that I've tried to execute, and they all work 
> as long as they take less than 30 seconds to run.  I just started 
> running pg_sleep to test.
> 
> If I log into pgAdmin with the same user name and password, and on the 
> same machine, then the functions will execute no matter how long they 
> take.  It's only through VBA and the psqlODBC driver that I have the 30 
> second timeout limit.
> 
> Has anyone found a way to make this work?  Thanks.

Realized you are setting CommandTimeout = 0.

Maybe this thread will help?:
https://www.postgresql.org/message-id/1516895461336-0.post%40n3.nabble.com

Otherwise I would see if someone over on the -odbc list:

https://www.postgresql.org/list/pgsql-odbc/

could help.

> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Postgresql and VBA - Connection Timeout

From

Wayne Mell

Date:

12 July 2019, 18:05:24

Thank you Adrian,

The link you sent had an answer that worked.

I had to edit the psqlodbc35w.dll file and change SET statement_timeout = %d to SET statement_timeout = 0 .

Somewhere in the thread, Hiroshi Inoue mentioned that a SQL_QUERY_TIMEOUT statement was built into the driver starting with version 9.5 of the driver, but all of my attempts to find documentation on how to use this feature in VBA have failed.

It certainly would be a better way to handle this. Can anyone point me at some documentation? I've copied this to the ODBC list as well.

Thanks everyone who replied.

Wayne

From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Thursday, July 11, 2019 3:21 PM
To: Wayne Mell; pgsql-general@lists.postgresql.org
Subject: Re: Postgresql and VBA - Connection Timeout

On 7/11/19 1:04 PM, Wayne Mell wrote:
> Can someone help me with executing a postgres function from VBA? No
> matter what function I call, it always times out after exactly 30
> seconds. I've tried to use the statement_timeout a million different
> ways and always get an error that states "Cancelling statement due to
> statement timeout" in Access.
>
> Here is the code I've been using to test:
>
> Dim cnn As ADODB.Connection
> Dim cnnCmd As ADODB.Command
>
> Set cnn = New ADODB.Connection
>    cnn.ConnectionString = "Driver={PostgreSQL
> Unicode(x64)};Server=postgres.office.server;Port=5432;Database=DBS;UID=postgresuser;PWD=postgrespwd;"
>    cnn.ConnectionTimeout = 0
>      cnn.Open
>
> Set cnnCmd = New ADODB.Command
> cnnCmd.CommandTimeout = 0
>
> Set cnnCmd = cnn.Execute("select pg_sleep(50);")
>
> There are other functions that I've tried to execute, and they all work
> as long as they take less than 30 seconds to run. I just started
> running pg_sleep to test.
>
> If I log into pgAdmin with the same user name and password, and on the
> same machine, then the functions will execute no matter how long they
> take. It's only through VBA and the psqlODBC driver that I have the 30
> second timeout limit.
>
> Has anyone found a way to make this work? Thanks.

https://www.postgresql-archive.org/BUG-odbc-statement-timeout-gets-set-to-30000-td5992591.html

>
>

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Postgresql and VBA - Connection Timeout

From

Wayne Mell

Date:

12 July 2019, 18:05:24

Thank you Adrian,

The link you sent had an answer that worked.

I had to edit the psqlodbc35w.dll file and change SET statement_timeout = %d to SET statement_timeout = 0 .

It certainly would be a better way to handle this. Can anyone point me at some documentation? I've copied this to the ODBC list as well.

Thanks everyone who replied.

Wayne

Re: Postgresql and VBA - Connection Timeout

From

"Inoue, Hiroshi"

Date:

13 July 2019, 03:53:12

Hi Wayne,

On 2019/07/13 0:05, Wayne Mell wrote:

P {margin-top:0;margin-bottom:0;}
Thank you Adrian,

The link you sent had an answer that worked.

I had to edit the psqlodbc35w.dll file and change SET statement_timeout = %d to SET statement_timeout = 0 .

Somewhere in the thread, Hiroshi Inoue mentioned that a SQL_QUERY_TIMEOUT statement was built into the driver starting with version 9.5 of the driver, but all of my attempts to find documentation on how to use this feature in VBA have failed.

It certainly would be a better way to handle this. Can anyone point me at some documentation? I've copied this to the ODBC list as well.

Thanks everyone who replied.

Wayne

From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Thursday, July 11, 2019 3:21 PM
To: Wayne Mell; pgsql-general@lists.postgresql.org
Subject: Re: Postgresql and VBA - Connection Timeout

On 7/11/19 1:04 PM, Wayne Mell wrote:
> Can someone help me with executing a postgres function from VBA? No
> matter what function I call, it always times out after exactly 30
> seconds. I've tried to use the statement_timeout a million different
> ways and always get an error that states "Cancelling statement due to
> statement timeout" in Access.
>
> Here is the code I've been using to test:
>
> Dim cnn As ADODB.Connection
> Dim cnnCmd As ADODB.Command
>
> Set cnn = New ADODB.Connection
>    cnn.ConnectionString = "Driver={PostgreSQL
> Unicode(x64)};Server=postgres.office.server;Port=5432;Database=DBS;UID=postgresuser;PWD=postgrespwd;"
>    cnn.ConnectionTimeout = 0
>      cnn.Open
>

> Set cnnCmd = New ADODB.Command
> cnnCmd.CommandTimeout = 0

ISTM the above 2 lines are meaningless because the cnnCmd isn't use by the following
cnn.Execute command.

>
> Set cnnCmd = cnn.Execute("select pg_sleep(50);")

Could you Please try

calling cnnCmd.Execute() after setting cnnCmd.commandText

or

setting cnn.CommandTimeout = 0 before calling cnn.Execute()

?

regards,
Hiroshi Inoue

>
> There are other functions that I've tried to execute, and they all work
> as long as they take less than 30 seconds to run. I just started
> running pg_sleep to test.
>
> If I log into pgAdmin with the same user name and password, and on the
> same machine, then the functions will execute no matter how long they
> take. It's only through VBA and the psqlODBC driver that I have the 30
> second timeout limit.
>
> Has anyone found a way to make this work? Thanks.

https://www.postgresql-archive.org/BUG-odbc-statement-timeout-gets-set-to-30000-td5992591.html

>
>

--
Adrian Klaver
adrian.klaver@aklaver.com

ウイルスフリー｡ www.avg.com

Re: Postgresql and VBA - Connection Timeout

From

"Inoue, Hiroshi"

Date:

13 July 2019, 03:53:12

Hi Wayne,

On 2019/07/13 0:05, Wayne Mell wrote:

P {margin-top:0;margin-bottom:0;}
Thank you Adrian,

The link you sent had an answer that worked.

I had to edit the psqlodbc35w.dll file and change SET statement_timeout = %d to SET statement_timeout = 0 .

Somewhere in the thread, Hiroshi Inoue mentioned that a SQL_QUERY_TIMEOUT statement was built into the driver starting with version 9.5 of the driver, but all of my attempts to find documentation on how to use this feature in VBA have failed.

It certainly would be a better way to handle this. Can anyone point me at some documentation? I've copied this to the ODBC list as well.

Thanks everyone who replied.

Wayne

From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Thursday, July 11, 2019 3:21 PM
To: Wayne Mell; pgsql-general@lists.postgresql.org
Subject: Re: Postgresql and VBA - Connection Timeout

On 7/11/19 1:04 PM, Wayne Mell wrote:
> Can someone help me with executing a postgres function from VBA? No
> matter what function I call, it always times out after exactly 30
> seconds. I've tried to use the statement_timeout a million different
> ways and always get an error that states "Cancelling statement due to
> statement timeout" in Access.
>
> Here is the code I've been using to test:
>
> Dim cnn As ADODB.Connection
> Dim cnnCmd As ADODB.Command
>
> Set cnn = New ADODB.Connection
>    cnn.ConnectionString = "Driver={PostgreSQL
> Unicode(x64)};Server=postgres.office.server;Port=5432;Database=DBS;UID=postgresuser;PWD=postgrespwd;"
>    cnn.ConnectionTimeout = 0
>      cnn.Open
>

> Set cnnCmd = New ADODB.Command
> cnnCmd.CommandTimeout = 0

ISTM the above 2 lines are meaningless because the cnnCmd isn't use by the following
cnn.Execute command.

>
> Set cnnCmd = cnn.Execute("select pg_sleep(50);")

Could you Please try

calling cnnCmd.Execute() after setting cnnCmd.commandText

or

setting cnn.CommandTimeout = 0 before calling cnn.Execute()

?

regards,
Hiroshi Inoue

>
> There are other functions that I've tried to execute, and they all work
> as long as they take less than 30 seconds to run. I just started
> running pg_sleep to test.
>
> If I log into pgAdmin with the same user name and password, and on the
> same machine, then the functions will execute no matter how long they
> take. It's only through VBA and the psqlODBC driver that I have the 30
> second timeout limit.
>
> Has anyone found a way to make this work? Thanks.

https://www.postgresql-archive.org/BUG-odbc-statement-timeout-gets-set-to-30000-td5992591.html

>
>

--
Adrian Klaver
adrian.klaver@aklaver.com

ウイルスフリー｡ www.avg.com

Re: Postgresql and VBA - Connection Timeout

From

Wayne Mell

Date:

13 July 2019, 20:21:05

Hello Hiroshi,

Thank you for your reply.

Your suggestion to try setting cnn.CommandTimeout = 0 before calling cnn.Execute() worked perfectly and was exactly the solution I needed. Since the cnn connection was what owned the Execute, it was the object that needed the Command Timeout. That is a much nicer solution than editing the DLL.

I am very grateful for your help.

Thank you,

Wayne

From: Inoue, Hiroshi <h-inoue@dream.email.ne.jp>
Sent: Friday, July 12, 2019 7:53 PM
To: Wayne Mell
Cc: Adrian Klaver; pgsql-general@lists.postgresql.org; pgsql-odbc@lists.postgresql.org
Subject: Re: Postgresql and VBA - Connection Timeout

Hi Wayne,

On 2019/07/13 0:05, Wayne Mell wrote:

Thank you Adrian,

The link you sent had an answer that worked.

I had to edit the psqlodbc35w.dll file and change SET statement_timeout = %d to SET statement_timeout = 0 .

Somewhere in the thread, Hiroshi Inoue mentioned that a SQL_QUERY_TIMEOUT statement was built into the driver starting with version 9.5 of the driver, but all of my attempts to find documentation on how to use this feature in VBA have failed.

It certainly would be a better way to handle this. Can anyone point me at some documentation? I've copied this to the ODBC list as well.

Thanks everyone who replied.

Wayne

From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Thursday, July 11, 2019 3:21 PM
To: Wayne Mell; pgsql-general@lists.postgresql.org
Subject: Re: Postgresql and VBA - Connection Timeout

On 7/11/19 1:04 PM, Wayne Mell wrote:
> Can someone help me with executing a postgres function from VBA? No
> matter what function I call, it always times out after exactly 30
> seconds. I've tried to use the statement_timeout a million different
> ways and always get an error that states "Cancelling statement due to
> statement timeout" in Access.
>
> Here is the code I've been using to test:
>
> Dim cnn As ADODB.Connection
> Dim cnnCmd As ADODB.Command
>
> Set cnn = New ADODB.Connection
>    cnn.ConnectionString = "Driver={PostgreSQL
> Unicode(x64)};Server=postgres.office.server;Port=5432;Database=DBS;UID=postgresuser;PWD=postgrespwd;"
>    cnn.ConnectionTimeout = 0
>      cnn.Open
>

> Set cnnCmd = New ADODB.Command
> cnnCmd.CommandTimeout = 0

ISTM the above 2 lines are meaningless because the cnnCmd isn't use by the following
cnn.Execute command.

>
> Set cnnCmd = cnn.Execute("select pg_sleep(50);")

Could you Please try

calling cnnCmd.Execute() after setting cnnCmd.commandText

or

setting cnn.CommandTimeout = 0 before calling cnn.Execute()

?

regards,
Hiroshi Inoue

>
> There are other functions that I've tried to execute, and they all work
> as long as they take less than 30 seconds to run. I just started
> running pg_sleep to test.
>
> If I log into pgAdmin with the same user name and password, and on the
> same machine, then the functions will execute no matter how long they
> take. It's only through VBA and the psqlODBC driver that I have the 30
> second timeout limit.
>
> Has anyone found a way to make this work? Thanks.

https://www.postgresql-archive.org/BUG-odbc-statement-timeout-gets-set-to-30000-td5992591.html

>
>

--
Adrian Klaver
adrian.klaver@aklaver.com

ウイルスフリー｡ www.avg.com

Re: Postgresql and VBA - Connection Timeout

From

Wayne Mell

Date:

13 July 2019, 20:21:05

Hello Hiroshi,

Thank you for your reply.

I am very grateful for your help.

Thank you,

Wayne

Hi Wayne,

On 2019/07/13 0:05, Wayne Mell wrote:

Thank you Adrian,

The link you sent had an answer that worked.

I had to edit the psqlodbc35w.dll file and change SET statement_timeout = %d to SET statement_timeout = 0 .

Somewhere in the thread, Hiroshi Inoue mentioned that a SQL_QUERY_TIMEOUT statement was built into the driver starting with version 9.5 of the driver, but all of my attempts to find documentation on how to use this feature in VBA have failed.

It certainly would be a better way to handle this. Can anyone point me at some documentation? I've copied this to the ODBC list as well.

Thanks everyone who replied.

Wayne

From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Thursday, July 11, 2019 3:21 PM
To: Wayne Mell; pgsql-general@lists.postgresql.org
Subject: Re: Postgresql and VBA - Connection Timeout

On 7/11/19 1:04 PM, Wayne Mell wrote:
> Can someone help me with executing a postgres function from VBA? No
> matter what function I call, it always times out after exactly 30
> seconds. I've tried to use the statement_timeout a million different
> ways and always get an error that states "Cancelling statement due to
> statement timeout" in Access.
>
> Here is the code I've been using to test:
>
> Dim cnn As ADODB.Connection
> Dim cnnCmd As ADODB.Command
>
> Set cnn = New ADODB.Connection
>    cnn.ConnectionString = "Driver={PostgreSQL
> Unicode(x64)};Server=postgres.office.server;Port=5432;Database=DBS;UID=postgresuser;PWD=postgrespwd;"
>    cnn.ConnectionTimeout = 0
>      cnn.Open
>

> Set cnnCmd = New ADODB.Command
> cnnCmd.CommandTimeout = 0

ISTM the above 2 lines are meaningless because the cnnCmd isn't use by the following
cnn.Execute command.

>
> Set cnnCmd = cnn.Execute("select pg_sleep(50);")

Could you Please try

calling cnnCmd.Execute() after setting cnnCmd.commandText

or

setting cnn.CommandTimeout = 0 before calling cnn.Execute()

?

regards,
Hiroshi Inoue

>
> There are other functions that I've tried to execute, and they all work
> as long as they take less than 30 seconds to run. I just started
> running pg_sleep to test.
>
> If I log into pgAdmin with the same user name and password, and on the
> same machine, then the functions will execute no matter how long they
> take. It's only through VBA and the psqlODBC driver that I have the 30
> second timeout limit.
>
> Has anyone found a way to make this work? Thanks.

https://www.postgresql-archive.org/BUG-odbc-statement-timeout-gets-set-to-30000-td5992591.html

>
>

--
Adrian Klaver
adrian.klaver@aklaver.com

ウイルスフリー｡ www.avg.com