Transforming an OData Request to a SQL Query

You can transform the input OData GET request to an SQL SELECT query by using the Transform to SQL Select operation. This operation is useful when combined with the select operation of the Anypoint Connector for Database (Database Connector).

*Transform to SQL select* element and *Entity set name* field highlighted

The output payload is a prepared statement and the attributes are a map of prepared statement parameters.

*Select database* and *Input Parameters* field highlighted

You can use this operation with the Database connector to expose databases that use OData V4.

*Serialized entity* element and the *Inbound content* field highlighted

APIkit supports the following elements:

System query options:
- $filter
- $select
- $search
- $orderby
- $skip
- $top
Single entity
Entity relation
Server-side pagination

APIkit does not support the following elements:

Parameter Aliases
Built-in Filter Operations:
- has
- divby
Built-in Query Functions, all except:
- contains
- startswith
- endwith
$apply
$compute
$expand

Transform to SQL Select Operation in OData V4

The OData protocol defines a series of conventions that enable clients to query identified resources by using URLs. The list of queryable entities, their fields, and the relationships between them are defined in a data model by using HTTP messages. OData shares some similarities with Java database connectivity (JDBC). The transform-to-sql-select operation uses such similarities to help you create SQL queries for the resources. The operation results in a message that has a prepared statement with the SQL select sentence as the payload and the parameters name and values map as the attributes.

Implement the Transform to SQL Select Operation by Using APIkit for OData V4

You can implement the transform to SQL operation by using the <apikit-odata:transform-to-sql-select> XML element, which has the following parameters:

Parameter name Description

Parameter name	Description
Display name	Name of the operation.
Module configuration	Name of the related-module configuration.
Entity set name	Name of the entity set to fetch #[attributes.entitySetName].
Page size	Number that defines the amount of results per page during server-side pagination.
Entity type name	String obtained from the OData request by using #[attributes.odataRequestAttributes], which the entity data model specifies.
Entity type fields	Array of strings obtained from the OData request by using #[attributes.odataRequestAttributes], which provides the list of property fields obtained from the entity type.
Entity type keys	Object obtained from the OData request by using #[attributes.odataRequestAttributes], which provides a map of entity key names and values.
System query options	Parameter obtained from the OData request by using #[attributes.odataRequestAttributes].
Search transformation enabled	If `true`, this option enables the $search transformation option for a WHERE clause.
Strict search term match	If `true`, this option enables full term matching for $search. If enabled, terms in the WHERE clause aren’t enclosed between '%'.
SQL identifiers delimiter	Option that delimits identifiers in the generated SQL statement (Options: DOUBLE_QUOTES, BRACKETS, and BACK_TICKS). Examples: DOUBLE_QUOTES: `SELECT "TrackId", "Name" FROM "Tracks"` BRACKETS: `SELECT [TrackId], [Name] FROM [Tracks]` BACK_TICKS: SELECT `TrackId`, `Name` FROM `Tracks`
SQL limit rows	Option that limits the numbers of rows (Options: LIMIT and FETCH_NEXT_ROWS_ONLY). Examples: LIMIT: `SELECT OrderID, ShippedDate, ShipCountry FROM Orders LIMIT :parameter1` FETCH_NEXT_ROWS_ONLY: `SELECT OrderID, ShippedDate, ShipCountry FROM Orders ORDER BY OrderID ASC OFFSET :parameter2 ROWS FETCH NEXT :parameter1 ROWS ONLY` `ORDER BY` is automatically included if not present in the request by using the entity key defined in CSDL.
Date Transform Type	Values provided, one of: String: `Date` or `DateTimeOffset` parameters pass as `string` (default value). java.time - LocalDate and LocalDateTime: `Date` or `DateTimeOffset` parameters pass as objects of `java.time` package classes `LocalDate` and `LocalDateTime`. This option enables most DB drivers -such as MySQL- to identify and process `Date/DateTime` parameters and return the correct result. java.time - LocalDate and OffsetDateTime: `Date` or `DateTimeOffset` parameters pass as objects of `java.time` package classes `LocalDate` and `OffsetDateTime`. This option enables most DB drivers -such as Oracle- to identify and process `Date`, `Timestamp`, and `Timestamp with Time Zone` parameters and return the correct result. This operation takes the `Date/DateTime` values as strings by default. Select one of the `java.time object` options to output it as a corresponding `Date` or `DateTime` object in the `java.time` package.

Display name

Name of the operation.

Module configuration

Name of the related-module configuration.

Entity set name

Name of the entity set to fetch #[attributes.entitySetName].

Page size

Number that defines the amount of results per page during server-side pagination.

Entity type name

String obtained from the OData request by using #[attributes.odataRequestAttributes], which the entity data model specifies.

Entity type fields

Array of strings obtained from the OData request by using #[attributes.odataRequestAttributes], which provides the list of property fields obtained from the entity type.

Entity type keys

Object obtained from the OData request by using #[attributes.odataRequestAttributes], which provides a map of entity key names and values.

System query options

Parameter obtained from the OData request by using #[attributes.odataRequestAttributes].

Search transformation enabled

If true, this option enables the $search transformation option for a WHERE clause.

Strict search term match

If true, this option enables full term matching for $search. If enabled, terms in the WHERE clause aren’t enclosed between '%'.

SQL identifiers delimiter

Option that delimits identifiers in the generated SQL statement (Options: DOUBLE_QUOTES, BRACKETS, and BACK_TICKS).

Examples:

DOUBLE_QUOTES: SELECT "TrackId", "Name" FROM "Tracks"
BRACKETS: SELECT [TrackId], [Name] FROM [Tracks]
BACK_TICKS: SELECT `TrackId`, `Name` FROM `Tracks`

SQL limit rows

Option that limits the numbers of rows (Options: LIMIT and FETCH_NEXT_ROWS_ONLY).

Examples:

LIMIT: SELECT OrderID, ShippedDate, ShipCountry FROM Orders LIMIT :parameter1
FETCH_NEXT_ROWS_ONLY: SELECT OrderID, ShippedDate, ShipCountry FROM Orders ORDER BY OrderID ASC OFFSET :parameter2 ROWS FETCH NEXT :parameter1 ROWS ONLY

ORDER BY is automatically included if not present in the request by using the entity key defined in CSDL.

Date Transform Type

Values provided, one of:

String: Date or DateTimeOffset parameters pass as string (default value).
java.time - LocalDate and LocalDateTime: Date or DateTimeOffset parameters pass as objects of java.time package classes LocalDate and LocalDateTime. This option enables most DB drivers -such as MySQL- to identify and process Date/DateTime parameters and return the correct result.
java.time - LocalDate and OffsetDateTime: Date or DateTimeOffset parameters pass as objects of java.time package classes LocalDate and OffsetDateTime. This option enables most DB drivers -such as Oracle- to identify and process Date, Timestamp, and Timestamp with Time Zone parameters and return the correct result.

This operation takes the Date/DateTime values as strings by default. Select one of the java.time object options to output it as a corresponding Date or DateTime object in the java.time package.

$filter Expressions in OData V4

The OData $filter expression uses comparison, logical, arithmetic, and grouping operators. For a list of operators and their meaning, see the OData4 Specification.

APIkit does not support the has and divby operators.

Expressions Supported by Transform to SQL Select

For the filtering expressions that OData supports, see the specification.

The transform-to-sql-select operation supports the following subset of unary and binary operators, and the following method operators.

OData Operator	SQL Operator
add	+
and	AND
div	/
eq	=
ge	>=
gt	>
le	=<
lt	<
mod	%
mul	*
ne	<>
or	OR
sub	-
not	NOT
-	-
contains	LIKE %value%
startswith	LIKE value%
endswith	LIKE %value

OData Operator

SQL Operator

add

and

AND

div

mod

mul

sub

not

NOT

contains

LIKE %value%

startswith

LIKE value%

endswith

LIKE %value

$search Support

This operation builds a WHERE clause based on the $search expression that the system query option provides. The implementation selects all of the elements that have property values that match the condition.

By default, a term matches if the field value contains the term. If the strict search term match is set to true in the configuration, the search operation requires an exact match between the field value and the search term.

The search transformation enabled and strict search term match parameters define the $search feature.