Add err.number 503 to error routine.
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).
PS. for a detailed description of the function in question see here
.
Thanks
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
PS. for a detailed description of the function in question see here
.
Thanks
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.
Modern/Metro style message box and input box for Microsoft Access 2013+
Browse to the paragraph Simulating dialogue mode.
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.
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.
CERTIFIED EXPERT
Hi Experts,
@ste5an,
Your code seems like addressing this issue, however will have to implement in my code & test it.
Thanks,
Ben
You could use the Sleep API call...
...followed by a Resume, to attempt to run the same line of code againI 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
CERTIFIED EXPERT
Hi experts,
A quick question.
The code posted above
inserted the following in the ErrorMessage column
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
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
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>
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
Senior Developer
CERTIFIED EXPERT
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.
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
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
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
CERTIFIED EXPERT
@ste5an,
Okay, So if I modify the code to the following will it work?
PS. Just guessing it will not work, as Resume is only applicable to cases of error.
Thanks,
Ben
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
PS. Just guessing it will not work, as Resume is only applicable to cases of error.
Thanks,
Ben
Senior Developer
CERTIFIED EXPERT
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:
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
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
CERTIFIED EXPERT
Hi ste5an,
I'm not in today, will test it tom.
Really appreciate your help.
Thanks,
Ben
I'm not in today, will test it tom.
Really appreciate your help.
Thanks,
Ben
CERTIFIED EXPERT
@ste5an,
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.
Thanks,
Ben
To be honestOkay 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