Re: Postgresql and VBA - Connection Timeout
"Inoue, Hiroshi" <[email protected]> Sat, 13 Jul 2019 09:53:12 +0900
| Newsgroups | gmane.comp.db.postgresql.general,gmane.comp.db.postgresql.odbc |
|---|---|
| Message-ID | <[email protected]> |
This is a multi-part message in MIME format. --------------D4789CBB9D2773B51132ED62 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 <[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 = 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 > [email protected] --- このメールは、AVG によってウイルス チェックされています。 http://www.avg.com --------------D4789CBB9D2773B51132ED62 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> </head> <body text="#000000" bgcolor="#FFFFFF"> Hi Wayne,<br> <br> <div class="moz-cite-prefix">On 2019/07/13 0:05, Wayne Mell wrote:<br> </div> <blockquote type="cite" cite="mid:MN2PR11MB4208BD4115201F36656E4158B0F20@MN2PR11MB4208.namprd11.prod.outlook.com"> <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"> <style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style> <div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> Thank you Adrian,</div> <div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> <br> </div> <div style="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="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> <br> </div> <div style="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 = %d to SET statement_timeout = 0 .</div> <div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> <br> </div> <div style="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 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.</div> <div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> <br> </div> <div style="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 me at some documentation? I've copied this to the ODBC list as well.</div> <div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> <br> </div> <div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> Thanks everyone who replied.</div> <div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> <br> </div> <div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> Wayne<br> </div> <div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> <br> </div> <div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> <br> </div> <div> <div style="font-family:Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)"> <br> </div> <hr tabindex="-1" style="display:inline-block; width:98%"> <div id="divRplyFwdMsg" dir="ltr"><font style="font-size:11pt" face="Calibri, sans-serif" color="#000000"><b>From:</b> Adrian Klaver <a class="moz-txt-link-rfc2396E" href="mailto:[email protected]"><[email protected]></a><br> <b>Sent:</b> Thursday, July 11, 2019 3:21 PM<br> <b>To:</b> Wayne Mell; <a class="moz-txt-link-abbreviated" href="mailto:[email protected]">[email protected]</a><br> <b>Subject:</b> Re: Postgresql and VBA - Connection Timeout</font> <div> </div> </div> <div class="BodyFragment"><font size="2"><span style="font-size:11pt"> <div class="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 different <br> > ways and always get an error that states "Cancelling statement due 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 = New ADODB.Connection<br> > cnn.ConnectionString = "Driver={PostgreSQL <br> > Unicode(x64)};Server=postgres.office.server;Port=5432;Database=DBS;UID=postgresuser;PWD=postgrespwd;"<br> > cnn.ConnectionTimeout = 0<br> > cnn.Open<br> ></div> </span></font></div> </div> </blockquote> <br> <blockquote type="cite" cite="mid:MN2PR11MB4208BD4115201F36656E4158B0F20@MN2PR11MB4208.namprd11.prod.outlook.com"> <div> <div class="BodyFragment"><font size="2"><span style="font-size:11pt"> <div class="PlainText"> <br> > Set cnnCmd = New ADODB.Command<br> > cnnCmd.CommandTimeout = 0<br> </div> </span></font></div> </div> </blockquote> <br> <font size="2">ISTM the above 2 lines are meaningless </font><font size="2"><font size="2">because the cnnCmd isn't use by the following <br> cnn.Execute command.<br> <br> </font></font> <blockquote type="cite" cite="mid:MN2PR11MB4208BD4115201F36656E4158B0F20@MN2PR11MB4208.namprd11.prod.outlook.com"> <div> <div class="BodyFragment"><font size="2"><span style="font-size:11pt"> <div class="PlainText"> > <br> > Set cnnCmd = cnn.Execute("select pg_sleep(50);")</div> </span></font></div> </div> </blockquote> <br> <font size="2"><font size="2">Could you Please try<br> <br> calling cnnCmd.Execute() after setting cnnCmd.commandText<br> <br> or<br> <br> setting cnn.CommandTimeout = 0 before calling cnn.Execute()<br> <br> ?<br> <br> regards,<br> Hiroshi Inoue<br> <br> </font></font> <blockquote type="cite" cite="mid:MN2PR11MB4208BD4115201F36656E4158B0F20@MN2PR11MB4208.namprd11.prod.outlook.com"> <div> <div class="BodyFragment"><font size="2"><span style="font-size:11pt"> <div class="PlainText">> <br> > There are other functions that I've tried to execute, and they all work <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="https://www.postgresql-archive.org/BUG-odbc-statement-timeout-gets-set-to-30000-td5992591.html" moz-do-not-send="true">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> <a class="moz-txt-link-abbreviated" href="mailto:[email protected]">[email protected]</a><br> </div> </span></font></div> </div> </blockquote> <blockquote type="cite" cite="mid:MN2PR11MB4208BD4115201F36656E4158B0F20@MN2PR11MB4208.namprd11.prod.outlook.com"> <div> <div class="BodyFragment"><font size="2"><span style="font-size:11pt"></span></font></div> </div> </blockquote> <div id="DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2"><br /> <table style="border-top: 1px solid #D3D4DE;"> <tr> <td style="width: 55px; padding-top: 18px;"><a href="http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient" target="_blank"><img src="https://ipmcdn.avast.com/images/icons/icon-envelope-tick-green-avg-v1.png" alt="" width="46" height="29" style="width: 46px; height: 29px;" /></a></td> <td style="width: 470px; padding-top: 17px; color: #41424e; font-size: 13px; font-family: Arial, Helvetica, sans-serif; line-height: 18px;">ウイルス フリー｡ <a href="http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient" target="_blank" style="color: #4453ea;">www.avg.com</a> </td> </tr> </table> <a href="#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2" width="1" height="1"> </a></div></body> </html> --------------D4789CBB9D2773B51132ED62--