3 Attachment(s)
[VB6] JNodeDSO: JSON via ADO and Data Binding
While going through my toolbox of old VB6 sample code I found something that I don't think I've seen anyone else publish. There may be other examples that I haven't found. In any case it might be useful enough to put out there before I "go dark" at last. As you get older and see others disappear you start to worry about such things.
This is the idea of bundling an OLE DB Simple Provider (OSP) and a Data Source Object (DSO) into one unified VB6 class that can be compiled inline into programs. No DLL or registration needed.
Writing an OSP lets you do things like create your own custom OLE DB Providers for simple data formats that don't otherwise have support already. These can be used with ADO via connection strings when compiled into a DLL and properly registered, but there are also ways to make use of a VB6 DataAdapter DSO to get around that requirement.
JNodeDSO
This is a first cut at a JSON DSO written in such a way. I based it on my simple JNode JSON class. It is written in a stripped down manner in order to provide a working example with minimal code so others can study it for writing their own custom DSOs.
If I get the time I may replace the JNode with my more robust JsonBag class. I could probably add a "default" value property to the creation schema. Then I might expand the data types to include a datetime and binary type working around JSON's limitations much as I did to support an "autonumber" type in this version. If I really get ambitious I might add features to emulate the ADO Recordset BatchUpdate facility which is very useful for "remoting" things like database access.
But all of that is less important than getting the "Inline Unified OSP/DSO Class" concept out there for others to make use of. This isn't about JNode or alternatives anyway.
I tried to deal with internationalization but this has not been thoroughly tested. For example in the user interface True and False and number formatting should be localized but not the serialized JSON. There are probably still glitches in this though.
Attachment 162565
SIMPDATA.TLB does not require deployment.
Client Form also uses MSSTDFMT.DLL and JNode also uses SCRRUN.DLL.
Individual topics like writing an OSP and working with the DataAdapter object are documented if you need more detail on them. I haven't seen anyone else unify them into one class that doesn't need to be compiled into a DLL and registered to be used though.
This can't be an original idea though. Perhaps you know of other examples of VB6 inline DSO classes?
Requirements
VB6, probably any recent version of Windows perhaps even as far back as Windows 95.
Demo
The demo program starts up and looks for a sample.json file. If there it loads it into a JNode and if not it creates a JNode in the propert format from a schema string and a file of plain text data (just for some sample rows). This JNode has an "outer envelope" of JSON wrapping the JNodeDSO's JNode structure because that is probably how most people would really use it. The stuff in the "header" node is just some made up stuff.
The "envelope" and loading/saving is really the only thing the Cient Form does with JNode. All of the Form's data access is via an ADO Recordset and databound controls.
The data is displayed using a number of bound controls: a DataGrid and three TextBoxes. There are additional buttons and controls at the bottom of the Form for testing various Recordset operations.
If the data is changed during a run it gets written out as a new sample.json file.
Pretty boring, hmm?
The column name "F3" may look like an odd choice, but it is the result of a test of defaulting in the JNodeDSO creation schema. Maybe I did get more elaborate than I had intended? See the comments at the head of JNodeDSO.cls for more info on the creation schema syntax. Come to think of it "Up" is a weird name too, but I ran out of inspiration for a name for a Boolean column.
Sample JSON Snippet
Here are the first few lines of the JSON written to disk after making a few data changes. The demo writes it out "pretty-printed" with whitespace for easy examination but that's optional and it could write compacted JSON just as easily. See the comments in Form_Unload.
Code:
{
"header": {
"type": "rowset",
"to": "server"
},
"data": {
"schema": [
{
"name": "ID",
"type": "autonumber",
"nulls": false,
"write": false
},
{
"name": "Number",
"type": "number",
"nulls": true,
"write": true
},
{
"name": "F3",
"type": "string",
"nulls": true,
"write": true
},
{
"name": "Up",
"type": "boolean",
"nulls": true,
"write": true
}
],
"rows": [
[
1,
20,
"\"Toy\" Story (1995)",
true
],
[
2,
17.5,
"Jumanji (1995)",
false
],
[
3,
28.75,
"Grumpier Old Men (1995)",
true
],
[
4,
30,
"Waiting to Exhale (1995)",
false
],
[
5,
42.5,
"Father of the Bride Part II (1995)",
true
],
[
6,
13.7513678,
"Heat (1995)",
false
],
Re: [VB6] JNodeDSO: JSON via ADO and Data Binding
I was asked to take a look at a bug. Found it, though I have no idea how it got through testing and into the posted code.
Easy fix so I won't re-port. Just make the change in RED below to the hacked JNode.cls I used for this initial project:
Code:
Public Function RemoveFromArrayForN(ByVal Index As Long, ByVal N As Long) As Long
'Returns actual deleted count.
Dim Delete As Long
Dim NewUbound As Long
If IsObject Then
Err.Raise 5
Else
If Index + N - 1 > UBound(Members) Then
'Delete reaches or exceeds RowCount:
RemoveFromArrayForN = UBound(Members) - Index + 1
Else
'Move rows beyond the delete "up":
For Delete = Index + N To UBound(Members)
Set Members(Delete - N) = Members(Delete)
Next
RemoveFromArrayForN = N
End If
NewUbound = UBound(Members) - RemoveFromArrayForN
If NewUbound >= 0 Then
ReDim Preserve Members(NewUbound)
Else
Members = Array()
End If
End If
End Function
I.e. just add the Set here:
Code:
Set Members(Delete - N) = Members(Delete)
