Transforming an OData Request to a SQL Query | MuleSoft Documentation
# 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).
The output payload is a prepared statement and the attributes are a map of prepared statement parameters.
You can use this operation with the Database connector to expose databases that use OData V4.
APIkit supports the following elements:
  * System query options:
    * $filter
    * $select
    * $search
    * $orderby
    * $skip
  * Single entity
  * Entity relation
  * Server-side pagination


APIkit does not support the following elements:
  * Parameter Aliases
  * Built-in Filter Operations:
  * 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 `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  
---|---  
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.
## See Also
  * APIkit for OData v4 Module Reference
  * APIkit for OData v4 Error Handling Reference