Results 1 to 10 of 10

Thread: How to convert Json data to ADO RecordSet?

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    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?
    Last edited by dreammanor; Feb 22nd, 2018 at 03:04 PM.

  2. #2
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    Re: How to convert Json data to ADO RecordSet?

    Quote Originally Posted by dilettante View Post
    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 View Post
    ...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.

  3. #3
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: How to convert Json data to ADO RecordSet?

    Quote Originally Posted by dreammanor View Post
    ...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

  4. #4

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    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.
    Last edited by Shaggy Hiker; Feb 25th, 2018 at 07:30 PM. Reason: Post was removed, so quote of post was removed.

  5. #5

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: How to convert Json data to ADO RecordSet?

    Quote Originally Posted by jpbro View Post
    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.

  6. #6

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: How to convert Json data to ADO RecordSet?

    Quote Originally Posted by Schmidt View Post
    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.

  7. #7
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    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
    Name:  sshot.png
Views: 5736
Size:  2.9 KB


    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.
    Attached Files Attached Files
    Last edited by Shaggy Hiker; Feb 25th, 2018 at 07:24 PM. Reason: Rendered irrelevant

  8. #8

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: How to convert Json data to ADO RecordSet?

    Quote Originally Posted by dilettante View Post
    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
    Name:  sshot.png
Views: 5736
Size:  2.9 KB
    Wonderful, it is very useful to me. Thank you so much, dilettante.

    Quote Originally Posted by dilettante View Post
    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.
    Last edited by Shaggy Hiker; Feb 25th, 2018 at 07:31 PM.

  9. #9
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,852

    Re: How to convert Json data to ADO RecordSet?

    Quote Originally Posted by jpbro View Post
    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
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  10. #10
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    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.
    Last edited by Shaggy Hiker; Feb 25th, 2018 at 07:25 PM.
    My usual boring signature: Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width