Delete and Update are now available for CEJSON. This mans that you can now Create, Read, Update and Delete (CRUD) records in an Azure Mobile Service table using the CEJSON API as a console app on Windows Embedded Compact 2013. There is a matching console app (same source code) for the Windows desktop as well as a Universal app and a HTML app (web site project)(the latter two don't have update and delete though..yet). The projects are available as full source code so you can refactor then to suit your own purposes.

Links:

 

CEJSON Project on Codeplex: IoT with REST on Windows Embedded Compact and Azure Mobile Services

 

CEJSON Part1: Getting started

JSONParser App

The CEJson projects compile to a console application called JSONParser which has command line options. This application submits HTTP requests an Azure Mobile Service (AzMS) table and processes the response. Each request uses a specific verb depending upon the CRUD operation. In particular, if the response is a JSON string then it is parsed into an array of name-value records, as with HTML POST and GET.

CEJSON AzMS Table

The projects assume a Version 1 AzMS table which use an auto-increment integer as the primary key (id) of record and this is used for sorting based upon chronology.(ie. Time: new records for same sensor have larger id). The API can be modified to use GUIDs for the primary key as used with Version 2 AzMS tables, the current default which you get when you create an AzMS table in the Azure Portal. To create a Version 1 AzMS table refer to here (half way down the page). PS Note also that the new trial Azure Portal doesn't currently support AzMS..


The table used with CEJSON is focused upon sensor data. The fields are:

Field Type Description
id integer Created by AzMS on POST and auto-incremented
Sensor string The name of the sensor, eg Temperature1
Value integer Can be integer or float when parsed by JSONParser but currently JSONParser only submits integer values.
DateTime string

UMT Datetime auto-generated by AzMS (script API).

- A future version of the API will parse this into a datetime struct
complete boolean

Like the ToDo app is used to tag records as not being the most recent for a sensor.

On POST is always set to false.

Is used by GET from within AzMS to only return those records that are tagged as false;

 

JSONParser Command Line Options

The first parameter is the HTML verb which states what CRUD function is being performed. This is followed by parameters relevant to the verb. Note that the order is important.

CRUD HTML Verb Parameters
Create POST Sensor name and value
Read GET None, a later version will implement filters
Update

PATCH

Not PUT .. An AzMS idiosyncracy.

id, field name, field value

- field name should be Value

- the field value currently can only be an integer
Delete DELETE id

 

The app command line is:  JSONParser   {POST|GET|PATCH|PUT|DELETE}  {id}  {Sensor}  {Field Name}  {Field or Sensor Value}

Notes:

  • The app does support Patch and Put but translates PUT into PATCH
  • The command line verbs are not case sensitive
  • Only the first 3 letters of the verbs matter, you can use POS GET PAT PUT and DEL
  • Not all parameters are required for all verbs but the order as above does matter.

 

The specific command lines are:

Verb Command Line
POST JSONParser POST  <Sensor Name>  <Sensor integer value>
GET JSONParser GET
PATCH JSONParser PATCH  <id> <Field name: Should be Value> <new integer field value>
DELETE JSONParser DELETE <id>

 

 

JSONParser CRUD Primitives Limitations

Except for table specific coding (see next blog in this series .. coming) the AzMS mantra of keep it simple means there are some limitations.

Verb Command Line
POST Can only post one record at a time
GET

Can only return the whole table…
Filters can be applied but not in the current version of the API (coming).

Actually, for this API GET only returns records for which the complete field is false. This is handled the tables POST command in AzMS.

In the GET command the parameter embedded=1 is inserted
PATCH Requires an id therefore only one update at a time
DELETE Requires one id therefore only one deletion at a time

 

With these 4 primitives any database operation could be performed with further post processing. For example after performing GET, the ids could be used to individually call the app with the DELETE verb to clear the table. With filters (coming) a select could be performed follows by multiple updates or deletions.

 

Examples

Click links to see the output when the commands are run.

1. GET

2. POST

3. DELETE

4. PATCH

sss