[Download] | [Documentation Home] | [Release Note]

SQL Dialects

Introduction

This functionality allows users to create multiple SQL queries (for different SQL dialects) by checking the driver. This helps users to have driver specific functionalities/key words in their SQL statements in 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 :
SELECT OCTET_LENGTH(employeeNumber) FROM Employees;


On Microsoft SQL Server :

SELECT DATALENGTH(employeeNumber) FROM Employees


On Oracle :

SELECT LENGTH(employeeNumber) FROM Employees

How to use SQL Dialects support to avoid writing different data service queries

To avoid writing differant 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.

Define dbs file

How to use SQL Dialects feature from DSS wizard

In 'Add Query' page we can define the sql query. This query will execute if non of the sql dialects matches. To define sql dialect, click on the 'Add New SQL Dialect' link as shown in the Figure 01.

Add New SQL Dialect
Figure 01: Add New SQL Dialect

Once click on Add New SQL Dialect it will redirect to the page shown in Figure 02 and from there you can select the required sql driver and define the sql statement.

Click Add New SQL Dialect
Figure 02: Click on Add New SQL Dialect

If the sql query is same for multiple drivers, then we can select all these drivers at once and define the sql query.

Selecting Multiple Drivers
Figure 03: Selecting Multiple Drivers

If you want to define sql dialect for other than the drivers in supported driver list, you can provide the driver prefix in provided text field and define the sql query.