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.

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