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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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
- Syntax
entity "owner"."object-name" [ as "entityset-name" ]
[ { with | without } ("included-or-excluded-column- name"[ , ... ]) ]
[ keys ( { ("key-column-name"[ , ... ]) | generate local "key-column-name" } ]
[ concurrencytoken ("token-column-name" [ , ... ] ) ]
[ navigates ("association-name" as "navprop-name" [ from { principal | dependent } ] [, ...] ) ]
- 'as "entityset-name"' This allows you to use a different name from the matching table or view name in the OData definition of the entity.
- { with | without } ("included-or-excluded-column-name"[, ...]) To create an inclusive list of columns in the OData definition, use WITH and a comma-separated column name list. To create an exclusive list (i.e., all columns EXCEPT those listed), use the WITHOUT keyword.
- keys ( { ("key-column-name"[, ...]) This is only required for tables or views that do not have a defined primary key. The generate local "key-column-name" clause adds a generated property of type Edm.Int64 to the result set that can serve as a unique ID in the result. When used, these are sequentially numbered, starting at 1, and they're only valid for the current retrieval.
- concurrencytoken ("token-column-name" [, ...]) This is a list of columns ('properties') that are always modified when an entity instance is updated, such as a DEFAULT CURRENT TIMESTAMP or an integer column that is incremented by an update trigger.
- navigates ("association-name"as "navprop-name" [ from { principal | dependent } This clause defines an association to a second entity by referencing the "Association" object. This will be defined more clearly in the next section...
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:
- The OData server does not seem to parse the result set to determine the field names. Their names are generated sequentially, starting at "rtn1".
- The parameter names are case-sensitive.
- String parameters require single-quotes.
-Paul Horan-
Bluesky
