VB6 - JsonBag, Another JSON Parser/Generator

With SOAP basically a dead duck and XML itself fading in importance, JSON is becoming more and more important as a serialization format today.

I've seen a number of VB6 JSON implementations around, including a couple posted right here in the CodeBank. Sadly none of them are very good, with little quirks and latent bugs (like improperly handling numeric data). Most of these ignore and flout the JSON standards at JSON too!

In any case I have my own implementation designed to conform as closely as possible to the standard, and now at version 1.6 it seems mature enough to share. I rewrote my notes into six pages of documentation which should make it a bit easier for others to use.


Bug reports are welcome. Though it seems to be working fine it is hard to prove code to be correct and there are probably edge cases I've never encountered.

Performance seems more than adequate for its purpose, which is mainly access to cloud computing services. Local config files are probably best still handled as plain old INI format files, though one could use JSON for that as well I suppose.


There is just one Class involved: JsonBag.cls, and as the documentation suggests it should be easy enough to import into a VBA host (Excel, etc.) as long as you tweak the API calls for 64-bit hosts when required.


The attachment includes this Class along with the documentation in RTF format, packaged up as a testbed Project JsonBagTest.vbp:

Attachment 106027

As you can see JsonBag supports a Whitespace property to format JSON for readability. By default compact JSON is generated.


Accessing the "document model" and creating JSON documents in code is easy enough. This is illustrated by a fragment from the test Project:

Code:

---

Private Sub cmdGenSerialize_Click()
    With JB
        .Clear
        .IsArray = False 'Actually the default after Clear.

        ![First] = 1
        ![Second] = Null
        With .AddNewArray("Third")
            .Item = "These"
            .Item = "Add"
            .Item = "One"
            .Item = "After"
            .Item = "The"
            .Item = "Next"
            .Item(1) = "*These*" 'Should overwrite 1st Item, without moving it.

            'Add a JSON "object" to this "array" (thus no name supplied):
            With .AddNewObject()
                .Item("A") = True
                !B = False
                !C = 3.14E+16
            End With
        End With
        With .AddNewObject("Fourth")
            .Item("Force Case") = 1 'Use quoted String form to force case of names.
            .Item("force Case") = 2
            .Item("force case") = 3

            'This syntax can be risky with case-sensitive JSON since the text is
            'treated like any other VB identifier, i.e. if such a symbol ("Force"
            'or "Case" here) is already defined in the language (VB) or in your
            'code the casing of that symbol will be enforced by the IDE:

            ![Force Case] = 666 'Should overwrite matching-case named item, which
                                'also moves it to the end.
            'Safer:
            .Item("Force Case") = 666
        End With
        'Can also use implied (default) property:
        JB("Fifth") = Null

        txtSerialized.Text = .JSON
    End With
End Sub

---

Newst version here.

Here is another demo that may be helpful in understanding how to use JsonBag.

It loads and parses a big complicated JSON document and displays it. Then it traverses and dumps the parsed JSON in the JsonBag object hierarchy. Finally it displays an item from within the hierarchy using two possible VB syntaxes.

Attachment 106173

great, works like a charm. here is an online one http://newexception.com/tools/json-validator

Quote:

---

Originally Posted by dilettante

Here is another demo that may be helpful in understanding how to use JsonBag.

It loads and parses a big complicated JSON document and displays it. Then it traverses and dumps the parsed JSON in the JsonBag object hierarchy. Finally it displays an item from within the hierarchy using two possible VB syntaxes.

Attachment 106173

---

This was really fun to watch work until it blew up with an "invalid procedure call or argument - 5" error on this line;
If IsObject(Values.Item(PrefixedKey)) Then

PrefixedKey value was; 6D0D431Eweb-app

Quote:

---

Originally Posted by y2kdeuce

This was really fun to watch work until it blew up with an "invalid procedure call or argument - 5" error on this line;
If IsObject(Values.Item(PrefixedKey)) Then

PrefixedKey value was; 6D0D431Eweb-app

---

It is worth noting that any test done within the IDE should have Break on Unhandled Errors selected, or false stops are inevitable. This of course doesn't matter in compiled programs.

Is it possible to parse json string without the recursive example? for example, how do i parse this string in a for/foreach loop

Code:

---

{
  "status": "error",
  "request": [
    "sdgsdg",
    "sgdgsdg",
  ],
  "update": [
    {
      "id": "1",
      "val": "rqrq",
    },
    {
      "id": "3",
      "val": "rqwerq",
    }
  ]
}

---

I don't see why not, but that would be an entirely different solution.

in php for the json string above, i could do

Code:

---

$decoded=json_decode($json_string);
$status=$decoded->status
$request=$decoded->request
foreach($request as $value){
echo $value
}

---

can i do the same way with jsonbag?

Ahh.

I misunderstood your question. You aren't asking about parsing at all.

Scroll down to the blue lines here:

Code:

---

Option Explicit

Private Sub Echo(ByVal Value As Variant)
    With Text1
        .SelStart = &H7FFF
        If IsNull(Value) Then
            .SelText = "#NULL"
        ElseIf IsEmpty(Value) Then
            .SelText = "#EMPTY"
        ElseIf VarType(Value) = vbObject Then
            If TypeOf Value Is JsonBag Then
                If Value.IsArray Then
                    .SelText = "#JSON array"
                Else
                    .SelText = "#JSON list"
                End If
            Else
                .SelText = "#instance of " & TypeName(Value)
            End If
        Else
            .SelText = CStr(Value)
        End If
        .SelText = vbNewLine
    End With
End Sub

Private Sub Form_Load()
    Dim F As Integer
    Dim JSON As String
    Dim JB As JsonBag
    Dim I As Long
    Dim Value As Variant

    F = FreeFile(0)
    Open "json.txt" For Input As #F
    JSON = Input$(LOF(F), #F)
    Close #F

    Set JB = New JsonBag
    JB.JSON = JSON
    Echo JB.Item("status")
    'Must enumerate JSON arrays by index, not with For...Each:
    With JB.Item("request")
        For I = 1 To .Count
            Echo .Item(I)
        Next
    End With
End Sub

---

You can use For...Each with lists however.

thank you, you are the man

I tried to check if an item exists in the above json string

Code:

---

JB.Exists("status")

---

but it return false

You used collections in the jsonbag, i suggest dictionary as they are 10x faster than collections

Quote:

---

Originally Posted by coolcurrent4u

thank you, you are the man

I tried to check if an item exists in the above json string

Code:

---

JB.Exists("status")

---

but it return false

---

Thanks, you're right. There was a bug in one line. New version attached here.

Quote:

---

Originally Posted by coolcurrent4u

You used collections in the jsonbag, i suggest dictionary as they are 10x faster than collections

---

Feel free to do so if you wish.

I considered Scripting.Dictionary and discarded the idea. They can actually be much slower than Collections for enumeration, and you have a large library loaded that you normally don't need. Normally you don't do much local batch processing of JSON documents, so performance is far more limited by network speeds than Collections.

About the only place they were faster for this purpose was doing "exists" checks so if you are doing a huge amount of that Dictionary might be worth considering. Notice that I never stumbled over the "Exists bug" in JsonBag 1.6 myself? I hardly ever use it so when I created the bug I never even knew it.

If you have to validate every chunk of JSON ever sent to your code... you're going to have a ton of validation logic in there! You'll be far better off just using error trapping.

With collections existence check performance suffers greatly when key is not present because the built-in `Err` object has to be populated with error details. I use a typelib declared `IVbCollection` interface that returns `Long` instead of `HRESULT` on `Item` property (actually method) that is 10x times faster on [non-]existence check. A performace optimized `SearchCollection` function with this typelib looks like this:

Code:

---

Public Function SearchCollection(ByVal pCol As Object, Index As Variant, Optional RetVal As Variant) As Boolean
    Const DISPID_VALUE  As Long = 0
    Dim pVbCol          As IVbCollection
   
    If pCol Is Nothing Then
        '--- do nothing
    ElseIf TypeOf pCol Is IVbCollection Then
        Set pVbCol = pCol
        SearchCollection = pVbCol.Item(Index, RetVal) = 0
    Else
        SearchCollection = DispInvoke(pCol, DISPID_VALUE, ucsIclPropGet, Result:=RetVal, Args:=Index)
        If Not SearchCollection Then
            '--- some weird collections have default (Item) method
            SearchCollection = DispInvoke(pCol, DISPID_VALUE, ucsIclMethod, Result:=RetVal, Args:=Index)
        End If
    End If
End Function

---

My tests with `Scripting.Dictionary` vs `VB.Collection` for JSON containers resulted in 10x more memory usage with `Dictionaries`. Nevertheless I prefer using `Dictionaries` for JSON containers as I can use `CompareMode` property to distinguish object vs array containers.

cheers,
</wqw>

Quote:

---

Originally Posted by dilettante

Thanks, you're right. There was a bug in one line. New version attached here.

---

In your v1.7, testing code got error:

Debug.Print JB.Item("web-app").Item("servlet")(1).Item("init-param").Item("templateProcessorClass")

Seems to be working fine here.

What does "got error" mean? What error number? Error description?

Quote:

---

Originally Posted by dilettante

Seems to be working fine here.

What does "got error" mean? What error number? Error description?

---

Runtime error '438'
Object doesn't support this property and method.

I paste your v1.7 class to your demo2.

I did that here too, but it runs with no problem. I even copied your Debug line into the program and it still works.

Can you track down which line of code fails in the class?

Quote:

---

Originally Posted by dilettante

I did that here too, but it runs with no problem. I even copied your Debug line into the program and it still works.

Can you track down which line of code fails in the class?

---

Code:

---

Private Sub Form_Load()
    Dim F As Integer
    Dim JsonData As String
    Dim JB As JsonBag
   
    GapHorizontal = lblJson.Left
    GapVertical = lblJson.Top
   
    F = FreeFile(0)
    Open "JsonSample.txt" For Input As #F
    JsonData = Input$(LOF(F), #F)
    Close #F
    txtJson.Text = JsonData

    Set JB = New JsonBag
    JB.JSON = JsonData
    Show
    JbDump "*anonymous outer JsonBag*", JB
    HomeDump
   
    Debug.Print JB.Item("web-app").Item("servlet")(1).Item("init-param").Item("templateProcessorClass")

End Sub

---

I debug into the loop, seems to fail at .Item("servlet")(1).

If you copy and pasted the code, perhaps the procedure attributes did not come across? Therefore, you would not have a default attribute of the JsonBag class (Item), which would cause the error.

The following code should fix the problem, proving the above assertion:

Debug.Print JB.Item("web-app").Item("servlet").Item(1).Item("init-param").Item("templateProcessorClass")

Instead of copying the code, you should replace the code file directly in its entirety to get all of the procedure attributes that Dilettante has defined.

Quote:

---

Originally Posted by jpbro

If you copy and pasted the code, perhaps the procedure attributes did not come across? Therefore, you would not have a default attribute of the JsonBag class (Item), which would cause the error.

The following code should fix the problem, proving the above assertion:

Debug.Print JB.Item("web-app").Item("servlet").Item(1).Item("init-param").Item("templateProcessorClass")

Instead of copying the code, you should replace the code file directly in its entirety to get all of the procedure attributes that Dilettante has defined.

---

You are right. The Item should set "Default" and the NewEnum set -4 in Procedure Attributes.

Now it is OK.

Thanks.

Thanks for tracking the issue down. I was going around in circles trying to reproduce it!

Quote:

---

Originally Posted by dilettante

Thanks for tracking the issue down. I was going around in circles trying to reproduce it!

---

I roughly go through the codes, the parser is very intelligent and a complicated process but the code is very neat.

Have you made a XML parser so that we can make XLSX or XML by codes. (Somebody told me Excel 2007's xlsx file is actually compressed XML.)

I Rated 5 stars for your excellent coding.

Thanks.

While I do have a class for processing XML it only handles a form of "simplified XML." It is useful for me for a number of purposes where I have full control over the data sent between programs, but probably is not worth making available to others.

The main reason is that Microsoft's MSXML already handles XML very well, is present in any modern version of Windows (and even older ones with IE 5.0 or leter), and is much more complete. This makes it much safer for general use because it can parse and generate very complete XML.

Cheers, glad to help! I couldn't reproduce the problem for a while here either, then I just happen to notice the word "pasted" in Jonney's comment, and got suspicious...

Update.

I hope this new version didn't create new bugs while trying to fix old things.

Raised exceptions now all use custom error numbers instead of trying to use stabdard VB error numbers. These new numbers are all in the &H800499xx range.

The .Exists(key) method should now work for noth String "names" and numeric "index" values.

.Exists() is now called within Property Get Item(key) for keys passed as String names. This may help with a confusing situation where the case-sensitive names will throw you off if you try to use the "bang" syntax (! operator)... the IDE may adjust the case of typed in names on you, leading to trouble. Before this change calling Item() with a non-existant key name resulted in a very confusing exception being raised, and hopefully now you'll see a less confusing exception instead.

It all sort of comes down to the limitations of VB's Collection class. I may be tempted to do a 2.0 rewrite at some point using Scripting.Dictionary instead.

The JsonBag.rtf document has also been updated.

Hmm... I believe the current version is not compatible with 64-bit VB/Office VBA. I tried to tweak some of the Long/LongPtr definitions, but I don't understand well how the arithmetic works. Would it be safe to just mass-replace Long with LongLong and add PtrSafe for a 64-bit version only?

Quote:

---

Originally Posted by Xpucmo

Hmm... I believe the current version is not compatible with 64-bit VB/Office VBA. I tried to tweak some of the Long/LongPtr definitions, but I don't understand well how the arithmetic works. Would it be safe to just mass-replace Long with LongLong and add PtrSafe for a 64-bit version only?

---

64-bit VBA will require some modification. I wouldn't try to blindly replace one data type with another, but such changes will be required strategically in regard to the API calls that deal with pointers.

dilettante, anyway to modify the above code to read a Json that has two delimiters? As in this thread.

I used JsonBag 1.8. Received data as JSON

Code:

---

{"_comment":"pair_id: 166, interval: 86400, candles: 120",
"candles":[[1404777600000,1963.71,0],[1404864000000,1972.83,0],[1404950400000,1964.6801,0],[1405036800000,1967.5699,0],[1405296000000,1977.1,0],[1405382400000,1973.28,0],[1405468800000,1981.5699,0],[1405555200000,1958.12,0],[1405641600000,1978.22,0],[1405900800000,1973.63,0],[1405987200000,1983.53,0],[1406073600000,1987.01,0],[1406160000000,1987.98,0],[1406246400000,1978.34,0],[1406505600000,1978.91,0],[1406592000000,1969.95,0],[1406678400000,1970.0699,0],[1406764800000,1930.67,0],[1406851200000,1925.15,0]

---

The first figure in brackets ([1404777600000,1963.71,0]) - is the date. How do I get the date in the usual format: 24/12/2014 11:00 ?
thank you.

Quote:

---

Originally Posted by Vostok

The first figure in brackets ([1404777600000,1963.71,0]) - is the date. How do I get the date in the usual format: 24/12/2014 11:00 ?

---

Well you have to remember that JSON comes from the impoverished world of "web standards" and in particular JavaScript. JavaScript has no decent native data type for date-time values so people tend to pass almost anything around.

Commonly they'll use Unix Timestamp format, though nice folks will scale (divide) that by 1000 since millisecond precision is (a.) kind of silly and (b.) just means more text to ship over the wire.

See On the nightmare that is JSON Dates and just know you are not alone.


So here is a demo based on your sample JSON fragment.

I'm pretty sure the Unix-to-Earth (i.e. VB Date) conversion works correctly though I have not throoughly tested values from #1/19/2038 3:14:08 AM# onward.

Your sample values have no time-part, so here I have skipped over the step of converting the UTC result to the local time zone but I don't think that matters for your case. Additional functions to convert to local and to UTC are included, just not used here.


The demo converts the timestamps to VB Date type values, then inserts them back as formatted String values just for visual examination. Remember, JavaScript and thus JSON are weak in this area and cannot use Date values, so only String values could be used. In a real program you'd probably just convert and use the values and not stick them back into the JsonBag as this demo does.

Attachment 122093

Quote:

---

Originally Posted by Nightwalker83

dilettante, anyway to modify the above code to read a Json that has two delimiters? As in this thread.

---

Sorry, I hadn't seen your post before today.

Well wrapping a few completed instances of his text in proper bracketing JsonBag didn't have any problem handling it:

Attachment 122097

Quote:

---

Originally Posted by dilettante

The demo converts the timestamps to VB Date type values, then inserts them back ....

---

Thank you for your response to my question. The procedure is called "test_HD_TimeFrame", module name's Download_TF. My operating system Windows XP. File is sent. Thank you.

P.S. Like everything worked as it should. I was not wrong in the code?

Code:

---

Debug.Print UnixToDate(CDec(1411430400000#))
Debug.Print ToLocal(UnixToDate(CDec(1411430400000#)))
Debug.Print ToUTC(UnixToDate(CDec(1411430400000#)))

'[1410998400000,2003.0699,2012.34,2003.0699,2011.36,0]
'23.09.2014
'23.09.2014 11:00:00
'22.09.2014 13:00:00

---

Your tests look good.

Just be wary about using Double values (such as 1411430400000#) because they have limited precision.

Decimal is safer for this, but we have no way to enter a Decimal literal in code. I used Currency (1411430400000@) because it has greater precision.

See Data Type Summary for details.

Quote:

---

Originally Posted by dilettante

... I used Currency (1411430400000@) because it has greater precision.

---

Perhaps I will follow your example and do the same code as your own. Thank you.

Major update.

Changed enumeration model, optimized parsing somewhat, minor new features, untested support for VBA7/WIN64 VBA7. See JsonBag.rtf for more information.

Very simple Excel example included, which bulks up the attachment quite a bit.

Still largely compatibile with programs using earlier versions unless they were enumerating Names.

Thanks for this. I recently discovered JsonBag and its a good fit for adding json support to some of our legacy vb6 projects. I especially like how easy it is to build the object in code, and then serialize it and send it on its way. It's also nice that there are no other dependencies outside of JsonBag.cls

Thanks!

Ron

This is a helpfuly module. Where can we get a license?

We're using ver. 1.8.j1. In order to use it, I was told by our attorney that we need to have a license (something more tangible than posting to a publicly available forum because the original author still retains the copyright).

Ok, here is a bundle of just the Class module and documentation. Versions 1.7, 1.8, and 2.0 included (there was no 1.9 and 1.6 was pretty rough).

Apache 2.0 License added. Should be liberal enough for most purposes. ;)

New version 2.1, should be compatibile with most programs written against 2.0 and remains largely compatible with those written against earlier versions.

I should probably note that the whitespace-related properties are really only useful for producing formatted "dumps" of JSON for easier development, testing, debugging, and documenting. Parsing always ignores whitespace and adding whitespace just creates larger JSON payloads so production programs shouldn't do it.

Changes for 2.1

Even though just a "point release" this version makes quite a large number of changes. However it should be highly compatible with version 2.0 and most programs should not require changes to accomodate these JsonBag changes:

• Minor optimization of Public Property Get Item (Exists() calls now create the "prefixed" keys used internally).
• Other small optimizations.
• New Clone() method returns a deep-copy of the original JsonBag.
• New CloneItem read/write Property, like Item but deep-copies instead of returning/storing the original Object reference.
• New ItemIsJSON read-only Property, used to determine whether an item is a JsonBag rather than a simple value.
• New ItemJSON read/write Property, like Item but accepts and returns JSON text for JsonBag nodes.
• Property Let Item/CloneItem will only allow VB6 Objects of type JsonBag to be assigned (i.e. this is now validated).
• Bug fix: Parsing (Property Let JSON) did not detect "arrays" and "objects" with missing values between commas.
• Bug fix: Replacing an "array" item at the end of the "array" caused "Subscript out of range" error 9.
• Bug fix: Property Let JSON did not propagate .WhitespaceNewLine to child nodes as they were inserted.
• Bug fix: Methods AddNewArray() and AddNewObject() did not propagate .DecimalMode, .Whitespace, .WhitespaceIndent, or .WhitespaceNewLine to child nodes as they were inserted.
• Bug fix: Clear method did not reset .IsArray. Now it gets cleared to the default value False. This change might be the most likely one to have impact on existing programs relying on earlier behavior.
• New conditional compilation constant: NO_DEEPCOPY_WHITESPACE. When True the .WhitespaceXXX properties are not propagated, which improves parsing performance.

Notes:

The whitespace propagation bug fixes resulted in a significant drop in parsing performance. However getting things right is probably more important. Massive JSON strings are not used very often, nor is heavy batch processing of vast numbers of smaller JSON strings. But as mentioned above in the changes list, NO_DEEPCOPY_WHITESPACE has been provided where the tradeoff in functionality for better performance makes sense.

Setting the VB6 compiler's native code optimization switches has little impact on JsonBag's parsing performance.


General:

Bug reports are always appreciated.

The new "cloning" operations would probably only be used in programs performing complex operations on JsonBags. That would be those needing to trim out parts of the tree as separate JsonBag writeable objects, copy nodes from one JsonBag to another, etc. Without cloning, making a change to such JsonBag nodes alters the single instance that multiple JsonBags would point to by Object references.

The ItemJson property is more of a convenience feature. It makes it easy to build a JsonJag partially from JSON fragements for the "constant" parts of a JSON payload being created. This could simplify client code by saving on calls to individual properties and methods just to insert such constant data. Previously a program might do quite a few calls or instead might create temporary JsonBags and assign JSON text to them via their JSON properties before adding them to the "main" JsonBag.

The attachment is bulky because of the Excel workbook included, which is just a tiny VBA usage demo.

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:

YYYY-MM-DDTHH:MM{.SS{.mmm}}{{Z}|+/-HH|+/-HHMM|+/-HH:MM}

Examples:

2015-04-20T15:51:38.001+05:01
1962-01-01T05:25Z
1962-01-01T05:25

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.

Thanks for the early feedback!

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.

Error NO: 13


Attachment 126025

Attachment 126027

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!

---

We take data from the site.
http://wmhost.ru/img/dt-EGHA.png

Query result comes in the form of text:

Code:

---

jQuery18308002048165113468({ "label" : "AN", "data" : [[1417500000000,58769,-41110,-17659],[1418104800000,65280,-45013,-20267],[1418709600000,56235,-34253,-21982],[1419314400000,61321,-39236,-22085],[1419919200000,63170,-40697,-22473],[1420524000000,72169,-48652,-23517],[1421128800000,68844,-45365,-23479],[1421733600000,70448,-46580,-23868],[1422338400000,78541,-48943,-29598],[1422943200000,85786,-56159,-29627],[1423548000000,82727,-53182,-29545],[1424152800000,82494,-53831,-28663],[1424757600000,91479,-63154,-28325],[1425362400000,85162,-61545,-23617],[1425963600000,107717,-76851,-30866],[1426568400000,56113,-28807,-27306],[1427173200000,50321,-28368,-21953],[1427778000000,45893,-24356,-21537],[1428382800000,67838,-40281,-27557],[1428987600000,71396,-42433,-28963],[1429592400000,59245,-34663,-24582],[1430197200000,44578,-27405,-17173],[1430802000000,12836,626,-13462],[1431406800000,10325,4487,-14812],[1432011600000,4058,7323,-11381],[1432616400000,11898,6422,-18320],[1433221200000,33511,-13256,-20255],[1433826000000,34010,-14027,-19983],[1434430800000,19713,-4048,-15665]] });

---

How to load Web query results in a class module JsonBag? When I load a text query in a class module error.
Thank you.

The stuff you posted is not legal JSON text.

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.

Quote:

---

Originally Posted by dilettante

The stuff you posted is not legal JSON text.
However if you strip off the junk surrounding the valid JSON:

---

It'S Great. How could I not notice? Really everything is working fine. It is enough to extract JSON

Code:

---

jQuery18308002048165113468(...JSON...);

---

Code:

---

  DATA = Split((Split(XMLHTTP.responsetext, "(")(1)), ")")(0)

---

Code:

---

{ "label" : "AN", "data" : [[1417500000000,58769,-41110,-17659],[1418104800000,65280,-45013,-20267],[1418709600000,56235,-34253,-21982],[1419314400000,61321,-39236,-22085],[1419919200000,63170,-40697,-22473],[1420524000000,72169,-48652,-23517],[1421128800000,68844,-45365,-23479],[1421733600000,70448,-46580,-23868],[1422338400000,78541,-48943,-29598],[1422943200000,85786,-56159,-29627],[1423548000000,82727,-53182,-29545],[1424152800000,82494,-53831,-28663],[1424757600000,91479,-63154,-28325],[1425362400000,85162,-61545,-23617],[1425963600000,107717,-76851,-30866],[1426568400000,56113,-28807,-27306],[1427173200000,50321,-28368,-21953],[1427778000000,45893,-24356,-21537],[1428382800000,67838,-40281,-27557],[1428987600000,71396,-42433,-28963],[1429592400000,59245,-34663,-24582],[1430197200000,44578,-27405,-17173],[1430802000000,12836,626,-13462],[1431406800000,10325,4487,-14812],[1432011600000,4058,7323,-11381],[1432616400000,11898,6422,-18320],[1433221200000,33511,-13256,-20255],[1433826000000,34010,-14027,-199
83],[1434430800000,19713,-4048,-15665],[1435035600000,24475,-9052,-15423]] }

---

Thank you very much !!! :)

Quote:

---

Originally Posted by Vostok

Code:

---

{ "label" : "AN", "data" : [[1417500000000,58769,-41110,-17659],[1418104800000,65280,-45013,-20267],[1418709600000,56235,-34253,-21982],[1419314400000,61321,-39236,-22085],[1419919200000,63170,-40697,-22473],[1420524000000,72169,-48652,-23517],[1421128800000,68844,-45365,-23479],[1421733600000,70448,-46580,-23868],[1422338400000,78541,-48943,-29598],[1422943200000,85786,-56159,-29627],[1423548000000,82727,-53182,-29545],[1424152800000,82494,-53831,-28663],[1424757600000,91479,-63154,-28325],[1425362400000,85162,-61545,-23617],[1425963600000,107717,-76851,-30866],[1426568400000,56113,-28807,-27306],[1427173200000,50321,-28368,-21953],[1427778000000,45893,-24356,-21537],[1428382800000,67838,-40281,-27557],[1428987600000,71396,-42433,-28963],[1429592400000,59245,-34663,-24582],[1430197200000,44578,-27405,-17173],[1430802000000,12836,626,-13462],[1431406800000,10325,4487,-14812],[1432011600000,4058,7323,-11381],[1432616400000,11898,6422,-18320],[1433221200000,33511,-13256,-20255],[1433826000000,34010,-14027,-199
83],[1434430800000,19713,-4048,-15665],[1435035600000,24475,-9052,-15423]] }

---

---

I can not understand. What is the function of the given search can be used to convert the date to the correct view. Help me please. Thanks.

Quote:

---

Originally Posted by Vostok

I can not understand. What is the function of the given search can be used to convert the date to the correct view. Help me please. Thanks.

---

I think I found the answer

Code:

---

  Dim JD As JsonDate, dateD as Variant
  Set JD = New JsonDate
  dateD = JD.UnixToDate(CDec(....JSON Date....)

---

No need for CDec() there. The very first thing that the UnixToDate() method does is call CDbl(UnixTimestampValue).

Also your dateD should be As Date in most cases.

Quote:

---

Originally Posted by dilettante

No need for CDec() there. The very first thing that the UnixToDate() method does is call CDbl(UnixTimestampValue).

---

I see. Thank's

Hi .. so I'm havving a play with v2.1 of JsonBag. I have an instantiated jsonbag which produces the following json.

{"Document":{"SectionSet":"True","Copy":"C:\\InfoFlex v5\\Data\\","Convert":"[Do not convert]","UseFilename":"True","UseExtension":"[Not Set]","Filename":{"SectionSet":"False","FilenameExpr":[],"CharReplacement":{"/":"",":":"","*":"","?":"","<":"",">":"","\"":"","|":"-","^":"-","&":"-","TILDE":"-","\\":"-","(Carriage Return)":"","(Line Feed)":"","(Tab)":""}}},"Replacements":{"Prefix":"","Suffix":"","Tags":[]},"Metafile":{"SectionSet":"False","Document":"","Destination":"C:\\InfoFlex v5\\Data\\","UseFilename":"Use Primary Filename","UseExtension":"[Not Set]","Filename":{"SectionSet":"False","FilenameExpr":[],"CharReplacement":{"/":"",":":"","*":"","?":"","<":"",">":"","\"":"","|":"-","^":"-","&":"-","TILDE":"-","\\":"-","(Carriage Return)":"","(Line Feed)":"","(Tab)":""}},"Encode":"[No encoding]"},"Actions":{"Perform":"[Not Set]","Action":{"SectionSet":"False","ActionConfig":{}}}}

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.

Any chance of a helping hand?

Many thx IA

S

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.

Thx

S

Glad you found it.

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.

Quote:

---

Originally Posted by dilettante

Glad you found it.

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.

Olaf

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! ;)

Quote:

---

Originally Posted by Schmidt

From what I read from the specs (and especially the flow-diagrams), empty Objects and Arrays
are definitely allowed.

---

I have looked again and discovered I was misinterpreting what I saw, so I agree. Thank you for that important feedback!

Version 2.3 makes a change to allow this, so it should be preferred over any earlier versions.

Hi!

I've just downloaded v2.3, and when I open the *.xls demo file I get a 'Type Mismatch' error in the 'PtrWhiteSpace' variable.

I am using Office 2013 64bit.

Could you take a look please?

Thanks!

Sorry for the trouble!

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.

JsonBag now has a "little brother" that is smaller and might be easier to use in VBA hosts. See: JNode - JSON revisited