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(dot)klaver(at)aklaver(dot)com>
> *Sent:* Thursday, July 11, 2019 3:21 PM
> *To:* Wayne Mell; pgsql-general(at)lists(dot)postgresql(dot)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(dot)klaver(at)aklaver(dot)com

---
このメールは、AVG によってウイルス チェックされています。
http://www.avg.com