My script (fragment below) blows up
sometimes, i.e. not always, at the boldened lines below. ErroMode (@off) doesn't suppress it. Any ideas to make this run smoother? Thanks much, sg.
QuoteErrorMode (@off)
DB = CreateObject("ADODB.Connection")
cConn = 'DRIVER={MySQL ODBC %mySqlVersion% Driver};SERVER=%serverName%;DATABASE=%dbname%;UID=%userName%;PWD=%userPW%;OPTION=3;' ; mySQL connection string
DB = ObjectCreate("ADODB.Connection") ;We are openning an ADO db connection Object
RS = ObjectCreate("ADODB.Recordset") ;We are opening a record set object to hold the results of a select query.
sqlExist = "select count(*) from %table% where Marked = '' or Marked is null "
DB.Open(cConn)
dbstate = DB.State
if error <> @false
if strindex( wberroradditionalinfo, "Access denied", 1, @Fwdscan)
credentialProb = @true
Message("Problem","Error %error%: %wberroradditionalinfo%. %@crlf%%@crlf%Please check server address, username, password, and database name and then try again.")
Exit
Endif
Endif
if dbState == 1 then existNew = db.Execute(sqlExist) ;; blowing up here sometimes. 3131
if ExistNew == "" ; ExistNew could legitimately be 0, but blank means a problem
timeNow = TimeYmdHms ( )
message("DB Connection Problem","Could not connect properly... please contact support. Timestamp = %timenow%.")
Exit
Endif
if dbState == 1 then existNew = existNew.getstring() ; this line blew up today
ErrorMode (@cancel)
Take a look at Intcontrol 72 and/or 73. What causes it to fail? Obviously tracking that down and building in some error trapping at that point is the best option.
Jim
ErrorMode doesn't handle fatal errors. You will need to use IntControl 73. Here is a nice tutorial that should help: http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+Tutorials+Trap~Errors.txt
Also note that the ADODB exposes an error object that can also contains useful data. You might consider adding code that looks something like this to your error handler:
oE = ObjectCreate("ADODB.Error")
display(1,"Connection Errors",DB.Errors.Count)
ForEach Err In DB.Errors
str = StrCat("Error #",oE.Number,@CRLF)
str = StrCat(str," ",oE.Description,@CRLF)
str = StrCat(str," (Source: ",oE.Source,")",@CRLF)
str = StrCat(str," (SQL State: ",oE.SQLState,")",@CRLF)
str = StrCat(str," (NativeError: ",oE.NativeError,")",@CRLF)
Message("Connection Error",str)
Next
oE=0
Reference: http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+Tutorials/OLE~TUTORIAL+WINBATCH~OLE~PROGRAMMING~-~Part~1.txt
Thanks; looks helpful... it turns out that the first bold line returns a trappable error, so if I see it I now skip the second bold line. I'm pretty sure it has to do with the query returning a null set or the connection (via Internet) quirking out.
Quote from: stevengraff on April 25, 2014, 09:16:29 AM
Thanks; looks helpful... it turns out that the first bold line returns a trappable error, so if I see it I now skip the second bold line. I'm pretty sure it has to do with the query returning a null set or the connection (via Internet) quirking out.
I have a habit of always setting the connectionTimeout=0 to avoid 'quirks' when even a CommandTimeout=0. This is especially true since I now have to work through a VPN Connection with MySQL databases and tables. I have also found it beneficial to turn off my corporate email and IM when processing large queries. Finally, I generally use a client-side cursor for my connection.
Quote from: stanl on April 26, 2014, 04:40:50 AM
I have a habit of always setting the connectionTimeout=0 to avoid 'quirks' when even a CommandTimeout=0.
Thanks Stan.
It looks like the defaults are 15 secs and 30 secs respectively... does 0 mean "wait forever?" Seems like that would present the alternate risk of "hanging" (vs. "blowing up")
Quote from: stevengraff on May 03, 2014, 04:47:30 AM
Seems like that would present the alternate risk of "hanging" (vs. "blowing up")
On the rare exception that I will get "ODBC Connection Failed" which impies the server is inactive, "hanging" has never been an issue. I currently work with MySQL from a corporate VPN (where I VPN in in when not on site), and it often takes 2-3 minutes before Recordset retrieval begins.
You can, of course, set your error handler with P3 to a separate UDF to monitor the connection or command state.