Example: a REST service for retrieving data (SELECT)

Previous Next

The following step-by-step instructions are for creating a custom REST service that retrieves data on the basis of a booking ID.

This example shows how you can quickly generate a REST service from a SQL statement.

In this particular example, matrix parameters are used, but you have a number of other options when designing input parameters.

 

 

collapseDefining SQL for data presentation

Often the data structure you want to present through a REST service is not exactly the same as the table structure (the data model) in which you store the data in the database. You want to re-structure the data for presentation through the REST service.

The Service Definer lets you create SQL statements "on top of" the data model. These statements tailor data structure to the requirements of your REST service.

An elegant way is to work with 2 layers. First create a Logical View in USoft Definer that formats the data in functional terms to the required structure. Then, in USoft Service Definer, write your REST service functionality against this Logical View. The functional Logical View becomes available for use everywhere in your application. Only the more technical service aspect is associated with the REST service itself.

This 2-layer strategy is demonstrated in the following steps.

1.In USoft Definer, create a Logical View that selects a specific subset of columns and records that you want to present through the REST service.
For example, the following view statement presents certain properties of active bookings in a travel agency:

SELECT   Reservation.res_id

,        Reservation.schedtour_id

,        "Scheduled tour".destination

,        "Scheduled tour".tour_type

,        "Scheduled tour".start_date

,        "Scheduled tour".return_date

,        Reservation.num_part

FROM     Reservation Reservation

,        Schedtour "Scheduled tour"

RELATE   "Scheduled tour" "FOR WHICH ARE MADE" Reservation

WHERE    "Scheduled tour".start_date > TRANSACTION_DATE()

AND      Reservation.cancel_date IS NULL

 

2.Have the Logical View checked (press the Check button), then save your work. See the Definer Help for details on this step.
3.In USoft Service Definer, if you have an existing connection you want to use, re-start the Rules Service of that connection. Otherwise, in USoft Service Definer, make sure you define a Rules Service, the Application, and a Connection, and make sure you start the Rules Service.
4.In USoft Service Definer, choose Define, SQL Statements and add a statement with a variable element corresponding to input to be supplied by the REST service client, signified by a colon (:) followed by a string. In this case, this input is a booking ID:
Statement Name: GET_ACTIVE_BOOKING
Statement:

SELECT   res_id

,        schedtour_id

,        destination

,        tour_type

,        start_date

,        return_date

,        num_part

FROM     active_booking

WHERE    res_id = :booking_id

 

5.Save. Click the Check button. See that the Correct flag is set to Yes.
collapseGenerating a REST method based on SQL
1.Define a custom REST service and make sure that this new service is exposed by a Server. For details on how to do these 2 things, step out to help topic " Setting up your own REST service ".
2.On the Methods tab, create a record for the retrieval method:

•Method Name = GetActiveBooking

•Relative URI Path = /ActiveBookings

•REST HTTP Verb = GET

3.Press the "Generate from SQL" button.

The "Generate Method from SQL Statement" appears.

4.In the "Return Structure Name" field, enter MyBookingStructure.
5.On the SQL Statements tab, click in the record for the SQL statement that you want the REST method to be generated from.

The SQL code is now in view in the box on the right. Check that the Correct flag is set to Yes.

6.Press the Generate button.

In the REST Services window or tab, values for Signature, Implementation and Parameters are generated.

In the Catalog on the left-hand side, a Structure with name MyBookingStructure is offered for inspection.
collapsePublishing the solution
1.Choose Define, Servers, and retrieve the record of the Server where you expose the service.
2.With input focus on this record, choose Publish, Publish and Restart from the menu.
collapseTesting the implementation
1.Open a Google Chrome browser window, search the download site for the “POSTMAN – REST Client” Google extension, and download it to your browser.
Once installed, this extension can be opened easily from a new Google Chrome browser tab. To do this, click the standard “Apps” button on the Favorites bar, then click the POSTMAN item in the middle of the screen.
2.Open POSTMAN, and specify the following URL:

http://<host-name>:<port-number>/<service-uri>/<method-uri>;<variable><operator><value>[;<variable><operator><value>...]

 

for example:

http://localhost:8090/mycustomservice/ActiveBookings;booking_id=182

 

Here, 'reservation_id' is a matrix parameter. See the help topic " Input Parameters as Matrix Parameters " for details.
3.Check that your request is about to use the GET protocol (this is the default, displayed to the right of the URL).
4.Press the Send button.
See that booking information is retrieved and displayed in the lower pane.

 

 

See Also

Generating REST Services from SQL

Example: a REST Service for adding new data (INSERT)

Example: a REST Service for manipulating data (UPDATE)

Example: a REST Service for returning manipulated data

Example: a REST Service for deleting data (DELETE)