SQL Dialect support of the WSO2 Data Services Server allows users to create multiple SQL queries for different SQL dialects by checking the driver. This helps maintain driver-specific functionality/key words in SQL statements of a single data service query.
For example, if a user wants to determine the data length retrieved from a column, there can be different ways to write the SQL query depending on the SQL driver.
On MySQL and PostgreSQL:
| Code Block |
|---|
SELECT OCTET_LENGTH(employeeNumber) FROM Employees; |
...
| Code Block |
|---|
SELECT LENGTH(employeeNumber) FROM Employees |
To avoid writing different data service queries for the same operation depending on the configuration, we can write all three SQL queries in the same data service query as shown below.
To define a query for your data service, open the Add New Query page. This query executes if none of the SQL dialects match.
1. Follow the instruction instructions in section Handling Data Service Queries to open the Add New Query page.
2. To define a SQL dialect, click on the Add New SQL Dialect link on the Add New Query page.
3. The Add/Edit SQL Dialect window opens. Select the required SQL driver and define the SQL statement and click Save.
For example,4.
If the SQL query is the same for multiple drivers, select all drivers at once and define the query as follows:
If you want to define a SQL dialect for a driver other than the ones listed in the supported drivers list, you can provide the driver prefix in the provided text field and define the SQL query as shown in the figure above.