Re: Postgresql and VBA - Connection Timeout
Wayne Mell <[email protected]> Sat, 13 Jul 2019 17:21:05 +0000
| Newsgroups | gmane.comp.db.postgresql.general,gmane.comp.db.postgresql.odbc |
|---|---|
| Message-ID | <CH2PR11MB4200496F19C4BCE1BF470E9FB0CD0@CH2PR11MB4200.namprd11.prod.outlook.com> |
--_000_CH2PR11MB4200496F19C4BCE1BF470E9FB0CD0CH2PR11MB4200namp_ Content-Type: text/plain; charset="iso-2022-jp" Content-Transfer-Encoding: quoted-printable Hello Hiroshi, Thank you for your reply. Your suggestion to try setting cnn.CommandTimeout =3D 0 before calling cn= n.Execute() worked perfectly and was exactly the solution I needed. Since = the cnn connection was what owned the Execute, it was the object that neede= d 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 <[email protected]> Sent: Friday, July 12, 2019 7:53 PM To: Wayne Mell Cc: Adrian Klaver; [email protected]; [email protected]= tgresql.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 =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]><mailto:adrian.klaver@aklave= r.com> Sent: Thursday, July 11, 2019 3:21 PM To: Wayne Mell; [email protected]<mailto:pgsql-general@lis= ts.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 =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 ISTM the above 2 lines are meaningless because the cnnCmd isn't use by the= following cnn.Execute command. > > Set cnnCmd =3D cnn.Execute("select pg_sleep(50);") Could you Please try calling cnnCmd.Execute() after setting cnnCmd.commandText or setting cnn.CommandTimeout =3D 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-3= 0000-td5992591.html > > -- Adrian Klaver [email protected]<mailto:[email protected]> [https://ipmcdn.avast.com/images/icons/icon-envelope-tick-green-avg-v1.png]= <http://www.avg.com/email-signature?utm_medium=3Demail&utm_source=3Dlink&ut= m_campaign=3Dsig-email&utm_content=3Demailclient> =1B$B%&%$%k%9=1B(B =1B$B%= U%j!<!#=1B(B www.avg.com<http://www.avg.com/email-signature?utm_medium=3Dem= ail&utm_source=3Dlink&utm_campaign=3Dsig-email&utm_content=3Demailclient> --_000_CH2PR11MB4200496F19C4BCE1BF470E9FB0CD0CH2PR11MB4200namp_ Content-Type: text/html; charset="iso-2022-jp" Content-Transfer-Encoding: quoted-printable <html> <head> <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-2022-= jp"> <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);"> Hello Hiroshi,</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);"> Thank you for your reply.</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);"> Your suggestion to <font size=3D"2"><font size=3D"2"><span style=3D"font-si= ze: 11pt; line-height: normal;">try setting cnn.CommandTimeout =3D 0 &= nbsp; before calling cnn.Execute() worked perfectly and was exactly the sol= ution 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.</span></font></font><= /div> <div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;= color: rgb(0, 0, 0);"> <font size=3D"2"><font size=3D"2"><span style=3D"font-size: 11pt; line-heig= ht: normal;"><br> </span></font></font></div> <div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;= color: rgb(0, 0, 0);"> <font size=3D"2"><font size=3D"2"><span style=3D"font-size: 11pt; line-heig= ht: normal;">I am very grateful for your help.</span></font></font></div> <div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;= color: rgb(0, 0, 0);"> <font size=3D"2"><font size=3D"2"><span style=3D"font-size: 11pt; line-heig= ht: normal;"><span id=3D"" title=3D"" class=3D""><br> </span></span></font></font></div> <div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;= color: rgb(0, 0, 0);"> <font size=3D"2"><font size=3D"2"><span style=3D"font-size: 11pt; line-heig= ht: normal;"><span id=3D"" title=3D"" class=3D"">Thank you,</span></span></= font></font></div> <div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;= color: rgb(0, 0, 0);"> <font size=3D"2"><font size=3D"2"><span style=3D"font-size: 11pt; line-heig= ht: normal;"><span id=3D"" title=3D"" class=3D""><br> </span></span></font></font></div> <div style=3D"font-family: Calibri, Helvetica, sans-serif; font-size: 12pt;= color: rgb(0, 0, 0);"> <font size=3D"2"><font size=3D"2"><span style=3D"font-size: 11pt; line-heig= ht: normal;"><span id=3D"" title=3D"" class=3D"">Wayne </span><br> </span></font></font></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> Inoue, Hiroshi <= [email protected]><br> <b>Sent:</b> Friday, July 12, 2019 7:53 PM<br> <b>To:</b> Wayne Mell<br> <b>Cc:</b> Adrian Klaver; [email protected]; pgsql-odbc@li= sts.postgresql.org<br> <b>Subject:</b> Re: Postgresql and VBA - Connection Timeout</font> <div> </div> </div> <div style=3D"background-color:#FFFFFF">Hi Wayne,<br> <br> <div class=3D"x_moz-cite-prefix">On 2019/07/13 0:05, Wayne Mell wrote:<br> </div> <blockquote type=3D"cite"> <div style=3D"font-family:Calibri,Helvetica,sans-serif; font-size:12pt; col= or:rgb(0,0,0)"> Thank you Adrian,</div> <div style=3D"font-family:Calibri,Helvetica,sans-serif; font-size:12pt; col= or:rgb(0,0,0)"> <br> </div> <div style=3D"font-family:Calibri,Helvetica,sans-serif; font-size:12pt; col= or: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; col= or:rgb(0,0,0)"> <br> </div> <div style=3D"font-family:Calibri,Helvetica,sans-serif; font-size:12pt; col= or: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; col= or:rgb(0,0,0)"> <br> </div> <div style=3D"font-family:Calibri,Helvetica,sans-serif; font-size:12pt; col= or: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; col= or:rgb(0,0,0)"> <br> </div> <div style=3D"font-family:Calibri,Helvetica,sans-serif; font-size:12pt; col= or: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; col= or:rgb(0,0,0)"> <br> </div> <div style=3D"font-family:Calibri,Helvetica,sans-serif; font-size:12pt; col= or:rgb(0,0,0)"> Thanks everyone who replied.</div> <div style=3D"font-family:Calibri,Helvetica,sans-serif; font-size:12pt; col= or:rgb(0,0,0)"> <br> </div> <div style=3D"font-family:Calibri,Helvetica,sans-serif; font-size:12pt; col= or:rgb(0,0,0)"> Wayne<br> </div> <div style=3D"font-family:Calibri,Helvetica,sans-serif; font-size:12pt; col= or:rgb(0,0,0)"> <br> </div> <div style=3D"font-family:Calibri,Helvetica,sans-serif; font-size:12pt; col= or:rgb(0,0,0)"> <br> </div> <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"x_divRplyFwdMsg" dir=3D"ltr"><font style=3D"font-size:11pt" face= =3D"Calibri, sans-serif" color=3D"#000000"><b>From:</b> Adrian Klaver <a class=3D"x_moz-txt-link-rfc2396E" href=3D"mailto:[email protected]= om"><[email protected]></a><br> <b>Sent:</b> Thursday, July 11, 2019 3:21 PM<br> <b>To:</b> Wayne Mell; <a class=3D"x_moz-txt-link-abbreviated" href=3D"mail= to:[email protected]"> [email protected]</a><br> <b>Subject:</b> Re: Postgresql and VBA - Connection Timeout</font> <div> </div> </div> <div class=3D"x_BodyFragment"><font size=3D"2"><span style=3D"font-size:11p= t"> <div class=3D"x_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> ></div> </span></font></div> </div> </blockquote> <br> <blockquote type=3D"cite"> <div> <div class=3D"x_BodyFragment"><font size=3D"2"><span style=3D"font-size:11p= t"> <div class=3D"x_PlainText"><br> > Set cnnCmd =3D New ADODB.Command<br> > cnnCmd.CommandTimeout =3D 0<br> </div> </span></font></div> </div> </blockquote> <br> <font size=3D"2">ISTM the above 2 lines are meaningless </font><font = size=3D"2"><font size=3D"2">because the cnnCmd isn't use by the following <br> cnn.Execute command.<br> <br> </font></font> <blockquote type=3D"cite"> <div> <div class=3D"x_BodyFragment"><font size=3D"2"><span style=3D"font-size:11p= t"> <div class=3D"x_PlainText">> <br> > Set cnnCmd =3D cnn.Execute("select pg_sleep(50);")</div> </span></font></div> </div> </blockquote> <br> <font size=3D"2"><font size=3D"2">Could you Please try<br> <br> calling cnnCmd.Execute() = after setting cnnCmd.commandText<br> <br> or<br> <br> setting cnn.CommandTimeout =3D 0 = before calling cnn.Execute()<br> <br> ?<br> <br> regards,<br> Hiroshi Inoue<br> <br> </font></font> <blockquote type=3D"cite"> <div> <div class=3D"x_BodyFragment"><font size=3D"2"><span style=3D"font-size:11p= t"> <div class=3D"x_PlainText">> <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> <a class=3D"x_moz-txt-link-abbreviated" href=3D"mailto:adrian.klaver@aklave= r.com">[email protected]</a><br> </div> </span></font></div> </div> </blockquote> <blockquote type=3D"cite"> <div> <div class=3D"x_BodyFragment"><font size=3D"2"><span style=3D"font-size:11p= t"></span></font></div> </div> </blockquote> <div id=3D"x_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2"><br> <table style=3D"border-top:1px solid #D3D4DE"> <tbody> <tr> <td style=3D"width:55px; padding-top:18px"><a href=3D"http://www.avg.com/em= ail-signature?utm_medium=3Demail&utm_source=3Dlink&utm_campaign=3Ds= ig-email&utm_content=3Demailclient" target=3D"_blank"><img alt=3D"" sty= le=3D"width:46px; height:29px" width=3D"46" height=3D"29" src=3D"https://ip= mcdn.avast.com/images/icons/icon-envelope-tick-green-avg-v1.png"></a></td> <td style=3D"width:470px; padding-top:17px; color:#41424e; font-size:13px; = font-family:Arial,Helvetica,sans-serif; line-height:18px"> =1B$B%&%$%k%9=1B(B =1B$B%U%j!<=1B(B｡ <a href=3D"http://www.avg.com/e= mail-signature?utm_medium=3Demail&utm_source=3Dlink&utm_campaign=3D= sig-email&utm_content=3Demailclient" target=3D"_blank" style=3D"color:#= 4453ea"> www.avg.com</a> </td> </tr> </tbody> </table> <a href=3D"#x_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2" width=3D"1" height=3D"1= "></a></div> </div> </div> </body> </html> --_000_CH2PR11MB4200496F19C4BCE1BF470E9FB0CD0CH2PR11MB4200namp_--