One sore point with JSON is its weak intrinsic type system. In particular date/time values can be painful.
In the wild you'll encounter several workarounds. Some will use ad-hoc formatted strings, and some will use either Unix "Epoch" timetamps (large numbers) or ISO 8601 date-time strings.
Here's a helper class JsonDate for performing conversions on the latter two date/time representations.
In this version IsoToDate() only handles ISO timestamps of the combined date and time type:
In the last case there is no time zone specification and this can be ambiguous. The default is to treat this as meaning you have a local time, but you can modify this by setting the property:
.AssumeZ = True
Since the VB Date type doesn't have the resolution to use milliseconds this method just truncates and ignores any .mmm part found in input strings.
While both Unix Epoch and ISO date/time values are considered Zulu/UTC/GMT values the VB Date type is normally considered a local time. By default the format conversion methods in JsonBag will perform this adjustment. However you can also choose to work entirely in UTC by setting the property:
.UtcMode = True
This causes the format conversions to skip the time zone adjustments.
There isn't a lot of code here. So if you merely need a conversion or two it is easy enough to copy some of the code inline into your programs instead of using the JsonDate class at all, aside from maintenance issues if bugs are later discovered. Everything but IsoToDate() is trivial code.
The code seems to be working properly, but having more eyes on it will help find and fix any lingering bugs.
A simple test using Excel 2003 (32-bit) seemed to work just fine, and I suspect newer versions of VBA can use it as well, both 32-bit and 64-bit.
The attachment has JsonDate.cls and a VB6 test Project and several test cases. The Project reads the test cases and displays the results of various conversions.
Last edited by dilettante; Apr 23rd, 2015 at 09:52 AM.
Yes, there were glitches so here is JsonDate 1.1 with these changes:
IsoToDate()'s AssumeZ handling was exactly backwards! Desired and corrected behavior:
Code:
Given that the local time zone is -5, and the ISO input is:
2015-04-20T15:51:38
UtcMode = False and AssumeZ = False mean ISO value with no
time zone should give a local time same as the ISO local time:
2015-04-20 15:51:38
UtcMode = True and AssumeZ = False mean ISO value with no time
zone should give a UTC time converted from the ISO local time:
2015-04-20 20:51:38
UtcMode = False and AssumeZ = True mean ISO value with no time
zone should give a local time converted from ISO UTC time:
2015-04-20 10:51:38
UtcMode = True and AssumeZ = True mean ISO value with no time
zone should give a UTC time same as the ISO UTC time:
2015-04-20 15:51:38
ISO date/time values are allowed to use 24:00 or 24:00:00 as midnight the next day. IsoToDate() handles this now.
IsoToDate()'s signed time zone offset handling was exactly backwards!
These kinds of bugs show how hard it is to develop correct software on your own. Even with test cases that cover most of the edge cases you can still miss some. And even then one set of eyeballs is never enough. This is part of the argument for open source software.
These bugs were embarassing because two of them were nothing but "dumb mistakes." The important thing is finding the errors and fixing them, and for that I thank those who PMed me with feedback.
Last edited by dilettante; Apr 23rd, 2015 at 10:02 AM.
Yes. Sure enough a string like that is an error. Examples like that were included to make sure they raised exceptions.
There isn't a whole lot more that could be done, and other methods of this class can also throw exceptions. The only alternative would be to twist them up so that every method accepted a ByRef result parameter and returned an error code result.
As I wrote at the top of the demo's Form1:
Code:
'IDE TESTING: Set "Break on unhandled errors" in Tools|Options...
' dialog's General tab!
However if you strip off the junk surrounding the valid JSON:
Code:
jQuery18308002048165113468();
... then it parses just fine.
As far as I can tell what you are getting back is JavaScript that calls into one of those flabby lazy-boy JavaScript libraries, in this case jQuery. If you need to do that then a JSON parser isn't going to help you. You'd probably have to use the Microsoft Script Control and load in a compatible version of jQuery, and then use that to evaluate your text.
but when I try and load it back into a new jsonbag via the json property, it generates an "Empty value at character 192".
Now I can copy/paste that same json into a json validator website and it seems to parse ok (e.g. http://jsonlint.com/). I am struggling to work out just why it may be failing.
Actually I worked out that the problem seemed to be that empty arrays and empty objects throw an exception.
See methods ParseArray line 664 and ParseObject line 711. For the time being I've just commented out the throwing of the exception but obviously I am unsure of the side-effects of theis change.
But if you look at the JSON specs at http://json.org/ you will see that empty arrays and objects are not legal JSON, so throwing an exception is the correct thing to do.
But if you look at the JSON specs at http://json.org/ you will see that empty arrays and objects are not legal JSON, so throwing an exception is the correct thing to do.
From what I read from the specs (and especially the flow-diagrams), empty Objects and Arrays
are definitely allowed.
Wellknown Online-Validators like http://jsonlint.com/ are useful - and there for a reason.
Here is a new update with a few small but important changes relating to /LARGEADDRESSAWARE programs and the presence of Unicode characters above &H7FFF& which were being mishandled due to the nature of VB/VBA's AscW() function. That latter could have caused false "bad character" exceptions to be raised or even crashes when parsing JSON string values.
There was also a change in the conditional code for VBA7.
The documentation (JsonBag.rtf) included has been updated with more details.
For most programs this should be a drop-in replacement for JsonBag 2.1: no changes were made to method or property signatures or usage. I suggest everyone upgrade to this version and test, test, test!
Here is a new version 2.4 that may address this problem. I don't have any VBA7 hosts (either 32- or 64-bit) right now so I can only test in VBA6. This means the problem might not be fixed since I have to make the VBA7 differences by reading documentation, guessing, and just putting them in there for others to try.
These changes should have no impact on VB6 users and probably not on VBA6 users either - at least none I have discovered.
An update to the JsonDate.cls companion class. Breaking changes, so read the "Changes" text file. Now version 2.0 but no new JsonBag.cls here.
Handles Unix timetamp values in both seconds and milliseconds. Corrects conversions to/from local time to handle DST better. This means it won't work on Windows XP and earlier dead versions of Windows... but see the comments for "fallback" code you can use if you need XP support as well.
ISS Plot Demo
This demo makes use of the super simple "Where the ISS at?" REST API web service to plot the position of the International Space Station over the Earth against a simple "equirectangular projection" map.
Much of the size of the attachment is images.
The program uses JsonBag 2.4 and JsonDate 2.0 along with WinHTTP to make a position request every 5 seconds. Since this is a free web service that doesn't even require a free account, anyone should be able to unzip and run the demo on Windows Vista SP1 or later.
With tweaks mentioned above to JsonDate.cls it should even run on Windows XP.
It's a quick and dirty demo, so there might be some errors in the offsets and arithmetic used to position the IIS icon and "drop" the red trail of breadcrumbs. But it works well enough for demo purposes.
If you are interested in this sort of thing, the API might be expanded in the future to track more orbital objects as well as the ISS. There are other similar web services from other sources for more data regarding orbiting stuff and astronomy more generally.
No logic changes of any significance. Just a change to several properties that accepted an "Optional ByVal Arg As Variant = Null" argument.
If you have any of these your Class cannot be compiled into a VB6 DLL, OCX, etc. This involves a subtle point of COM Marshalling.
The fix is to remove the default Null value and early in the procedure test for IsMissing() and if True assign Null to the Optional argument. The result should compile and work identically to the original.
How would I get "issuelinks" (an array) and then create headers from the objects "id" ,"self","type" : object+{"id","name","inward" ,"outward" "self}..., etc and then populate rows. Maybe to complex JSON?
I am trying to parse a JSON file and am getting an error at this step:
Private Sub SkipWhitespace(ByRef Text As String)
CursorIn = CursorIn + StrSpn(StrPtr(Text) + 2 * (CursorIn - 1), PtrWhiteSpace)
End Sub
VBA support isn't thoroughly tested, especially in a 64-bit VBA host. I suggest you try a newer version though, there were some VBA-related changes made in version 2.4 that might address this.
Dim JB As JsonBag
Dim F As Integer
Dim I As Integer
Dim JBText As JsonBag
Set JB = New JsonBag
F = FreeFile(0)
Open "sample.js" For Input As #F
JB.JSON = Input$(LOF(F), #F)
Close #F
Print " Single sample:fudge:"
Print , JB.Item("sample").Item("fudge")
Print
Print " Single sample:story:title:"
Print , JB.Item("sample").Item("story").Item("title")
Print
Print " List sample:story:"
With JB.Item("sample").Item("story")
For I = 1 To .Count
Print , .Item(I)
Next
End With
Print
Print " Single sample:text(1):"
Print , JB.Item("sample").Item("text")(1)
Print
Set JBText = JB.Item("sample").Item("text")
Print " List JBText:"
For I = 1 To JBText.Count
Print , JBText.Item(I)
Next
The function fails still when retriving some keys, with a error 3265 - 'Item not found in Collection'. What is odd is the that when I a follow the code, it seems to find the key and retrieve the value, but falls out of the 'Property Get' function with the 'Item not found... error or Run-time -424 - Object Required. The key is 'message' which appears to be a nested json, but I have another routine where it fails on string values.
I'm not seeing a problem. I had no trouble parsing and using your sample document.
Code:
Option Explicit
Private Sub Form_Load()
Dim JB As JsonBag
Dim F As Integer
Dim JBLogs As JsonBag
Dim LogName As String
Dim JBLog As JsonBag
Dim Row As Long
Dim Col As Long
Dim JBMessage As JsonBag
Set JB = New JsonBag
F = FreeFile(0)
Open "SD_Logs.json" For Input As #F
JB.JSON = Input$(LOF(F), #F)
Close #F
'Discard all but "logs" from here on:
Set JBLogs = JB.Item("logs")
Set JB = Nothing
Caption = "Dump of SD_Logs.json ""logs"""
With MSHFlexGrid1
.WordWrap = True
.ColWidth(0) = TextWidth("..log..")
.Rows = JBLogs.Count + 1
Set JBLog = JBLogs.Item(1)
.Cols = JBLog.Count + 1
.Row = 0
.Col = 0
.Text = "log"
For Col = 1 To .Cols - 1
.Col = Col
.Text = JBLog.Name(Col)
.ColAlignment(Col) = flexAlignLeftTop
Next
'JBLog item 4 is the very long embedded JSON "message" value:
.ColWordWrapOption(4) = flexWordBreakEllipsis
.ColWidth(4) = 4320
Set JBMessage = New JsonBag
For Row = 1 To .Rows - 1
.Row = Row
If Row > 1 Then Set JBLog = JBLogs.Item(Row)
.Col = 0
.Text = JBLogs.Name(Row)
For Col = 1 To .Cols - 1
.Col = Col
If Col = 4 Then
'Parse the embedded JSON and extract the first "error" value:
JBMessage.JSON = JBLog.Item(Col)
.Text = JBMessage.Item("errors").Item(1)
Else
.Text = CStr(JBLog.Item(Col))
End If
Next
.RowHeight(Row) = TextHeight("Wjgy") * 4
Next
End With
End Sub
Private Sub Form_Resize()
If WindowState <> vbMinimized Then
MSHFlexGrid1.Move 0, 0, ScaleWidth, ScaleHeight
End If
End Sub
Just tested your last example, and you might want to fix your "parsing-handler for numeric data" -
in the last column you convert the (quite large) integer into a double, which could lead to imprecise output or calculations
(in the RichClient I start with mapping Integer-Numbers to Longs I think, then Currencies, and then Decimals, in case
the incoming Numbers "cross" the "fitting-boundaries" of the types in question).
The Field(Values) I mean (which are mapped to a Double-Type), are the last ones in the records of the larger JSON-array:
..., "_version_":1556427511093526531}
That's a funny edge case.
The JSON spec allows integers (Number) of unlimited size, but Javascript itself treats integers that large the same as a "Double".
What do you do when it doesn't fit in a Decimal? keep it a string?
As it says in the documention (not included in the example above but in the other uploads before it):
DecimalMode As Boolean [read/write]
Defaults to False.
The setting of this property determines how numbers are parsed and serialized. See the discussion Impact Of DecimalMode Setting above.
This should only be set on a JsonBag instance that is being used as a "root" document node when it is empty.
So most JSON parsers deal with it one way or another, and that's how JsonBag does it. The default is Double which is what the spec calls for even though that is widely violated. JSON inherits the weak type system of JavaScript.
Slopping a "number" into an Integer for example can lead to unexpected overflows when somebody works with it. That's one of the reasons why trying to get clever can blow up in users' faces. Badly.
But you have to make a decision one way or the other, and that's just how the JSON game goes.
But you have to make a decision one way or the other, and that's just how the JSON game goes.
It was just a hint, to not leave anything to chance (or to some Usage-Properties, which might not be switched away from their defaults)...
In my understanding, the usage of Doubles (IEEE754) is just a suggestion (since it's a commonly available type between languages).
But VB6 *does* have support for higher precision (per Decimal-Variants) - so why not switch *automatically* to
the Dec-Types (in the Variant-holding Collection), when e.g. a large Int64 value comes along (often used in DB-Tables as Record-ID)
To detect, whether the text-representation of a number is an integer, is a no-brainer -
it can be easily determined at the parsing-stage, by checking for the local-invariant: "."
Sure, JavaScript cannot deal with the large Integer-Value (as shown in post #73),
and for example, when it's meant as an ID (e.g. to identify a Record in a DB-Table)...:
alert( JSON.stringify( JSON.parse('{"ID":1556427511093526531}') ) );
... will give the serialized output: {"ID":1556427511093526500} - which is a loss of information,
... so a potential next roundtrip back to the WebServer (and its DB) will cause errors due to not matching IDs
(e.g. when the purpose is an Update-Query, to store some piggy-back-traveling extra-JSON-fields under that ID).
Though many other languages (and JSON-Parsers) will not have any problem with such a large Integer-Value (VB6 among them) -
in the meantime JSON is not a "JavaScript-only"-thing anymore - it's a quite wide-spread serialization-format -
and the usage of RPCs is not limited to (WebBrowser+JavaScript)<->(WebServer) communication.
Thank you for all your help. My biggest challenge is my lack of experience with JSON's.
I am working my way thru each nested JSON using your example. Is there way to tell if a key exists in a JSON? For example, the message block could have messages warnings or errors, and I would want to handle the information differently.
The way Javascript and PHP deals with overly large integers - is to export them as a JSON strings.
I remember this being a fiasco when facebook went to 64bit IDs, and broke a bunch of APIs.
The best thing about this class - is it's open source. We can modify it to suit specific needs, and edge case behavior.
The way Javascript and PHP deals with overly large integers - is to export them as a JSON strings.
I remember this being a fiasco when facebook went to 64bit IDs, and broke a bunch of APIs.
Yep, and that raises the question, which toolset (or language) did produce the JSON-serialization
(containing that large Integer-Value in mjk1165's example)...
Apparently it expects a JSON-parser (on the other end), which is capable to understand
(and not mangle) that Integer-Value.
Originally Posted by DEXWERX
The best thing about this class - is it's open source. We can modify it to suit specific needs, and edge case behavior.
Well, I hope mjk1165 is capable of doing that...
As it is currently, I hope he understood from what was said, that he has to use a specific non-default-setting on diles JSON-Bag,
to not run into issues with that Integer (e.g. on a potential back-posting roundtrip with "re-serialized" content to the ServerSide).
The "automatic-fallback-mode to Decimal" I was suggesting (when Integer-Numbers get too large) would hurt nobody
(even in the Default-Setting "Parse-Numbers-into-Doubles") and would require the least efforts, when dile does it himself.
But as said, it was only a well-meant suggestion (take it or leave it)...