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

Re: [json] Defining a standard for Recordsets in JSON

Expand Messages
  • 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 1 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 2 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.