Enhanced OData support in SQL Anywhere 16.0.1823

Introduction

With the release of SQL Anywhere 16.0 in March of 2013, SAP and Sybase iAnywhere introduced support for the OData protocol.  This allowed for easy web services access to database resources, without having to create additional code assets or database objects.  I blogged about it here.  That blog covered the basics of the new OData Server architecture, and gave step-by-step instructions on how to use the OData samples that ship with SA16.

This blog post will cover a specific set of new features and functions that were just released into build 1823, the most recent maintenance release for SQL Anywhere 16.  Licensed customers of SQL Anywhere 16 can download build 1823 (EBF #22519) from either the SAP Support Portal or the Sybase Download Center.

Background

The initial release of the SA16 OData Server was very easy to setup and use, but it had some basic default behaviors that limited its functionality.

1. If a table had a defined primary key, it could automatically be exposed as an OData entity.  But this default behavior would expose every column in the table.  There was no easy way to expose a subset of the columns of a table.
2. OData entity names were derived from the matching table and view names. There was no way to use a different name for the OData entity.
3. Views could only be exposed as OData entities by listing them in an .OSDL configuration file.  Listing a view also required you to select one or more columns that would serve as the view’s “primary key” and list these in the keys() clause.
4. If the database had a defined foreign key between two tables, then SA16 would automatically create an OData “association” between the two entities.  There was no facility to create associations between two tables or views that did not have a defined foreign key.
5. The column(s) that comprised an association between two entities did not appear in the dependent entity’s definition.  In other words, if Orders and Order_Items were associated on the “order_id” column, then “order_id” would not appear as a property in the “Order_Items” entity definition, even though it is a column in the Order_Items table.
6. There was no facility to invoke database stored procedures that took input parameters.  Procedure that had no parameters could be exposed as OData entities by wrapping them as a view and exposing the view in the .OSDL file.

The good news is, these issues have all been resolved in build 1823.  To illustrate these new features, I’ll use the Northwind sample database that I created in this blog post.  To follow along, please review that post and download the attached .ZIP file.

OSDL file changes

The .OSDL file language now has three new statement types:  ENTITY, ASSOCIATION, and SERVICEOP.  We’ll cover each of these separately.

ENTITY definition

ASSOCIATION definition

Creates an association between entities, including complex associations that use an underlying association table.  These are only required when a physical foreign key definition does not exist between the two database tables.

Syntax

    association “association-name”

                  principal “principal-entityset-name” (“column-name” [, …]) multiplicity “[ 1 | 0…1 | 1..* | * ]“

                dependent “dependent-entityset-name” (“column-name” [, …]) multiplicity “[ 1 | 0..1 | 1..* | *]“

                  over “owner”.”object-name” principal (“column-name” [, …]) dependent (“column-name” [, …])

• association-name  The name for the association – must be unique within the namespace.  This name is referenced in the Entity navigates clause.
• principal  The name provided here is the “parent” side of the association, and is referenced in the Entity navigates clause as the navprop-name property.
• dependent  The name provided here is the “child” side of the association.
• multiplicity  “1” = mandatory 1:1 relationship;  “0..1” = optional 1:1 relationship; “1..*” = mandatory 1:m relationship; “*” = optional 1:m relationship
• over  This clause is only used when a physical database table is used as an associative table, and contains the primary keys of both the principal and dependent entities. 

SERVICEOP definition

Exposes a database stored procedure or function as an HTTP GET or POST operation.

Syntax

    serviceop { get | post } catalog-object-name

        [ as service-name ]

        [ returns multiplicity “{ 0 | 1 | * }”

• get | post    Creates the service as either an HTTP GET or POST.  The same database operation can be exposed as both a GET and a POST, but they have to be created with unique service-name values.  The catalog-object-name refers to the database stored procedure or function to be called.  The OData server will automatically parse the input parameters and create them as IN parameters in the $metadata.
• returns multiplicity
  • Use “0” when the operation has no output;
  • Use “1” when the operation returns a single scalar value or a single complex type;
  • Use “*” when the operation returns a collection of simple or complex types;

Examples

Let’s take a quick look at some examples of the features listed above.  (All changes to the .OSDL file below will require a restart of the DBOSRV16 process.)

Removing columns from the Entity definition

The default behavior of the OData server is to expose every column in a table as an OData property.  Let’s remove the BirthDate and HireDate columns from the Employee entity.

Edit the Northwind.OSDL file, and find the entity definition for the Employees entity.  Since it has a primary key, it should just say:

"DBA"."Employees" ;

Change that line to read:

entity "DBA"."Employees" without ("BirthDate", "HireDate");

If you now open a browser, and navigate to

http://localhost:8090/odata/Employees?$format=json

You’ll see that BirthDate and HireDate have been removed from the entity definition.

Creating an Association between a Table and a View

The OData entity “Sales_by_Category” (and view of the same name) summarizes sales totals by product within product category.  A nice feature would be to associate the base Categories entity to this view, so that a single OData query could return both sets of data.  This is accomplished by creating an Association entry in the OSDL file, then linking the two Entity statements to that Association.

Edit the Northwind.OSDL file, and add the following lines to the bottom of the file:

association "CategorySales"
  principal "Categories" ("CategoryID") multiplicity "1"
  dependent "Sales_by_Category" ("CategoryID") multiplicity "*" ;

Change the Entity entry for the Categories entity to:

entity "DBA"."Categories"
      navigates ("CategorySales" as "Sales_by_Category");

Change the Entity entry for the Sales_by_Category entity to:

entity "DBA"."Sales_by_Category" keys("CategoryID","ProductName")
  navigates ("CategorySales" as "Categories");

After restarting the OData server, open a browser to

http://localhost:8090/odata/Categories?$expand=Sales_by_Category&$format=json

to see the sales totals broken out by Product within each Category.

Invoking an OData Service Operation

Calling a stored procedure and passing in arguments is now available in SA16.  Edit the Northwind.OSDL file and add the following lines at the bottom:

serviceop get "dba"."salesbycategory" returns multiplicity "*" ;

The stored procedure “dba”.”salesbycategory” takes two string arguments, @CategoryName and @OrdYear, and it returns a result set.  Note that the ServiceOP statement above mentions neither the input arguments nor the structure of the result set.  The OData server automatically parses the procedure, and adds these to the $metadata document.  The arguments are passed in as parameters on the URL.

Open a browser to

http://localhost:8090/odata/salesbycategory?@CategoryName='Beverages'&@OrdYear='1998'&$format=json

to see the result of the procedure call.  A couple of notes:

1. The OData server does not seem to parse the result set to determine the field names. Their names are generated sequentially, starting at “rtn1”.
2. The parameter names are case-sensitive.
3. String parameters require single-quotes.

-Paul Horan-