Re: Postgresql and VBA - Connection Timeout
Wayne Mell <[email protected]> Fri, 12 Jul 2019 15:05:24 +0000
| Newsgroups | gmane.comp.db.postgresql.general,gmane.comp.db.postgresql.odbc |
|---|---|
| Message-ID | <MN2PR11MB4208BD4115201F36656E4158B0F20@MN2PR11MB4208.namprd11.prod.outlook.com> |
--_000_MN2PR11MB4208BD4115201F36656E4158B0F20MN2PR11MB4208namp_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 =3D= %d to SET statement_timeout =3D 0 . Somewhere in the thread, Hiroshi Inoue mentioned that a SQL_QUERY_TIMEOUT s= tatement 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 <[email protected]> Sent: Thursday, July 11, 2019 3:21 PM To: Wayne Mell; [email protected] 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 =3D New ADODB.Connection > cnn.ConnectionString =3D "Driver=3D{PostgreSQL > Unicode(x64)};Server=3Dpostgres.office.server;Port=3D5432;Database=3DDBS;= UID=3Dpostgresuser;PWD=3Dpostgrespwd;" > cnn.ConnectionTimeout =3D 0 > cnn.Open > > Set cnnCmd =3D New ADODB.Command > cnnCmd.CommandTimeout =3D 0 > > Set cnnCmd =3D 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-3= 0000-td5992591.html > > -- Adrian Klaver [email protected] --_000_MN2PR11MB4208BD4115201F36656E4158B0F20MN2PR11MB4208namp_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <html> <head> <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-8859-= 1"> <style type=3D"text/css" style=3D"display:none;"> P {margin-top:0;margin-bo= ttom:0;} </style> </head> <body dir=3D"ltr"> <div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;= color: rgb(0, 0, 0);"> Thank you Adrian,</div> <div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;= color: rgb(0, 0, 0);"> <br> </div> <div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;= color: rgb(0, 0, 0);"> The link you sent had an answer that worked.</div> <div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;= color: rgb(0, 0, 0);"> <br> </div> <div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;= color: rgb(0, 0, 0);"> I had to edit the psqlodbc35w.dll file and change SET statement_timeout =3D= %d to SET statement_timeout =3D 0 .</div> <div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;= color: rgb(0, 0, 0);"> <br> </div> <div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;= color: rgb(0, 0, 0);"> Somewhere in the thread, Hiroshi Inoue mentioned that a SQL_QUERY_TIMEOUT s= tatement 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.</div> <div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;= color: rgb(0, 0, 0);"> <br> </div> <div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;= color: rgb(0, 0, 0);"> It certainly would be a better way to handle this. Can anyone point m= e at some documentation? I've copied this to the ODBC list as well.</= div> <div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;= color: rgb(0, 0, 0);"> <br> </div> <div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;= color: rgb(0, 0, 0);"> Thanks everyone who replied.</div> <div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;= color: rgb(0, 0, 0);"> <br> </div> <div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;= color: rgb(0, 0, 0);"> Wayne<br> </div> <div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;= color: rgb(0, 0, 0);"> <br> </div> <div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;= color: rgb(0, 0, 0);"> <br> </div> <div> <div id=3D"appendonsend"></div> <div style=3D"font-family:Calibri,Helvetica,sans-serif; font-size:12pt; col= or:rgb(0,0,0)"> <br> </div> <hr tabindex=3D"-1" style=3D"display:inline-block; width:98%"> <div id=3D"divRplyFwdMsg" dir=3D"ltr"><font style=3D"font-size:11pt" face= =3D"Calibri, sans-serif" color=3D"#000000"><b>From:</b> Adrian Klaver <a= [email protected]><br> <b>Sent:</b> Thursday, July 11, 2019 3:21 PM<br> <b>To:</b> Wayne Mell; [email protected]<br> <b>Subject:</b> Re: Postgresql and VBA - Connection Timeout</font> <div> </div> </div> <div class=3D"BodyFragment"><font size=3D"2"><span style=3D"font-size:11pt"= > <div class=3D"PlainText">On 7/11/19 1:04 PM, Wayne Mell wrote:<br> > Can someone help me with executing a postgres function from VBA? = No <br> > matter what function I call, it always times out after exactly 30 <br> > seconds. I've tried to use the statement_timeout a million diffe= rent <br> > ways and always get an error that states "Cancelling statement du= e to <br> > statement timeout" in Access.<br> > <br> > Here is the code I've been using to test:<br> > <br> > Dim cnn As ADODB.Connection<br> > Dim cnnCmd As ADODB.Command<br> > <br> > Set cnn =3D New ADODB.Connection<br> > cnn.ConnectionString =3D "Driver= =3D{PostgreSQL <br> > Unicode(x64)};Server=3Dpostgres.office.server;Port=3D5432;Database=3DD= BS;UID=3Dpostgresuser;PWD=3Dpostgrespwd;"<br> > cnn.ConnectionTimeout =3D 0<br> > cnn.Open<br> > <br> > Set cnnCmd =3D New ADODB.Command<br> > cnnCmd.CommandTimeout =3D 0<br> > <br> > Set cnnCmd =3D cnn.Execute("select pg_sleep(50);")<br> > <br> > There are other functions that I've tried to execute, and they all wor= k <br> > as long as they take less than 30 seconds to run. I just started= <br> > running pg_sleep to test.<br> > <br> > If I log into pgAdmin with the same user name and password, and on the= <br> > same machine, then the functions will execute no matter how long they = <br> > take. It's only through VBA and the psqlODBC driver that I have = the 30 <br> > second timeout limit.<br> > <br> > Has anyone found a way to make this work? Thanks.<br> <br> <br> <a href=3D"https://www.postgresql-archive.org/BUG-odbc-statement-timeout-ge= ts-set-to-30000-td5992591.html">https://www.postgresql-archive.org/BUG-odbc= -statement-timeout-gets-set-to-30000-td5992591.html</a><br> <br> > <br> > <br> <br> <br> -- <br> Adrian Klaver<br> [email protected]<br> </div> </span></font></div> </div> </body> </html> --_000_MN2PR11MB4208BD4115201F36656E4158B0F20MN2PR11MB4208namp_--