Add err.number 503 to error routine.

bfuchs
bfuchs used Ask the Experts™
on
Hi Experts,

We have a function that opens a CSV file, loops thru all records and uploads each record to a web site thru an API call.
Now we realized some time that site is offline and would like to add to the existing error routine below logic that if error number is 503 then it should wait a minute and try again call that API (the command that caused to throw the error).

Err_Proc:
    Select Case Err.Number
        Case 9
            '' display a message if you want
           Resume Next  '''' assuming you want to just pick up at the statement after the error
        Case Else
            s = "Insert into API_Errors(ErrorMessage,TableName, [TableID],FileName, ValuesSent) Values ('" & Err.Number & "--" & Err.Description & "','" & sTable & "'," & Nz(pk, "") & ",'" & sFile & "','') "
            CurrentDb.Execute s
'            MsgBox Err.Number & "--" & Err.Description, vbOKOnly
'            Resume ExitImportDataToCaspio
            Resume Next
    End Select

Open in new window


PS. for a detailed description of the function in question see here
.
Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
Draw a flow diagram of your desired logic. You will pretty quickly see, that this should not be done via the error handler. You need some abstraction. You don't call the method for the APi call directly. E.g.

Option Compare Database
Option Explicit

Private Function ApiCallImpl() As Boolean

  On Local Error GoTo LocalError
  
  ' The actual API call..
  ApiCallImpl = True ' Call was successful.
  Exit Function
  
LocalError:
  ApiCallImpl = (Err.Number <> 503)
  If ApiCallImpl Then
    Resume Next
  End If

End Function

Private Function ApiCallIntf() As Boolean

  ApiCallIntf = ApiCallImpl
  If Not ApiCallImpl Then
    Sleep 60000
    ApiCallIntf = ApiCallImpl
  End If

End Function

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You could use the Sleep API call. An implementation is shown in my article:

Modern/Metro style message box and input box for Microsoft Access 2013+

Browse to the paragraph Simulating dialogue mode.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Are you calling this function manually, or does it run automatically, every so often?

If it is run automatically, and you can wait for the next iteration, then just elegantly exit from your function and wait for the next upload process.

If not, I would agree with Gustav about adding a pause into your code, the Sleep API would work or you could simply create your own Pause procedure and use the Timer or Now functions to initiate a loop for some period of time. followed by a Resume, to attempt to run the same line of code again.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Hi Experts,

You could use the Sleep API call...
...followed by a Resume, to attempt to run the same line of code again
I have been using already API sleep function in my code, and therefore my main question was how to ensure it tries to execute again (after sleeping) the same line of code which caused the error, taking in consideration that it can come from different locations in my function.

@ste5an,
Your code seems like addressing this issue, however will have to implement in my code & test it.

Thanks,
Ben
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
I have been using already API sleep function in my code, and therefore my main question was how to ensure it tries to execute again (after sleeping) the same line of code which caused the error, taking in consideration that it can come from different locations in my function.

How could we know? It was not was you asked originally.

Resume [Next] will let the code continue where it left.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Your code uses:
Resume NEXT

Open in new window

IN order to rerun the line that raised the error, you need to simply use:
Resume

Open in new window

Thank you my experts!
Hi experts,

A quick question.

The code posted above
 s = "Insert into API_Errors(ErrorMessage,TableName, [TableID],FileName, ValuesSent) Values ('" & Err.Number & "--" & Err.Description & "','" & sTable & "'," & Nz(pk, "") & ",'" & sFile & "','') "
            CurrentDb.Execute s

Open in new window


inserted the following in the ErrorMessage column
<!DOCTYPE HTML PUBLIC ~-//W3C//DTD HTML 4.01//EN~~http://www.w3.org/TR/html4/strict.dtd~>
<HTML><HEAD><TITLE>Service Unavailable</TITLE>
<META HTTP-EQUIV=~Content-Type~ Content=~text/html; charset=us-ascii~></HEAD>
<BODY><h2>Service Unavailable</h2>
<hr><p>HTTP Error 503. The service is unavailable.</p>
</BODY></HTML>

Open in new window


Just realized its not inserting the Err.Number (as its not the first thing, followed by two dashes..).

Any idea why, and how can I figure out the number?

Thanks,
Ben
ste5anSenior Developer

Commented:
You need to query the HTTP result, not the Err object. Err is only populated by an VB(A) runtime error. Thus you need to look at the value in Status after the request.
Reason is simple: An HTTP 503 is not an error. It is simply a possible state of that resource.

btw, after looking at your code again:
Do NOT use New in normal variable declarations.

Dim variable As New ClassType

Open in new window

Cause it behaves differently than in OO languages like C# or VB or VB.NET. It will auto-create an instance on each variable access when the reference is Nothing:

Option Compare Database
Option Explicit

Public Sub TestDimAsNew()

  Dim variable As New clsTestClass
  
  Set variable = New clsTestClass
  variable.Test = "txtMyTextBox"
  Debug.Print "1"; variable.Test
  
  Set variable = Nothing
  Debug.Print "2"; variable.Test

End Sub

Open in new window

with

Option Compare Database
Option Explicit

Private m_Test As String

Public Property Get Test() As String
  
  Test = m_Test
  
End Property

Public Property Let Test(AValue As String)

  m_Test = AValue
  
End Property

Open in new window

This means that you cannot longer test for uninitalized instances. And when using COM+ objects, this means that when these error out and get Nothing, you wouldn't catch that. Thus it should be really avoided in most cases.
@ste5an,

Okay, So if I modify the code to the following will it work?
                    objHTTP.Send JsonConverter.ConvertToJson(patient)
                    If objHTTP.ResponseText <> "" Then
                        If InStr(1, objHTTP.ResponseText, " in field 'PatientID' is invalid because it does not exist in the related parent table 'Patients' (field 'PatientID')") > 0 Then
                            Call AddPatient(columns(iPatIDCol), access_token)
                            objHTTP.Send JsonConverter.ConvertToJson(patient)
                        Else
                            s = "Insert into API_Errors(ErrorMessage,TableName, [TableID],FileName, ValuesSent) Values ('" & Replace(Replace(objHTTP.ResponseText, """", "~"), "'", "") & "','" & sTable & "'," & pk & ",'" & sFile & "','" & JsonConverter.ConvertToJson(patient) & "') "
                            CurrentDb.Execute s
                            If InStr(1, objHTTP.ResponseText, "HTTP Error 503") > 0 Then
                                Sleep 1000
                                Resume
                            End If

                        End If
                        'Debug.Print objHTTP.ResponseText
                    Else

Open in new window


PS. Just guessing it will not work, as Resume is only applicable to cases of error.


Thanks,
Ben
ste5anSenior Developer

Commented:
To be honest, even when it would work, it is a terrible piece of code.. didn't I wrote you should refactor it? This is not an option. Cause your current code does too much things in one procedure. While this can work, it is on the other hand simply wrong in terms of software architecture and implementation.

The first step is to extract the HTTP handling and the logging into their own methods.  Then you need to thoroughly think about the flow. Cause imho it should like this base on the previous approach:

Option Compare Database
Option Explicit

Private Enum EnumApiCallStatus
  acsNoError,
  acsRetryNecessary,
  acsRetryFailed,
  acsRetrySucceded
  ' And maybe some more.
End Enum

Private Function ApiCallImpl(AParameters As Variant, ByRef OStatus As EnumApiCallStatus) As Boolean

  On Local Error GoTo LocalError

  ' The actual API call..
  ' Here you log the verbose/debug informations of your API calls.
  ApiCallImpl = True ' Call was successful.
  OStatus = acsNoError
  Exit Function

LocalError:
  ApiCallImpl = (Err.Number <> 503)
  If ApiCallImpl Then
    ' Here are only real exceptions/errors logged.
    ' These will not be propagated.
    Resume Next
    ' Or you set a different status here.
  Else
    OStatus = acsRetryNecessary
  End If

End Function

Private Function ApiCallIntf(Parameters As Variant, ByRef OStatus As EnumApiCallStatus) As Boolean
' No logging needed here.

  ApiCallIntf = ApiCallImpl(Parameters, OStatus)
  If OStatus = acsRetryNecessary Then
    ' Sleep or an other way of scheduling it.
    Sleep 60000
    ApiCallIntf = ApiCallImpl(Parameters, OStatus)
    If OStatus = acsNoError Then
      OStatus = acsRetrySucceded
    Else
      OStatus = acsRetryFailed
    End If
  End If

End Function

Open in new window

This is just a quick outline. Then your actual business logic looks readable:

Private Function AddPatientIntf(patient)

  Dim Status As EnumApiCallStatus

  If ApiCallIntf(patient, Status) Then 'JSON handling is down in the API call method.
     AddPatient(columns(iPatIDCol), access_token)  ' why an access_token as parameter??
  End If

End Function

Open in new window

Hi ste5an,

I'm not in today, will test it tom.
Really appreciate your help.

Thanks,
Ben
@ste5an,

To be honest
Okay so let be honest with you as well...
This code was originally designed by a more experienced developer (I'm not in that level yet...), and therefore may take some time till I get to implement your suggestions...

Will try do it step by step, and perhaps open a new thread to get it finalize, if necessary...

just a quick question.

ApiCallImpl = (Err.Number <> 503)
How does that coincide with the following.
You need to query the HTTP result, not the Err object. Err is only populated by an VB(A) runtime error

Thanks,
Ben
ste5anSenior Developer
Commented:
It doesn't. It is an artifact from your posts, cause I thought the you get error codes to handle in the first answer. Thus the inner method should look like

Option Compare Database
Option Explicit

Private Enum EnumApiCallStatus
  acsNoError,
  acsUnkownException,
  acsFailure,
  acsRetryNecessary,
  acsRetryFailed,
  acsRetrySucceded
  ' And maybe some more.
End Enum

Private Function ApiCallImpl(AParameters As Variant, ByRef OStatus As EnumApiCallStatus) As Boolean

  On Local Error GoTo LocalError

  Dim HttpClient As Object ' Your concrete type or late bindng.

  Dim Content as String

  ApiCallImpl = False

  ' The actual API call..
  ' Here you log the verbose/debug informations of your API calls.
  '..
  HttpClient.Send Content
  Select Case HttpClient.Status
    Case Is = 200
      ApiCallImpl = True
      OStatus = acsNoError

    Case Is = 503
      ApiCallImpl = False
      OStatus = acsRetryNecessary

    Case Else
      ApiCallImpl = False
      OStatus = acsFailure
  End Select
  Exit Function

LocalError:
  ApiCallImpl = False
  OStatus = acsUnkownException

End Function

Open in new window

Thank you ste5an!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial