Adding an SQL Dialect

SQL Dialect support 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, 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:

SELECT OCTET_LENGTH(employeeNumber)
FROM Employees;

On Microsoft SQL Server:

SELECT DATALENGTH(employeeNumber)
FROM Employees

On Oracle:

SELECT LENGTH(employeeNumber)
FROM Employees

To avoid writing different data service queries for the same operation depending on the configuration, write all three SQL queries in the same data service query as shown below.

<config id="default">
      <property name="org.wso2.ws.dataservice.driver">com.mysql.jdbc.Driver</property>
      <property name="org.wso2.ws.dataservice.protocol">jdbc:mysql://localhost:3306/MyDB</property>
      <property name="org.wso2.ws.dataservice.user">root</property>
      <property name="org.wso2.ws.dataservice.password">password</property>
</config>
<query id="q1" useConfig="default">
      <sql>SELECT DATALENGTH(employeeNumber) FROM Employees</sql>
      <sql dialect="oracle">SELECT LENGTH(employeeNumber) FROM Employees</sql>
      <sql dialect="h2,mysql,postgresql">SELECT OCTET_LENGTH(employeeNumber) FROM Employees;</sql>
      <sql dialect="mysql">SELECT DATALENGTH(employeeNumber) FROM Employees</sql>
      <result element="Employees" rowName="Employee">
         <element column="employeeNumber" name="employeeNumber" />
      </result>
</query>

Follow the steps below to add an SQL dialect to a query.

1. The SQL dialect option appears when adding a query to data sources such as RDBMS. For example, 
2. Click the link to open the SQL Dialect window. Provide the required SQL driver, define the SQL statement and Sav e. For example, 
3. If the SQL query is the same for multiple drivers, select all drivers at once and define the query as follows: 
   To define an SQL dialect for a driver other than the ones listed in the supported drivers list, provide the driver prefix in the text field and define the SQL query as shown above.