Hands-on IOTemp

The goal of this project is to develop an Oracle Application Express (APEX) application to control and report on a wi-fi enabled temperature sensor. It will utilize an Oracle REST Data Services (ORDS) JSON service to communicate with the device.

IOTemp Overview

We'll refer to this wi-fi device as an IOTemp. It will be able to accurately measure ambient temperature and indicate the temperature range with three colored LEDs. In an actual device, the LEDs could correspond to turning an air conditioning unit on (red for hot), off (green for saving $$) or leaving the state unchanged (yellow because we have yellow LEDs).

The IOTemp will communicate with an Oracle database via a JSON service using ORDS. It will report its ID and current temperature reading. The service will respond indicating what LED to light and how long to sleep until reporting the temperature again.

The High Temp and Low Temp set points and time to sleep are controlled via an APEX application. We will also build an APEX application to display the temperatures reported by the device.

Electric Imp

A key component to the IOTemp is an Electric Imp. The Electric Imp looks like an SD card but is actually a wifi transmitter that can run small programs and communicate with the Electric Imp servers.

For an overview of the IOTemp architecture click here.

Setting Up Your Own Electric Imp

If you've acquired your own parts or purchased a kit from us, there is information at the bottom of the next page, titled Build Your IOTemp, that will guide you in connecting your Electric Imp to your WiFi network and to your own schema.

Oracle APEX Workspace Setup

You have been pre-assigned a workspace. You will need to log in to the workspace and create the necessary database objects. Follow the steps below to import an application and install the supporting objects.

Log in to the APEX Workspace
Use the workspace, username and password provided to log in to your APEX workspace at the following URL: http://apex.oracle.com
If you are doing this on your own and not as part of one of our classes, you should use your own schema.
Download the Seeded Application

Click here to download the APEX application export with supporting objects.

Importing this application along with its supporting objects will create two tables, C2IOT_CONTROL and C2IOT_TEMP_LOG. It will seed one row of data in C2IOT_CONTROL. This table should only have one row of data. It will also create an application that allows you to change the data in this table (but not to insert or delete).
Import the seeded APEX application with Supporting Objects
Show Details

I. Navigate to the Application Builder page and click 'Import'.

II. Click the 'Browse...' or 'Choose File' button and open the file you just downloaded. Then click 'Next >'.

III. Click 'Next' on the Import Confirmation page

iV. Ensure 'Build Status' is set to 'Run and Build Application', then click 'Install Application'.

V. On the 'Supporting Objects' step, in order to create the necessary tables, ensure that 'Install Supporting Objects' is set to 'Yes'. Then click 'Next >'.

VI. Click 'Install' to confirm you wish to install the application.

VII. Click 'Run Application' to instantly run the new application.
Run the Application

Run the application and review the help associated with the items on the page.

Create JSON Service

Your Electric Imp has been programmed to call a specific JSON service which you need to build. Follow the instructions below, using the exact names below, in order to allow your device to communicate correctly.

Note: This lesson utilizes the APEX interface to create the ORDS components. Beginning with ORDS 3.0 and SQL*Developer 4.1 you have the option of creating the ORDS components via SQL*Developer instead.

Navigate to RESTful Services
Show Details

Click on the Application NNNNN in the navigation bar at the bottom of the page to get to the APEX application home page.
Select SQL Workshop

Select RESTful Services. If this is the first time using RESTful services in this workspace you may need to choose the new ORDS based Rest Service.

You may also need to register the schema with ORDS. Click the button and accept the defaults.

Create the JSON service

Click on Modules at the left. Then click the Create Module button on the right.

Enter the fields exactly as shown below.
Module Name: c2.IOTemp
Base Path: iotemp
Click the Create Module button.
Scroll down and click the Create Template button. Enter
URI Template: temp
Click the Create Template button and then the Create Handler button. Enter
Method: Post
Source Type: Pl/SQL

Copy and paste the code from the text area below in to Source.

Copy ALL of this code into Source. Use Ctl-A or Cmd-A to be sure to get all of the code.

Click the "Create Handler" button.

Add Parameters

In this step you will map the bind variables in the code above to outbound parameters. The ORDS engine will create a properly formatted JSON response based upon these parameters, adding them to the response header and body as specified. The Electric Imp can easily read the JSON response and act upon it.

At the bottom of the right panel, under Parameters, click add row to create each the parameters shown below.

Name Bind Variable Name Access Method Source Type Parameter Type comments

X-ORDS-STATUS-CODE x_status OUT HTTP Header Integer This is a special ORDS parameter that will send the HTTP Header response code. A post should get a 201 if it was successful or a 500 if there was an error.

color x_color OUT URI String

sleep_seconds x_sleep_seconds OUT URI Integer

Be sure to indicate String or Integer correctly in the Parameter Type field.
Note: The name and bind variable name can be the same. The example above was created to make it explicit how each value is being used.

Test your service

Select the link below to test your service.

test service

Your response should resemble the following:
```
{"color":"YELLOW","sleep_seconds":5}
```
This is the JSON response that your web service provided. If your response does not resemble the string above you likely have made a typo while creating the service.

If your response is similar to that above, your web service received the data, wrote it to the log table and responded with a JSON message. After you build the IOTemp, it will receive the JSON and act accordingly, lighting the appropriate light and sleeping for the indicated number of seconds.

You can use SQL Workshop to confirm that the data was stored in the table C2IOT_TEMP_LOG.

Note: if the test service above does not work for any reason (most likely that Oracle has changed the TLS certificate of apex.oracle.com), you can perform the same test using the command line utility curl.
```
curl -H "Content-Type:application/json" \
     -X POST \
     -d '{"impid":"234324","temp":65}' \
     https://apex.oracle.com/pls/apex/your_workspace_alias/iotemp/temp
```