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

Re: Defining a standard for Recordsets in JSON

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