Working with OData in Lianja Cloud Server

Print

Lianja Cloud Server supports OData-compatible data access. The Server handles ODBC connections as well as HTTP requests using OData URIs. In this article I will show you how to use Lianja Cloud Server with OData URIs that will allow you to perform CRUD (Create, Read, Update and Delete) operations on your data.

Making OData requests from jQuery and/or the Lianja HTML5 Client in a Web App is extremely straightforward and simple. 

Unlike other OData implementations, Lianja Cloud Server OData services does not require any server-side configuration of web services.

So let's get started and see what we can do with this new OData functionality.

When testing your OData queries I find it useful to be able to just type the URIs in the address bar of Google Chrome. Any JSON data returned from the OData request is displayed in the browser tab (unlike IE which prompts you to save the JSON file into downloads).

First thing we need to do is make sure that we have enabled HTTP services and OData Read Services through the Lianja Server Manager which you will find in the Control Panel on Windows.

Screen_Shot_2013-08-14_at_6.32.52_PM.png

 

Screen_Shot_2013-08-14_at_2.13.48_PM.png

Now fire up Chrome on your desktop and type the following into the address bar.

http://127.0.0.1/odata/southwind/customers

You should now see JSON format output in the browser window showing all the "customer" records from the "southwind" database.

Screen_Shot_2013-08-14_at_2.21.31_PM.png 

Now let's just select the top 5 records and also only request the customerid and contactname columns using the $top and $select arguments.

Screen_Shot_2013-08-14_at_2.23.19_PM.png

We can filter what data is returned using the $filter argument. Note that in OData all arguments begin with a $ and are concatenated together using & just like a normal web page URI.

 Screen_Shot_2013-08-14_at_2.53.14_PM.png

Notice that when we specify conditions e.g. for the $filter in this case we must specify these in OData-style:

eq 
< lt 
<= le
> gt
>= ge
!= ne
and and
or or
+ add
* mul
/ div
% mod
- sub

The Lianja SQL Server will generate SQL behind the scenes and optimize the query based on what indexes exist on the specified table.

We can speed things up by specifying a "value selector" on the table.

/odata/southwind/customers('A*')

The OData server will use heuristics to determine the column that 'A*' is referring to in the 'customers' table. If the column customersid exists it will be used, if that does not exist and the table is a 'collection' i.e. ends with an 's', the 's' will be removed and the column customerid will be looked up.

In many cases this will just work but there are occasions where for example you may want to select all customers where the 'contactname' starts with 'A'. This is accomplished in the following manner.

/odata/southwind/customers('A*', contactname)

There are a range of arguments that can be postfixed onto the URI like this.

/odata/southwind/customers('A*')?$skip=10&$top=5

The $nostrcompare argument can also be used to disable exact matching:

/odata/southwind/customers('A')?$nostrcompare&$skip=10&$top=5

The following arguments are available.

$top Selects the specified number of records  $top=10
$skip Skips a number of selected records   $skip=50
$filter Selects only those records that match the specified filter  $filter=contactname eq 'A' and amount gt 0
$select Selects only the specified columns (or expressions)  $select=customerid,contactname
$rowid Add the unique rowid into the output ( __rowid ) which can be used to update data later  $rowid 
$count Don't return any data just tell me how many records would have been selected  $count
$metadata Returns metadata describing the columns in the table; name, type, width and decimals.  $metadata
$format The output format  $format=json
 $format=jsongrid
 $format=jsonarray
 $format=ado
 $format=excel
 $format=attachment
 $format=html
 $format=csv
 $format=img
$orderby Specify a column or expression that the data should be ordered by  $orderby=contactname
$callback Specify a callback function that will wrap the data returned. This is used with JSONP calls to bypass SOP issues in the browser  $callback=your_javascript_function 
$sql Evaluate a SQL SELECT statement directly and return the results. Note that only the database name is required after e.g. /odata/southwind?$sql=...  $sql=select * from customers
$nocount Don't return the __count member which details the total number of rows that match the query. This improves performance on Virtual Tables  $nocount
$nostrcompare Disable exact matching. Comparisons are carried out up to the length of the left-hand expression, e.g. 'A' = 'ALFKI'.  $nostrcompare

To query for the metadata for a table use the $metadata argument.

Screen_Shot_2013-08-15_at_7.00.47_AM.png