How to convert Json data to ADO RecordSet?
I'm learning jpbro's FastCGI Framework, and now I can get json data from my web database via FastCGI:
http://www.vbforums.com/showthread.p...FastCGI-Server
Code:
{"RecordCount":26,"Fields":[{"Name":"code","Type":"TEXT","PrimaryKey":false,"Nullable":false,"DefaultValue":"NULL"},{"Name":"value1","Type":"INTEGER","PrimaryKey":false,"Nullable":false,"DefaultValue":"NULL"},{"Name":"value2","Type":"INTEGER","PrimaryKey":false,"Nullable":false,"DefaultValue":"NULL"},{"Name":"value3","Type":"REAL","PrimaryKey":false,"Nullable":false,"DefaultValue":"NULL"}],"RowsCols":[["A",90,42,412038147449493],["B",29,79,796712756156921],["C",44,95,721228301525116],["D",48,20,432099103927612],["E",30,95,385743200778961],["F",42,98,226647019386292],["G",93,89,423794209957123],["H",57,14,821462154388428],["I",28,35,939591228961945],["J",19,11,22318971157074],["K",49,31,60527628660202],["L",43,5,291807413101196],["M",0,56,990513384342194],["N",79,82,341699719429016],["O",14,9,833484590053558],["P",82,42,324763298034668],["Q",20,77,597042381763458],["R",97,1,314293742179871],["S",38,69,903768241405487],["T",78,79,258403539657593],["U",7,91,693687498569489],["V",88,46,221721291542053],["W",64,71,198390424251556],["X",60,96,584122180938721],["Y",31,28,7.53095746040344E-02],["Z",6,70,915239691734314]]}
Now I want to convert the Json data into an ADO Recordset and load it into the FlexGrid. I wonder if there is any way to convert Json data directly to an ADO RecordSet or load Json data directly into FlexGrid?
Re: How to convert Json data to ADO RecordSet?
Quote:
Originally Posted by
dilettante
If your clients are VB6 programs then just have the server side fetch the data and send it over the wire as a persisted Recordset
I agree with Dilettante here - if your client-side data consumer is a VB6 app, then there's probably no need to go through the web server<>fastcgi<>json overhead. I say "probably" because there might be a small benefit from having a single interface to/from your data regardless of whether the consumer is a VB6 app or a web app, but I still think it is better to hand the VB6 app "unmolested" binary data, and only hand-off JSON to web apps.
I personally use the RC5 SQLite & RPC classes for moving binary recordset data over the cRecordset.Content property back and forth between VB6 clients & VB6 server apps. Regarding ADO recordsets, I'm not sure how best to handle them since I don't use them, but if there is some similarly easy way to serialize/deserialize them in a binary format, then I would recommend doing that as opposed to converting to JSON and back.
Quote:
Originally Posted by
dilettante
...and some wacky web server
Not sure what's "wacky" about Nginx... It's beating IIS on web-facing computers, and rising in terms of market share of domain names (at the expense of IIS and Apache). Quick searches show it has the biggest % of the top 1000 sites by traffic outside of Google. Anyway, that's off topic here, but if you want to continue this part of the conversation, start a new thread and we can debate there.
Re: How to convert Json data to ADO RecordSet?
Quote:
Originally Posted by
dreammanor
...I want to convert the Json data into an ADO Recordset and load it into the FlexGrid. I wonder if there is any way to convert Json data directly to an ADO RecordSet or load Json data directly into FlexGrid?
Since your JSON-serialization came from an SQLite-Recordset in the first place, you could
directly write the Binary-Content of such an SQLite-Recordset into your http-Response-Object,
by using (as mentioned by jpbro already):
- Rs.Content .... instead of
- Rs.SerializeToJSONUtf8
At the VB-Clientside, you could then "deserailize" that binary (ByteArray)-content directly into an SQLite-Rs again, by doing e.g.:
Code:
WinHttpObj.Open ...
...
WinHttpObj.Send ...
Dim Rs As cRecordset
Set Rs = New_c.Recordset
Rs.Content = WinHttpObj.ResponseBody
And as for using an SQLite-Rs as the DataSource in e.g. a VB6-HFlexGrid or VB6-DataGrid, you could do it in two ways:
(ADO-conversion-wise):
Code:
Set MSHFlexGrid1.DataSource = SQLiteRs.DataSource '<- this is using the OleDB-SimpleProvider-interface under the covers
or alternatively create a "true, free-standing" ADO-Rs from the SQLite-one by doing:
Code:
Dim AdoRs As AdoDB.Recordset
Set AdoRs = SQLiteRs.GetADORsFromContent()
Set MSHFlexGrid1.DataSource = AdoRs
HTH
Olaf
Re: How to convert Json data to ADO RecordSet?
Hi dilettante, thanks for your advice. My client-side data consumer has three kinds: vb6 desktop-app, web-app, mobile-app. So I need Json data as a unified data source. Of course, I'll try classic ASP (mainly to process ADODB data from SQL Server), I'll post a new thread to discuss this issue (classic ASP + SQLServer + ADODB).
In addition, I'm also trying to make my software step by step independent from MS (separate from the Windows platform), that is, replacing MS-SQLServer with MySQL, using Linux instead of Windows.
Re: How to convert Json data to ADO RecordSet?
Quote:
Originally Posted by
jpbro
I agree with Dilettante here - if your client-side data consumer is a VB6 app, then there's probably no need to go through the web server<>fastcgi<>json overhead. I say "probably" because there might be a small benefit from having a single interface to/from your data regardless of whether the consumer is a VB6 app or a web app, but I still think it is better to hand the VB6 app "unmolested" binary data, and only hand-off JSON to web apps.
I personally use the RC5 SQLite & RPC classes for moving binary recordset data over the cRecordset.Content property back and forth between VB6 clients & VB6 server apps. Regarding ADO recordsets, I'm not sure how best to handle them since I don't use them, but if there is some similarly easy way to serialize/deserialize them in a binary format, then I would recommend doing that as opposed to converting to JSON and back.
Hi jpbro, there are three types of client-side data consumers for me: vb6 desktop-app, web-app, mobile-app. So I need Json data as a unified data source. However, for the vb6 desktop program, the binary-content of an DB-Recordset will indeed be much faster, so I'll provide both Json and binary-content formats to my VB6 client-side data consumer.
Re: How to convert Json data to ADO RecordSet?
Quote:
Originally Posted by
Schmidt
Since your JSON-serialization came from an SQLite-Recordset in the first place, you could
directly write the Binary-Content of such an SQLite-Recordset into your http-Response-Object,
by using (as mentioned by jpbro already):
- Rs.Content .... instead of
- Rs.SerializeToJSONUtf8
At the VB-Clientside, you could then "deserailize" that binary (ByteArray)-content directly into an SQLite-Rs again, by doing e.g.:
Code:
WinHttpObj.Open ...
...
WinHttpObj.Send ...
Dim Rs As cRecordset
Set Rs = New_c.Recordset
Rs.Content = WinHttpObj.ResponseBody
And as for using an SQLite-Rs as the DataSource in e.g. a VB6-HFlexGrid or VB6-DataGrid, you could do it in two ways:
(ADO-conversion-wise):
Code:
Set MSHFlexGrid1.DataSource = SQLiteRs.DataSource '<- this is using the OleDB-SimpleProvider-interface under the covers
or alternatively create a "true, free-standing" ADO-Rs from the SQLite-one by doing:
Code:
Dim AdoRs As AdoDB.Recordset
Set AdoRs = SQLiteRs.GetADORsFromContent()
Set MSHFlexGrid1.DataSource = AdoRs
HTH
Olaf
Hi Olaf, as you and jpbro said, I've successfully loaded the binary-content returned by FastCGI into a FlexGrid. Thank you so much.
2 Attachment(s)
Re: How to convert Json data to ADO RecordSet?
If you need to do this it isn't too hard:
Code:
Public Function JsonToRecordset(ByRef JSON As String) As ADODB.Recordset
Dim jbRoot As JsonBag
Dim RS As New ADODB.Recordset
Dim FieldNames As Variant
Dim ColIndex As Long
Dim jbField As JsonBag
Dim DataType As ADODB.DataTypeEnum
Dim Values As Variant
Dim RowIndex As Long
Dim jbRow As JsonBag
Dim jbCol As JsonBag
Set JsonToRecordset = New ADODB.Recordset
With JsonToRecordset
.CursorLocation = adUseClient
Set jbRoot = New JsonBag
jbRoot.JSON = JSON
With .Fields
ReDim FieldNames(1 To jbRoot("Fields").Count)
For ColIndex = 1 To jbRoot("Fields").Count
Set jbField = jbRoot("Fields")(ColIndex)
FieldNames(ColIndex) = jbField("Name")
Select Case jbField("Type")
'No support for Single and Double types so we punt and just
'make them all Double:
Case "REAL"
DataType = adDouble
'Hazard:
'
' The crude "INTEGER" type can be anything from a Byte to a
' LongLong (64-bit integer).
'
' To cope here we'll stuff them all into adBigInt (LongLong).
' Since VB6 does not directly support LongLong you may run
' into issues you wil have to work around if values exceed
' the range of a Long.
'
'No support for strongly typed integer sizes. Just punt and
'make them all LongLong:
Case "INTEGER"
DataType = adBigInt
'Just punt and use the adLongVarWChar data type with the
'maximum length to handle the crude attempt at a "TEXT" data
'type:
Case "TEXT"
DataType = adLongVarWChar
'Probably won't happen?
'
' JSON doesn't really have a decent way to represent a BLOB,
' so if you have any BLOB data you are sort of screwed anyway.
'
'Just punt and use the adLongVarBinary data type with the
'maximum length to handle its crude attempt at a "BLOB" data
'type which you'll probably NEVER get anyway:
Case "BLOB"
DataType = adLongVarBinary
Case Else
Err.Raise 5 'Just blow up. The crude DBMS doesn't seem to
'have any more anyway.
End Select
If DataType = adLongVarWChar Or DataType = adLongVarBinary Then
.Append jbField("Name"), _
DataType, _
&H7FFFFFFF, _
IIf(jbField("Nullable"), adFldIsNullable, 0)
Else
.Append jbField("Name"), _
DataType, _
, _
IIf(jbField("Nullable"), adFldIsNullable, 0)
End If
Next
End With
.Open
For ColIndex = 1 To jbRoot("Fields").Count
Set jbField = jbRoot("Fields")(ColIndex)
If jbField("PrimaryKey") Then
.Fields(jbField("Name")).Properties("Optimize").Value = True
Exit For 'PrimaryKey, so there can only be one anyway.
End If
Next
ReDim Values(1 To UBound(FieldNames))
For RowIndex = 1 To jbRoot("RowsCols").Count
Set jbRow = jbRoot("RowsCols")(RowIndex)
For ColIndex = 1 To jbRow.Count
Values(ColIndex) = jbRow(ColIndex)
Next
.AddNew FieldNames, Values
Next
.MoveFirst
End With
End Function
Attachment 156659
Nice feature:
Aside from your chosen FlexGrid OCX there is no dependency to deploy and the entire program compiles to just a 68KB EXE.
Bonus features:
Full source and even documentation included (imagine that!) so no need to beg for code snippets and scratch your head before finally giving up. Can be customized as you choose. You can fix bugs if encountered and make changes to keep your programs running as Windows changes: future-proof.
Re: How to convert Json data to ADO RecordSet?
Quote:
Originally Posted by
dilettante
If you need to do this it isn't too hard:
Code:
Public Function JsonToRecordset(ByRef JSON As String) As ADODB.Recordset
Dim jbRoot As JsonBag
Dim RS As New ADODB.Recordset
Dim FieldNames As Variant
Dim ColIndex As Long
Dim jbField As JsonBag
Dim DataType As ADODB.DataTypeEnum
Dim Values As Variant
Dim RowIndex As Long
Dim jbRow As JsonBag
Dim jbCol As JsonBag
Set JsonToRecordset = New ADODB.Recordset
With JsonToRecordset
.CursorLocation = adUseClient
Set jbRoot = New JsonBag
jbRoot.JSON = JSON
With .Fields
ReDim FieldNames(1 To jbRoot("Fields").Count)
For ColIndex = 1 To jbRoot("Fields").Count
Set jbField = jbRoot("Fields")(ColIndex)
FieldNames(ColIndex) = jbField("Name")
Select Case jbField("Type")
'No support for Single and Double types so we punt and just
'make them all Double:
Case "REAL"
DataType = adDouble
'Hazard:
'
' The crude "INTEGER" type can be anything from a Byte to a
' LongLong (64-bit integer).
'
' To cope here we'll stuff them all into adBigInt (LongLong).
' Since VB6 does not directly support LongLong you may run
' into issues you wil have to work around if values exceed
' the range of a Long.
'
'No support for strongly typed integer sizes. Just punt and
'make them all LongLong:
Case "INTEGER"
DataType = adBigInt
'Just punt and use the adLongVarWChar data type with the
'maximum length to handle the crude attempt at a "TEXT" data
'type:
Case "TEXT"
DataType = adLongVarWChar
'Probably won't happen?
'
' JSON doesn't really have a decent way to represent a BLOB,
' so if you have any BLOB data you are sort of screwed anyway.
'
'Just punt and use the adLongVarBinary data type with the
'maximum length to handle its crude attempt at a "BLOB" data
'type which you'll probably NEVER get anyway:
Case "BLOB"
DataType = adLongVarBinary
Case Else
Err.Raise 5 'Just blow up. The crude DBMS doesn't seem to
'have any more anyway.
End Select
If DataType = adLongVarWChar Or DataType = adLongVarBinary Then
.Append jbField("Name"), _
DataType, _
&H7FFFFFFF, _
IIf(jbField("Nullable"), adFldIsNullable, 0)
Else
.Append jbField("Name"), _
DataType, _
, _
IIf(jbField("Nullable"), adFldIsNullable, 0)
End If
Next
End With
.Open
For ColIndex = 1 To jbRoot("Fields").Count
Set jbField = jbRoot("Fields")(ColIndex)
If jbField("PrimaryKey") Then
.Fields(jbField("Name")).Properties("Optimize").Value = True
Exit For 'PrimaryKey, so there can only be one anyway.
End If
Next
ReDim Values(1 To UBound(FieldNames))
For RowIndex = 1 To jbRoot("RowsCols").Count
Set jbRow = jbRoot("RowsCols")(RowIndex)
For ColIndex = 1 To jbRow.Count
Values(ColIndex) = jbRow(ColIndex)
Next
.AddNew FieldNames, Values
Next
.MoveFirst
End With
End Function
Attachment 156659
Wonderful, it is very useful to me. Thank you so much, dilettante.
Quote:
Originally Posted by
dilettante
Nice feature:
Aside from your chosen FlexGrid OCX there is no dependency to deploy and the entire program compiles to just a 68KB EXE.
Bonus features:
Full source and even documentation included (imagine that!) so no need to beg for code snippets and scratch your head before finally giving up. Can be customized as you choose. You can fix bugs if encountered and make changes to keep your programs running as Windows changes: future-proof.
dilettante, I know you have different opinions about RC5, but I really hope that one day you, Lavolpe, Krool and others will support and use RC5. Have a good weekend.
Re: How to convert Json data to ADO RecordSet?
Quote:
Originally Posted by
jpbro
but is nothing that can't be achieved by careful study of MSDN.
Hi jpbro,
I probably shouldn't jump in here, but hey ho, I'm bored. :) And jpbro, before I start, let me say I think you're a good guy (which is important), and also make valued contributions to these forums. However, that statement did strike me as odd. If we count every aspect of all the APIs available to us, having a thorough handle on the MSDN is a huge undertaking. That statement just seemed to trivialize that undertaking a bit. Maybe that wasn't your intent though. :)
I can fairly safely say that neither Dilettante nor Olaf count me as a friend. And, on too regular a basis, I find both of their personalities to be ... erm ... difficult. Having said that, I too find their programming talents quite extraordinary. In certain areas, they clearly have more knowledge that me, and they both seem willing to share (most of) that knowledge, for which I'm grateful.
From participation in these forums, one might think that there's an inverse relationship between "being nice" and "being a superior programmer". However, I adamantly disagree with that. Just as some examples, if I had to name the person whom I thought had the highest degree of VB6 programming knowledge and talent on these forums, it'd be The Trick (just MHO), and he's been absolutely nothing but completely cordial, respectful, and outright nice to me. LaVolpe, wqweto, krool, fafalone, and many others also fall into that camp (top-notch programmers who also happen to be genuinely nice guys). (And apologies if you felt your handle should have been mentioned on my list.)
I'd like to say more about jpbro's quoted statement though. If we're going to dedicate ourselves to VB6 and a Windows platform, shouldn't that be precisely what we strive for? Careful study of the MSDN, and clearly interpretable programs? Sure, some trickery with CopyMemory, thunks/assembler, COM object manipulation, and other things is sometimes cool, and I think both Dilettante and Olaf have shown well established talent in those areas.
Personally, I wish we could all just learn to be nice to each other, and to recognize that we each make our own choices, and none are blatantly (morally, ethically, or existentially) right or wrong. And each and every one of us is just trying to get through the days the best we know how.
You know? I taught for many years, all at university level, both graduate and undergraduate classes. Most of it was intro and higher level statistics, with scientific methodology occasionally thrown in. Almost without exception, I'd give this spiel about how I was going to seem really smart during the class (and I always knew my material exceedingly well, so that was true). But I went on to explain to them that I "seemed" smart because I was setting the agenda. If they were setting the agenda, they might seem smarter than me. Those same ideas hold true in these forums. We each have various areas of expertise. Sometimes one person may know the most (about a specific topic), and then, another person may know the most (about some other topic). That's life. That's the way it works. And we all need to appreciate and respect that.
Y'all Take Care,
Elroy
Re: How to convert Json data to ADO RecordSet?
Okay I've had enough of this. I deleted nearly everything out of this thread. If people continue with the personal attacks, they'll be going away for a while.
