Consuming RESTful services in Oracle Database Cloud Service from AngularJS

Oracle Cloud Database Schema Service (DbCS Schema) allows developers to expose RESTful (Representational State Transfer) services in JSON (JavaScript Object Notation) format from the database. This nice feature offers an open and easy way for applications constructed using different technologies to access data in DbCS Schema.

AngularJS is an open source JavaScript framework. It provides the declarative tags along with the JavaScript libraries to simplify the development of a HTML5 User Interface. AngularJS also provides a $http service that facilitates the integration of Web UI pages with backend services via a RESTful interface.

In this post, I will walk you through the steps of

1. 1) creating a simple table in DbCS Schema service. This table will be used to expose the RESTful services
2. 2) exposing RESTful/JSON services (GET, POST and PUT methods in this post) in Oracle DbCS Schema
3. 3) building an AngularJS sample page to consume these RESTful services.

The AngularJS UI pages will be deployed on Java Cloud Service - SaaS Extension.

Create a table in DbCS Schema

In order to demonstrate how to expose the RESTful services from DbCS Schema, we will create a simple table first with a pre-propulated sample data. The table creation and data insertion are provided in the following SQL script, we will call it person_ddl.sql.

 CREATE TABLE PERSON  (      LASTNAME VARCHAR2(20) NOT NULL,      FIRSTNAME VARCHAR2(20) NOT NULL,      EMAIL VARCHAR2(40),      PHONE_NUMBER VARCHAR2(20)  );  INSERT INTO PERSON(LASTNAME,FIRSTNAME,EMAIL,PHONE_NUMBER) values('Smith','Mike','mike.smith@mycompany.com','111 222 3333');

• Open the DbCS Schema console, click in "SQL Scripts" under "SQL Workshop"

• Upload and Run the person_ddl.sql

Once the table is created, we are ready to expose the RESTful services from DbCS Schema.

Expose RESTful services from DbCS Schema

Create a RESTful Service Module

• Go to SQL Workshop->RESTful Services, create a module sample.person as follows:

If you intend to deploy the AngularJS application in a different domain other than Oracle Cloud, for example, on an on-premise http server, you must enable Cross Origin Request Sharing (CORS) by entering the domain name in "Origins Allowed" field. In this post, we simply put "*" that allows Javascript applications from any domain to invoke the services in DbCS Schema.

Expose the GET method from DbCS Schema

• Under the module “sample.person, create a Template "persons/".

• Then create a GET Resource Handler under the newly created template “persons/”.

• Click “Test” button to test the GET method, you should be able to see the pre-populated record.

Expose the POST method from DbCS Schema

• Under the template “persons/”, create a POST Resource Handler.

Expose the PUT method from DbCS Schema

Under the module “sample.person, create a new template as follows. The {lastname} is used as the identifier to indicate which record needs to be updated in DbCS Schema.

• Under the newly created template “person/”, create a PUT Resource Handler.

Now we have completed exposing the RESTful services as shown in the following figure.

DbCS Schema doesn’t provide an embedded Test feature for POST and PUT methods. You are free to use your favourite REST Client such as Chrome Postman or Firefox Poster to test these two methods.

Create a HTML Page

Create a HTML with AngularJS, say DemoGet.html, in your favorite IDE to get the list of persons. The invocation to the RESTful services in DbCS Schema is very straightforward by using the AngularJS $http service with the DbCS Schema URL. Deploy the Web Application to JCS-SX.

The snippet of AngularJS code for GET mehtod:

  <script>     angular.module("myapp", []).controller("SampleController", function($scope, $http) {      $http.get('https://<your-dbcs-host>/apex/person/persons/')         .success(function(data, status, headers, config) {             $scope.persons = data;      });     });   </script> 

The snippet AngularJS code for the POST method:

      <script>          angular.module("myapp", []).controller("SampleController", function($scope, $http) {             $scope.personform = {};             $scope.personform.submitPerson = function(item, event) {                 var dataObject = {                     "lastname": $scope.personform.lastname,                     "firstname": $scope.personform.firstname,                     "email": $scope.personform.email,                     "phone_number": $scope.personform.phone_number                 };                 $http.post('https://<your-dbcs-host>/apex/person/persons/',dataObject,{})                     .success(function(data, status, headers, config) {                      alert($scope.personform.lastname+"'s form submitted!");                 });             };          });       </script>

The code for the PUT method is very similar to the one for POST. You need to pass the lastname of the person as the identifier in the URL as follows:

$http.put('https://<your-dbcs-host>/apex/person/person/smith',dataObject,{})

Conclusion

This post demonstrates how to expose RESTful services in JSON format from DbCS Schema. This feature enables the easy access to DbCS Schema from a broader set of technologies in a heterogeneous environment. AngularJS is chosen as an example to demonstrate the access to the exposed RESTful services.