Need Help Determining if Array is Empty on Database Return

Databases
1

Hopefully, this has not been covered elsewhere…I searched and could not find the info I needed.

I receive our database inquiries back as JSON. This is working fine. The issue, however, is when a particular call results in a successful processing of the request but does not return records.

Typical Example:
I initiate a call and receive a replay from the server. I parse the “details” and use the data. The return may look something like:

{ "Message":"Return", "Details":[ {"amount":"50","flag_added_to_payroll":0,"flag_credit":0,"flag_debit":1,"pay_period_start":"2019-08-16 00:00:00","reason":"test"}, {"amount":"50","flag_added_to_payroll":0,"flag_credit":0,"flag_debit":1,"pay_period_start":"2019-08-01 00:00:00","reason":"test"}, {"amount":"50","flag_added_to_payroll":1,"flag_credit":0,"flag_debit":1,"pay_period_start":"2017-03-16 00:00:00","reason":"test"}, {"amount":"50","flag_added_to_payroll":1,"flag_credit":0,"flag_debit":1,"pay_period_start":"2017-03-01 00:00:00","reason":"test"} ] }

An example of the problem case is:

{
     "Message":"Return",
     "Details":[]
}

This is happening when the server receives a call, processes it, and finds no records to return.

QUESTION: How do we identify this is the case in code? The following is an example of how we are parsing the returning JSON. I have played with several ways to determine if details[] is empty but keep getting an interesting assortment of errors. I’m hoping someone can shed some light on the matter for me! I am still fairly new to Xojo and make no claim to know anything with regard to “best practices” for this type of thing. If I’m going about this entirely wrong, please get me headed down the path I need to go.

[code]// Parse the JSON result

Dim aString As String = Response.DefineEncoding(Encodings.UTF8)
Dim d As Xojo.Core.Dictionary = Xojo.Data.ParseJSON(aString.ToText)
Dim results() As Auto = d.Lookup(“Details”, “”)

For Each result As Xojo.Core.Dictionary In results

// THIS IS WHERE I WANT TO CHECK RESULTS() FOR BEING EMPTY

Dim period, amount, reason, creditT, debitT, procT As String
Dim credit, debit, proc As Integer
If result.HasKey(“pay_period_start”) Then
period = result.value (“pay_period_start”)
End If
If result.HasKey(“flag_credit”) Then
credit = result.value(“flag_credit”)
creditT=Str(credit)
End If
If result.HasKey(“flag_debit”) Then
debit = result.value(“flag_debit”)
debitT= Str(debit)
End If
If result.HasKey(“flag_added_to_payroll”) Then
proc = result.value (“flag_added_to_payroll”)
procT= Str(proc)
End If
If result.HasKey(“amount”) Then
amount = result.value(“amount”)
End If
If result.HasKey(“reason”) Then
reason = result.value(“reason”)
End If

pmod_listbox.AddRow(period, creditT, debitT, procT, amount, reason)
Next

[/code]

Thanks for your help!

Bill

2

try:

dim results() as Auto results=d.Lookup("Details", results)

3

The only way I see an error here is if Details doesn’t exist and you get back “” from the Lookup. If that happens, you will get an exception since you can’t assign a string to an array. (And Antonio just beat me to the answer.)

Otherwise, if Details is there and simply empty, the loop will never execute so there is no reason to check it beforehand.

BTW, be aware that the For Each construct is not guaranteed to process items in order. If that matters, do it this way instead:

for index as integer = 0 to results.Ubound
  dim result as Xojo.Core.Dictionary = results( index )
  ...
4

Try this:

Dim d As Xojo.Core.Dictionary = Xojo.Data.ParseJSON(aString.ToText) Dim emptyA() As Auto Dim results() As Auto = d.Lookup("Details", emptyA) If results.Ubound<0 Then msgbox "Details are empty or missing"

5

Thanks for the feedback gents. @Kem Tekinay thanks for the tip on the ordering of the For/Next. Will be implementing that asap as ordering does matter in a lot of my applications.