Loading ...
Sorry, an error occurred while loading the content.

Defining a standard for Recordsets in JSON

Expand Messages
  • todd_kingham
    Hello all, I ve searched for an existing discussion on this subject and couldn t find one, so I apologize if I m duplicating an existing thread. I m quite
    Message 1 of 3 , Jun 30, 2006
    • 0 Attachment
      Hello all,

      I've searched for an existing discussion on this subject and
      couldn't find one, so I apologize if I'm duplicating an existing
      thread.

      I'm quite curious to know how other developers/programmers feel
      about defining a standard way to return recordsets, rowsets, query
      beans, query objects, etc... Whatever you want to call them, they
      appear to be a ubiquitous data-type in most programming languages
      yet they seem to be addressed quite differently among different JSON
      libraries. I'm speaking from a limited sampling of course (my own
      implementation along with the two other ColdFusion implementations
      found on json.org).

      Below are a couple of examples of the same recordset being returned
      by two different JSON libraries.

      CFJSON
      =================================================================
      {
      "RECORDCOUNT":3,
      "COLUMNLIST":"EMPLOYEEID,NAME",
      "DATA":
      {
      "EMPLOYEEID":[1,2,3],
      "NAME":["Nancy Davolio","Andrew Fuller","Janet
      Leverling"]
      }
      }




      JSON Serializer (for ColdFusion)
      =================================================================
      [
      {
      "EMPLOYEEID":1,
      "NAME":"Nancy Davolio"
      },

      {
      "EMPLOYEEID":2,
      "NAME":"Andrew Fuller"
      },

      {
      "EMPLOYEEID":3,
      "NAME":"Janet Leverling"
      }
      ]



      I can't say I've experimented with every JSON library out there but
      I'm pretty sure other libraries will return recordsets completely
      differently then the two examples listed here.

      I suppose what I'm looking for here are 3 things:
      1. What are some examples of how other JSON libraries handle
      recordsets?
      2. Does anyone else feel there is a need to come up with a
      standard / guideline for recordsets?
      3. If there does need to be a standard what are some ideas of how
      that should look?


      Thanks in advance for everyone's input.
      -Todd
    • Martin Cooper
      ... A commercial toolkit I ve used in the past uses the second form below. That works well because you can take one record and manipulate or use it without
      Message 2 of 3 , Jun 30, 2006
      • 0 Attachment
        On 6/30/06, todd_kingham <todd@...> wrote:
        >
        > Hello all,
        >
        > I've searched for an existing discussion on this subject and
        > couldn't find one, so I apologize if I'm duplicating an existing
        > thread.
        >
        > I'm quite curious to know how other developers/programmers feel
        > about defining a standard way to return recordsets, rowsets, query
        > beans, query objects, etc... Whatever you want to call them, they
        > appear to be a ubiquitous data-type in most programming languages
        > yet they seem to be addressed quite differently among different JSON
        > libraries. I'm speaking from a limited sampling of course (my own
        > implementation along with the two other ColdFusion implementations
        > found on json.org).


        A commercial toolkit I've used in the past uses the second form below. That
        works well because you can take one record and manipulate or use it without
        having to extricate it in any way from the surrounding context, as you would
        have to do in the first form below. I like having a record set be a set of
        records, instead of a funky construct that you have to fiddle with. It's
        more verbose, certainly, but unless your key names are long or you're
        transferring a great deal of data, I don't mind that, because of the clarity
        it brings to your code. (Also, the first form is more painful to create and
        update if what you started out with was a set of objects.)

        --
        Martin Cooper


        Below are a couple of examples of the same recordset being returned
        > by two different JSON libraries.
        >
        > CFJSON
        > =================================================================
        > {
        > "RECORDCOUNT":3,
        > "COLUMNLIST":"EMPLOYEEID,NAME",
        > "DATA":
        > {
        > "EMPLOYEEID":[1,2,3],
        > "NAME":["Nancy Davolio","Andrew Fuller","Janet
        > Leverling"]
        > }
        > }
        >
        >
        >
        >
        > JSON Serializer (for ColdFusion)
        > =================================================================
        > [
        > {
        > "EMPLOYEEID":1,
        > "NAME":"Nancy Davolio"
        > },
        >
        > {
        > "EMPLOYEEID":2,
        > "NAME":"Andrew Fuller"
        > },
        >
        > {
        > "EMPLOYEEID":3,
        > "NAME":"Janet Leverling"
        > }
        > ]
        >
        >
        >
        > I can't say I've experimented with every JSON library out there but
        > I'm pretty sure other libraries will return recordsets completely
        > differently then the two examples listed here.
        >
        > I suppose what I'm looking for here are 3 things:
        > 1. What are some examples of how other JSON libraries handle
        > recordsets?
        > 2. Does anyone else feel there is a need to come up with a
        > standard / guideline for recordsets?
        > 3. If there does need to be a standard what are some ideas of how
        > that should look?
        >
        >
        > Thanks in advance for everyone's input.
        > -Todd
        >
        >
        >
        >
        >
        >
        >
        >
        >
        >
        >
        >
        > Yahoo! Groups Links
        >
        >
        >
        >
        >
        >
        >
        >


        [Non-text portions of this message have been removed]
      • Greg Patnude
        ... JSON ... below. That ... without ... you would ... set of ... It s ... you re ... the clarity ... create and ... but ... I use a mechanism similar to the
        Message 3 of 3 , Jul 3, 2006
        • 0 Attachment
          --- In json@yahoogroups.com, "Martin Cooper" <mfncooper@...> wrote:
          >
          > On 6/30/06, todd_kingham <todd@...> wrote:
          > >
          > > Hello all,
          > >
          > > I've searched for an existing discussion on this subject and
          > > couldn't find one, so I apologize if I'm duplicating an existing
          > > thread.
          > >
          > > I'm quite curious to know how other developers/programmers feel
          > > about defining a standard way to return recordsets, rowsets, query
          > > beans, query objects, etc... Whatever you want to call them, they
          > > appear to be a ubiquitous data-type in most programming languages
          > > yet they seem to be addressed quite differently among different
          JSON
          > > libraries. I'm speaking from a limited sampling of course (my own
          > > implementation along with the two other ColdFusion implementations
          > > found on json.org).
          >
          >
          > A commercial toolkit I've used in the past uses the second form
          below. That
          > works well because you can take one record and manipulate or use it
          without
          > having to extricate it in any way from the surrounding context, as
          you would
          > have to do in the first form below. I like having a record set be a
          set of
          > records, instead of a funky construct that you have to fiddle with.
          It's
          > more verbose, certainly, but unless your key names are long or
          you're
          > transferring a great deal of data, I don't mind that, because of
          the clarity
          > it brings to your code. (Also, the first form is more painful to
          create and
          > update if what you started out with was a set of objects.)
          >
          > --
          > Martin Cooper
          >
          >
          > Below are a couple of examples of the same recordset being returned
          > > by two different JSON libraries.
          > >
          > > CFJSON
          > > =================================================================
          > > {
          > > "RECORDCOUNT":3,
          > > "COLUMNLIST":"EMPLOYEEID,NAME",
          > > "DATA":
          > > {
          > > "EMPLOYEEID":[1,2,3],
          > > "NAME":["Nancy Davolio","Andrew Fuller","Janet
          > > Leverling"]
          > > }
          > > }
          > >
          > >
          > >
          > >
          > > JSON Serializer (for ColdFusion)
          > > =================================================================
          > > [
          > > {
          > > "EMPLOYEEID":1,
          > > "NAME":"Nancy Davolio"
          > > },
          > >
          > > {
          > > "EMPLOYEEID":2,
          > > "NAME":"Andrew Fuller"
          > > },
          > >
          > > {
          > > "EMPLOYEEID":3,
          > > "NAME":"Janet Leverling"
          > > }
          > > ]
          > >
          > >
          > >
          > > I can't say I've experimented with every JSON library out there
          but
          > > I'm pretty sure other libraries will return recordsets completely
          > > differently then the two examples listed here.
          > >
          > > I suppose what I'm looking for here are 3 things:
          > > 1. What are some examples of how other JSON libraries handle
          > > recordsets?
          > > 2. Does anyone else feel there is a need to come up with a
          > > standard / guideline for recordsets?
          > > 3. If there does need to be a standard what are some ideas of how
          > > that should look?
          > >
          > >
          > > Thanks in advance for everyone's input.
          > > -Todd
          > >
          > >
          > >
          > >
          > >
          > >
          > >
          > >
          > >
          > >
          > >
          > >
          > > Yahoo! Groups Links
          > >
          > >
          > >
          > >
          > >
          > >
          > >
          > >
          >
          >
          > [Non-text portions of this message have been removed]
          >

          I use a mechanism similar to the second sample you've shown --
          Additionally -- I extract a schema definition from the RDBMS that
          describes the ResultSet and incorporate this into the returning
          JSONObject as a JSONArray -- The schema is useful for various
          reasons, including the ability to do typecasting and type coercion in
          the UI.

          The back-end is an HTTP Servlet written in Java that generates an
          JSONArray of objects -- where each object is a record in the
          ResultSet. The front-end is a set of custom JavaScript libraries that
          convert the result set into table rows and columns.

          I am currently working on a commercial version of this engine.


          {"PAGING":
          {"TOTAL":19104,"PAGECOUNT":764,"LIMIT":25,"PAGE":0,"OFFSET":0},"SCHEMA
          ":
          [{"udt_name":"integer","label":"ID","ordinal_position":1,"str_length":
          254,"column_name":"id","column_oid":16398,"nulls_allowed":false,"data_
          type_id":23,"table_name":"sys_cities","tbl_oid":16398,"data_type":"int
          4","lookup":false},{"udt_name":"varchar","label":"City
          Name","ordinal_position":2,"str_length":254,"column_name":"city_name",
          "column_oid":16398,"nulls_allowed":false,"data_type_id":1043,"table_na
          me":"sys_cities","tbl_oid":16398,"data_type":"varchar","lookup":false}
          ,
          {"udt_name":"boolean","label":"Status","ordinal_position":3,"str_lengt
          h":254,"column_name":"active_flag","column_oid":16398,"nulls_allowed":
          true,"data_type_id":16,"table_name":"sys_cities","tbl_oid":16398,"data
          _type":"bool","lookup":false},{"udt_name":"timestamp","label":"Create
          Date","ordinal_position":4,"str_length":254,"column_name":"create_dt",
          "column_oid":16398,"nulls_allowed":true,"data_type_id":1114,"table_nam
          e":"sys_cities","tbl_oid":16398,"data_type":"timestamp","lookup":false
          }],"RECORDSET":[{"active_flag":true,"create_dt":"2003-09-28
          20:40:09.871895","city_name":"Aaron","id":116},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.874918","city_name":"Aaronsburg Pike","id":117},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.877966","city_name":"Abac","id":118},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.880984","city_name":"Abbeville","id":119},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.88406","city_name":"Abbot Village","id":120},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.887079","city_name":"Abbotsfordville","id":121},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.893238","city_name":"Abbott Park","id":123},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.896285","city_name":"Abbottstownorty","id":124},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.890101","city_name":"Abbottsville","id":122},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.899332","city_name":"Abbyville","id":125},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.902357","city_name":"Abell","id":126},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.908427","city_name":"Aberdeen Proving Grounds","id":128},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.905404","city_name":"Aberdeenophilia","id":127},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.911449","city_name":"Abernant","id":129},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.91451","city_name":"Abernathiopely","id":130},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.917538","city_name":"Abie","id":131},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.920555","city_name":"Abilene","id":132},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.923634","city_name":"Abingdon","id":133},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.926666","city_name":"Abington","id":134},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.929687","city_name":"Abiquxct","id":135},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.932734","city_name":"Abita Springs","id":136},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.935828","city_name":"Abraham","id":137},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.938853","city_name":"Abrams","id":138},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.941871","city_name":"Absaraka","id":139},
          {"active_flag":true,"create_dt":"2003-09-28
          20:40:09.944893","city_name":"Absarokee","id":140}],"JSESSIONID":"CA9E
          8CF87EAADDB7C25ED6C163759E5E","USERNAME":"Please
          login...","AUTHENTICATED":false,"CLICKTRACK":true}


          Regards,
          Gregory P. Patnude
          Vice President – Applications & Innovations Group

          iDynaTECH, Inc
          665 North Riverpoint Blvd
          Spokane, WA 99202

          (509) 343-3104 [voice]
          (208) 691-6198 [cellular]
          http://www.idynatech.com
        Your message has been successfully submitted and would be delivered to recipients shortly.