Results 1 to 7 of 7

Thread: OData output formats

  1. #1
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,246

    OData output formats

    As you should probably all realize by now the Lianja Cloud Server is a very functional and scaleable means of performing CRUD operations on data in any type of database.

    If you build Web and/or Mobile Apps using Lianja best practices (Pages, Sections, Fields and Gadgets) all of the heavy lifting for adding, updating, deleting and querying data is done for you. Lianja uses JSON extensively for this purpose.

    There can be no doubt that JSON is the modern and most commonly used data interchange format for Web and Mobile apps.

    Lianja Cloud Server has built in support for OData. This provides a wide range of data formats that can output based on a very simple and easy REST API.

    Let's take a look at some of these output formats.

    For the purposes of these examples I will be using the OData_Read() function from the Lianja App Builder console but the same REST URI can be types into the browser address bar (Google Chrome recommended as it does not prompt you to download the results of the query). If you are typing these REST URIs into the browser address bar prefix /odata/ to them.

    In the following examples I have added $top=1 to select only the first record. OData handles a wide range of parameters such as $filter, $orderby etc. See the following link for details.

    http://www.lianja.com/resources/blog...ja-sql-server-

    OData Command:
    Code:
    odata_read("/southwind/customers?$format=json&$top=1")
    OData Output:
    Code:
    {"d" : {
    	"results": [
    		{"customerid":"ALFKI","contactname":"Maria Anders","companyname":"Alfreds Futterkiste","contacttitle":"Sales Representative","address":"21 State Street","region":"East","city":"Boston","postalcode":"01943","country":"USA","phone":"617-342-8976","fax":"617-567-9876","notes":"<p>The last time this customer was contacted she was going on vacation.</p>"}
    	],
    	"__database": "southwind",
    	"__table": "customers",
    	"__primarykey": "",
    	"__tabletype": "table",
    	"__datatypes": "CCCCCCCCCCCM",
    	"__href": "/odata/southwind/customers?$format=json&$top=1",
    	"__querytime": "18ms",
    	"__count": 91,
    	"__page": 1
    }}
    OData Command:
    Code:
    odata_read("/southwind/customers?$format=jsongrid&$top=1")
    OData output:
    Code:
    {
    	"rows": [
    		{"customerid":"ALFKI","contactname":"Maria Anders","companyname":"Alfreds Futterkiste","contacttitle":"Sales Representative","address":"21 State Street","region":"East","city":"Boston","postalcode":"01943","country":"USA","phone":"617-342-8976","fax":"617-567-9876","notes":"<p>The last time this customer was contacted she was going on vacation.</p>","__rowid":1}
    	],
    	"database": "southwind",
    	"table": "customers",
    	"__tabletype": "table",
    	"__primarykey": "",
    	"__datatypes": "CCCCCCCCCCCMN",
    	"__href": "/odata/southwind/customers?$format=jsongrid&$top=1",
    	"__querytime": "13ms",
    	"total": 91,
    	"page": 1
    }
    OData Command:
    Code:
    odata_read("/southwind/customers?$format=jsonarray&$top=1")
    OData output:
    Code:
    [
    	{"customerid":"ALFKI","contactname":"Maria Anders","companyname":"Alfreds Futterkiste","contacttitle":"Sales Representative","address":"21 State Street","region":"East","city":"Boston","postalcode":"01943","country":"USA","phone":"617-342-8976","fax":"617-567-9876","notes":"<p>The last time this customer was contacted she was going on vacation.</p>"}
    ]
    OData Command:
    Code:
    odata_read("/southwind/customers?$format=excel&$top=1")
    OData output:
    Code:
    <?xml version="1.0"?>
    <?mso-application progid="Excel.Sheet"?>
    <Workbook 
       xmlns="urn:schemas-microsoft-com:office:spreadsheet"
       xmlns:o="urn:schemas-microsoft-com:office:office"
       xmlns:x="urn:schemas-microsoft-com:office:excel"
       xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
       xmlns:html="http://www.w3.org/TR/REC-html40">
       <Worksheet ss:Name="Sheet1">
    <ss:Table ss:ExpandedColumnCount="12" ss:ExpandedRowCount="92" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">
    <Row>
    <Cell><Data ss:Type="String">CUSTOMERID</Data></Cell>
    <Cell><Data ss:Type="String">CONTACTNAME</Data></Cell>
    <Cell><Data ss:Type="String">COMPANYNAME</Data></Cell>
    <Cell><Data ss:Type="String">CONTACTTITLE</Data></Cell>
    <Cell><Data ss:Type="String">ADDRESS</Data></Cell>
    <Cell><Data ss:Type="String">REGION</Data></Cell>
    <Cell><Data ss:Type="String">CITY</Data></Cell>
    <Cell><Data ss:Type="String">POSTALCODE</Data></Cell>
    <Cell><Data ss:Type="String">COUNTRY</Data></Cell>
    <Cell><Data ss:Type="String">PHONE</Data></Cell>
    <Cell><Data ss:Type="String">FAX</Data></Cell>
    <Cell><Data ss:Type="String">NOTES</Data></Cell>
    </Row><ss:Row><ss:Cell><ss:Data ss:Type="String">ALFKI</ss:Data></ss:Cell><ss:Cell><ss:Data ss:Type="String">Maria Anders</ss:Data></ss:Cell><ss:Cell><ss:Data ss:Type="String">Alfreds Futterkiste</ss:Data></ss:Cell><ss:Cell><ss:Data ss:Type="String">Sales Representative</ss:Data></ss:Cell><ss:Cell><ss:Data ss:Type="String">21 State Street</ss:Data></ss:Cell><ss:Cell><ss:Data ss:Type="String">East</ss:Data></ss:Cell><ss:Cell><ss:Data ss:Type="String">Boston</ss:Data></ss:Cell><ss:Cell><ss:Data ss:Type="String">01943</ss:Data></ss:Cell><ss:Cell><ss:Data ss:Type="String">USA</ss:Data></ss:Cell><ss:Cell><ss:Data ss:Type="String">617-342-8976</ss:Data></ss:Cell><ss:Cell><ss:Data ss:Type="String">617-567-9876</ss:Data></ss:Cell><ss:Cell><ss:Data ss:Type="String"><p>The last time this customer was contacted she was going on vacation.</p></ss:Data></ss:Cell></ss:Row>
    </ss:Table>
    </ss:Worksheet>
    </ss:Workbook>
    OData Command:
    Code:
    odata_read("/southwind/customers?$format=ado&$top=1")
    OData Output:
    Code:
    <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
         xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
         xmlns:rs="urn:schemas-microsoft-com:rowset"
         xmlns:z="#RowsetSchema">
      <s:Schema id='RowsetSchema'>
        <s:ElementType name='row' content='eltOnly' rs:updatable='true'>
          <s:AttributeType name='CUSTOMERID' rs:number='1' rs:nullable='true' rs:write='true' rs:basetable='_ODATA' rs:basecolumn='CUSTOMERID'>
          <s:datatype dt:type='string' rs:dbtype='str'  dt:maxLength='5' rs:fixedlength='true' />
          </s:AttributeType>
          <s:AttributeType name='CONTACTNAME' rs:number='2' rs:nullable='true' rs:write='true' rs:basetable='_ODATA' rs:basecolumn='CONTACTNAME'>
          <s:datatype dt:type='string' rs:dbtype='str'  dt:maxLength='30' rs:fixedlength='true' />
          </s:AttributeType>
          <s:AttributeType name='COMPANYNAME' rs:number='3' rs:nullable='true' rs:write='true' rs:basetable='_ODATA' rs:basecolumn='COMPANYNAME'>
          <s:datatype dt:type='string' rs:dbtype='str'  dt:maxLength='40' rs:fixedlength='true' />
          </s:AttributeType>
          <s:AttributeType name='CONTACTTITLE' rs:number='4' rs:nullable='true' rs:write='true' rs:basetable='_ODATA' rs:basecolumn='CONTACTTITLE'>
          <s:datatype dt:type='string' rs:dbtype='str'  dt:maxLength='30' rs:fixedlength='true' />
          </s:AttributeType>
          <s:AttributeType name='ADDRESS' rs:number='5' rs:nullable='true' rs:write='true' rs:basetable='_ODATA' rs:basecolumn='ADDRESS'>
          <s:datatype dt:type='string' rs:dbtype='str'  dt:maxLength='60' rs:fixedlength='true' />
          </s:AttributeType>
          <s:AttributeType name='REGION' rs:number='6' rs:nullable='true' rs:write='true' rs:basetable='_ODATA' rs:basecolumn='REGION'>
          <s:datatype dt:type='string' rs:dbtype='str'  dt:maxLength='15' rs:fixedlength='true' />
          </s:AttributeType>
          <s:AttributeType name='CITY' rs:number='7' rs:nullable='true' rs:write='true' rs:basetable='_ODATA' rs:basecolumn='CITY'>
          <s:datatype dt:type='string' rs:dbtype='str'  dt:maxLength='15' rs:fixedlength='true' />
          </s:AttributeType>
          <s:AttributeType name='POSTALCODE' rs:number='8' rs:nullable='true' rs:write='true' rs:basetable='_ODATA' rs:basecolumn='POSTALCODE'>
          <s:datatype dt:type='string' rs:dbtype='str'  dt:maxLength='10' rs:fixedlength='true' />
          </s:AttributeType>
          <s:AttributeType name='COUNTRY' rs:number='9' rs:nullable='true' rs:write='true' rs:basetable='_ODATA' rs:basecolumn='COUNTRY'>
          <s:datatype dt:type='string' rs:dbtype='str'  dt:maxLength='15' rs:fixedlength='true' />
          </s:AttributeType>
          <s:AttributeType name='PHONE' rs:number='10' rs:nullable='true' rs:write='true' rs:basetable='_ODATA' rs:basecolumn='PHONE'>
          <s:datatype dt:type='string' rs:dbtype='str'  dt:maxLength='24' rs:fixedlength='true' />
          </s:AttributeType>
          <s:AttributeType name='FAX' rs:number='11' rs:nullable='true' rs:write='true' rs:basetable='_ODATA' rs:basecolumn='FAX'>
          <s:datatype dt:type='string' rs:dbtype='str'  dt:maxLength='24' rs:fixedlength='true' />
          </s:AttributeType>
          <s:AttributeType name='NOTES' rs:number='12' rs:nullable='true' rs:write='true' rs:basetable='_ODATA' rs:basecolumn='NOTES'>
          <s:datatype dt:type='Memo' rs:dbtype='Memo'  dt:maxLength='8' rs:fixedlength='true' />
          </s:AttributeType>
        </s:ElementType>
      </s:Schema>
      <rs:data>
       <z:row   CUSTOMERID = 'ALFKI'  CONTACTNAME = 'Maria Anders'  COMPANYNAME = 'Alfreds Futterkiste'  CONTACTTITLE = 'Sales Representative'  ADDRESS = '21 State Street'  REGION = 'East'  CITY = 'Boston'  POSTALCODE = '01943'  COUNTRY = 'USA'  PHONE = '617-342-8976'  FAX = '617-567-9876'  NOTES = '<p>The last time this customer was contacted she was going on vacation.</p>'   />
     </rs:data>
    </xml>
    OData Command:
    Code:
    odata_read("/southwind/customers?$format=csv&$top=1")
    OData output:
    Code:
    "customerid","contactname","companyname","contacttitle","address","region","city","postalcode","country","phone","fax","notes"
    "ALFKI","Maria Anders","Alfreds Futterkiste","Sales Representative","21 State Street","East","Boston","01943","USA","617-342-8976","617-567-9876","<p>The last time this customer was contacted she was going on vacation.</p>"
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  2. #2
    Member
    Join Date
    May 2012
    Posts
    37
    Do you get the various formats for free or did you have to implement each yourself?

  3. #3
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,246
    Hi Alan,

    This is all built-in. Nothing to write, just reference the REST URIs and the data will be returned in the format you requested.

    The odata REST API works against any type of database.

    The examples I gave are just for querying but the cloud server handles full CRUD through the REST API. I will be posting examples of how to perform inserts, updates and deletes also.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  4. #4
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,246
    Maybe I have misunderstood your question, yes the formats were all implemented ourselves.

    They match the requirements of various web frameworks including the LianjaWebFramework.

    The Lianja Cloud Server is not solely for use by Lianja Web and Mobile Apps. It can be used to serve up OData data, web services (written in Lianja/VFP or JavaScript) and dynamic HTML5/JavaScript using Lianja/VFP Server Pages (.rsp pages) and/or JavaScript Server Pages (.jssp pages). So it can be used as a web/mobile application server for other web development tools and even .net / Java if required.
    Last edited by barrymavin; 2015-06-25 at 10:06.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  5. #5
    Member
    Join Date
    May 2012
    Posts
    37
    Quote Originally Posted by barrymavin View Post
    Maybe I have misunderstood your question, yes the formats were all implemented ourselves.
    Sorry that's what I meant, yes. I like OData, so much less pain than the likes of SOAP.

  6. #6
    Senior Member
    Join Date
    Feb 2012
    Posts
    1,239
    Hi Barry,

    I was wondering if you could put together a quick demo on reading in the OData call in an rsp page.
    Something simple like stepping through the result of http://127.0.0.1:8001/odata/southwind/customers.


    Thanks.

    Herb

  7. #7
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,246
    Hi Herb,

    Yes I will include an example app when I'm back from my holidays.

    In in the meanwhile you can peruse these articles:
    http://www.lianja.com/resources/blog...ja-sql-server-
    http://www.lianja.com/resources/blog...-in-lianja-v20
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Journey into the Cloud
Join us