MySQL - avoid or suppress error message - help, please

[stevengraff]

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.

ErrorMode (@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)

[JTaylor]

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

[Deana]

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:

Code (winbatch) Select Expand


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

[stevengraff]

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.

[stanl]

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.

[stevengraff]

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")

[stanl]

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.