![*Transform to SQL select* element and *Entity set name* field highlighted](_images/transform-to-sql-transformer.png)
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](_images/transform-to-sql-transformer.png)
The output payload is a prepared statement and the attributes are a map of prepared statement parameters.
![*Select database* and *Input Parameters* field highlighted](_images/add-db-transform-to-sql.png)
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](_images/serialize-transform-to-sql.png)
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 |
---|---|
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 |
Strict search term match |
If |
SQL identifiers delimiter |
Option that delimits identifiers in the generated SQL statement (Options: DOUBLE_QUOTES, BRACKETS, and BACK_TICKS). Examples:
|
SQL limit rows |
Option that limits the numbers of rows (Options: LIMIT and FETCH_NEXT_ROWS_ONLY). Examples:
|
Date Transform Type |
Values provided, one of:
This operation takes the |
$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 |
$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.