PDA

View Full Version : OData output formats



barrymavin
2015-06-24, 13:23
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/39-coding-tips/379-part-1-working-with-odata-in-lianja-sql-server-

OData Command:


odata_read("/southwind/customers?$format=json&$top=1")


OData Output:


{"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:


odata_read("/southwind/customers?$format=jsongrid&$top=1")


OData output:


{
"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:


odata_read("/southwind/customers?$format=jsonarray&$top=1")


OData output:


[
{"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:


odata_read("/southwind/customers?$format=excel&$top=1")


OData output:


<?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:


odata_read("/southwind/customers?$format=ado&$top=1")


OData Output:


<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:


odata_read("/southwind/customers?$format=csv&$top=1")


OData output:


"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>"

alanbourke
2015-06-25, 07:40
Do you get the various formats for free or did you have to implement each yourself?

barrymavin
2015-06-25, 07:50
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.

barrymavin
2015-06-25, 10:59
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.

alanbourke
2015-06-26, 07:36
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.

hmischel@diligentsystems.com
2015-12-26, 23:53
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

barrymavin
2015-12-27, 23:47
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/39-coding-tips/379-part-1-working-with-odata-in-lianja-sql-server-
http://www.lianja.com/resources/blog/39-coding-tips/447-working-with-json-and-jql-in-lianja-v20