Unified SQL

One of the most crucial problems in programming applications for several databases is that SQL syntax can be different in many situations. This article demonstrates how UniDAC helps to overcome this issue.

Database applications operate data using SQL statements. Unless entered directly by the user, the statements can be constructed in one of two ways, either hard-coded during development, or constructed at run time. The first way is very convenient for developer, while the second way is far more flexible. UniDAC allows to take best from both approaches: you can hard-code SQL statements that are transformed into appropriate syntax in run time.

General Information
Macros
Conditional Execution (IF)
Literals and Identifiers
Comments
SQL Functions
Macros Reference

General Information

Universal capabilities of UniDAC are based on the following features:

Macros that have values specific for different databases (providers). In addition to predefined macros you can define your own.
Set of automatically mapped functions.
Unified standard of literals.

Knowing this, you can write truly database-independent SQL code interpreted in run time.

Macros

UniDAC offers two approaches to working with macros: Connection Macros and DataSet Macros. They differ by the way they are defined and by the way they are indicated in the SQL query text.

DataSet Macros are difined by "&MacroName" and affect only the specified dataset.

Connection Macros are defined by "{MacroName}" and affect all associated datasets.

Lets make more detailed analysis of TUniConnection.Macros. You can work with it in the traditional way:

if  UniConnection.ProviderName = 'Oracle' then
  UniConnection.MacroByName('tablename').Value := 'dept'
else
if  UniConnection.ProviderName = 'MySql' then
  UniConnection.MacroByName('tablename').Value := 'test.dept';

Or you can use predefined approach.

Macro is a set of name, condition and value. Macro evaluates to its value if underlying condition is enabled, or to an empty string if the condition is not enabled. Conditions are enabled or disabled depending on a provider used by the TUniConnection component. For example, if you use the Oracle provider, ORACLE macro will be enabled.

Consequently, all macros that base on Oracle conditions return their value when used in SQL statements; all other macros return empty string.

For list of available conditions (in other words, predefined macros) refer to the Macros Reference.

From API point of view, macros are represented as TUniMacro class. Collections of macros are organized into TUniMacros, which can be accessed through the Macros property of TUniConnection. Each connection has individual set of macros.

The following examples demonstrate usage of macros:

UniConnection.Provider = 'MySQL';
...
UniConnection.Open;
UniConnection.Macros.Add('tablename', 'test.dept', 'MySQL');
UniQuery.SQL.Text := 'SELECT Count(*) FROM {tablename}';
UniQuery.Open;

Now suppose we need to do the same on an Oracle server. Due to usage of UniSQL the only thing to add is another macro:

UniConnection.Provider = 'Oracle';
...
UniConnection.Open;
UniConnection.Macros.Add('tablename', 'test.dept', 'MySQL');
UniConnection.Macros.Add('tablename', 'dept', 'Oracle');
UniQuery.SQL.Text := 'SELECT Count(*) FROM {tablename}';
UniQuery.Open;

As you see, it is very easy to control SQL statements transformation. Now let's take a look at another example that demonstrates a whole pack of important features:

UniConnection.Macros.Add('tablename', 'emp', '');
//For MySQL, prepend database name
UniConnection.Macros.Add('tablename', 'test.emp', 'MySQL');

//Limit records count where it is easy (MySQL and PostgreSQL)
UniConnection.Macros.Add('limit', 'LIMIT 0,5', 'MySQL');
UniConnection.Macros.Add('limit', 'LIMIT 5 OFFSET 0', 'PostgreSQL');

//Define default FROM clause
UniConnection.Macros.Add('from', 'FROM {tablename}', '');
//If the limit macro is defined, add extra clause
UniConnection.Macros.Add('from', 'FROM {tablename} {limit}', 'limit');

//Define query that uses the macro
UniQuery.SQL.Text := 'SELECT EName, Job, Sal {from}';
UniQuery.Open;

Supposed that in this sample connection is made to MySQL server, the executed statement would be

  SELECT EName, Job, Sal FROM emp LIMIT 0,5

Note: you can use DBMonitor application to see what your query turns into on execution.

A step-by step analysis of the sample reveals following important notes:

If a macro has blank condition, it is always evaluated.
Macro with enabled condition overrides macro with blank condition.
Conditions are case-insensitive.
You can use your own macros as conditions.
You can use macros as part of the value of other macros.

You can add any text after macros name inside braces. This text is added to final SQL statement if macro's condition is enabled. For example:

UniConnection.Macros.Add('schema', 'test', 'MySQL');
UniQuery.SQL.Text := 'SELECT * FROM {schema .}emp';
UniQuery.Open;

In this example a dot is added only when SCHEMA macro is enabled.

UniDAC has set of useful predefined macros that help you write universal statements. Please refer to Macros Reference for more information.

Conditional Execution (IF)

For the purpose of extra flexibility UniSQL supports conditional inclusion of SQL code into resulting statements. This is as simple as that:

{if my_macro} STATEMENT_1 {else} STATEMENT_2 {endif}

If macro my_macro is defined, the STATEMENT_1 is returned, otherwise STATEMENT_2 is the result of the expression. For instance:


{if Oracle} 
SELECT * FROM dept
{else} 
SELECT * FROM test.dept
{endif}

The {else} clause can be omitted. Here is a bit more sophisticated example:


SELECT {if Oracle}RowId, {endif} DeptNo, DName FROM dept

Note that you can use nested {if...} constructs to continue branching. Also you can use predefined macros.

Literals and Identifiers

UniDAC provides universal syntax for dates, timestamps and quoted identifiers. Its usage is similar to usage of macros. Note that this functionality is not available for OLE DB, ODBC, and DB2 data providers.

Date and time constants

In date/time constants parts of date are separated with hyphen, time parts are separated with colon, and space is expected between the two parts. The following table illustrates date/time format:

Literal type	Format	Example
date	yyyy-mm-dd	{date '2006-12-31'}
time	hh:mm:ss	{time '23:59:59'}
timestamp	yyyy-mm-dd hh:mm:ss	{timestamp '2006-12-31 23:59:59'}

The following SQL statement:

  SELECT * FROM emp WHERE HIREDATE>{date '1982-01-15'}

in MySQL evaluates to

  SELECT * FROM emp WHERE HIREDATE>CAST('1982-01-15' AS DATETIME)

and in Oracle it turns to

  SELECT * FROM emp WHERE HIREDATE>TO_DATE('1982-01-15', 'YYYY-MM-DD')

Universal quoting of identifiers

All database servers support quoting for identifiers that contain special symbols like spaces or dots. UniDAC allows to wrap identifiers universally so that quotation is appropriate for every database server. Use the following syntax:

"identifier"

For example, expression "table1"."field1" turns into "table1"."field1" in Oracle and PostgreSQL, into [table1].[field1] in MS SQL Server, and into `table1`.`field1` in MySQL server. Do not confuse with single quotes, which are intended to wrap string constants.

Comments

Comments are inserted in UniSQL with two hyphens (comments out the text till the end of current line). For multiline comment, wrap it into /*...*/ sequences. Example:

--This is a single-line comment

/*This one
  spans over
  several lines*/

SQL Functions

UniDAC introduces standard for calling common SQL functions. This is set of function names with fixed meaning. In run time the function is transformed either to corresponding native function, or to equivalent expression (for example, several functions). The construct syntax is

{fn Function_Name(parameter1 [,parameter2 ... ])}

For example, the following fragment

SELECT {fn TRIM(EName)} FROM emp

evaluates to

SELECT TRIM(EName) FROM emp

in MySQL, because there is the counterpart in the DBMS. But in MS SQL Server there is no single corresponding function, so the expression evaluates to

SELECT LTRIM(RTRIM(EName)) FROM emp

The following table lists unified functions and describes them briefly.

Function name	Description
System routines
USER	Returns current user name.
String routines
CHAR_LENGTH(string_exp)	Returns length of string expression in characters.
LOCATE(string_exp1, string_exp2)	Finds first occurrence of substring string_exp1 in string expression string_exp2.
SUBSTRING(string_exp, start, length)	Returns substring from specified string string_exp.
CONCAT(string_exp1, string_exp2)	Concatenates several string expressions.
CHAR(code)	Converts integer values into characters.
TRIM(string_exp)	Removes leading and trailing spaces from a string.
UPPER(string_exp)	Returns string_exp, with all letters uppercase.
LOWER(string_exp)	Returns string_exp, with all letters lowercase.
Number routines
TRUNCATE(numeric_exp, integer_exp)	Returns numeric_exp truncated to integer_exp places right of the decimal point.
CEILING(numeric_exp)	Returns the smallest integer value not less than numeric_exp.
Date and time routines
CURRENT_DATE	Returns date part of current timestamp, that is, year, month and day.
YEAR(date_exp)	Extracts year part of a timestamp.
MONTH(date_exp)	Extracts month part of a timestamp.
DAY(date_exp)	Extracts day part of a timestamp.
DATEADD(datepart, number, date)	Returns a new datetime value based on adding an interval to the specified date. The interval is formed as number of datepart units. The following example adds two years to HireDate field: SELECT {fn DATEADD(year,2,HireDate)} FROM emp
DATEDIFF (datepart, startdate, enddate)	Returns the number of date and time boundaries crossed between two specified dates.
Conversion routines
TODATE(string_exp)	Converts value to date format.
TOCHAR(any_type_exp)	Converts value to string format.
TONUMBER(string_exp)	Converts value to number format.

Macros Reference

The following table enumerates names of predefined macros that are enabled depending on DBMS server connected and provider used.

Provider	Macro name
Adaptive Server Enterprise	ASE
Advantage Database Server	Advantage
DB2	DB2
InterBase	InterBase
Microsoft Access	Access
MySQL	MySQL
ODBC	ODBC
Oracle	Oracle
PostgreSQL	PostgreSQL
SQLite	SQLite
SQL Server	SQLServer
DBF	DBF
NexusDB	NexusDB

There are also predefined macros that help to solve most common differences in SQL syntax. The following table enumerates them and gives translation for some databases.

Macro name	VARCHAR	DOUBLE	DATETIME	PROVIDER
Remarks	Evaluates to database type that represents string values. Used mainly in CAST expressions.	Evaluates to database type that represents floating point values. Used mainly in CAST expressions.	Evaluates to database type that represents date and time values. Used mainly in CAST expressions.	Evaluates to the name of currently used provider
Adaptive Server Enterprise	VARCHAR	FLOAT	DATETIME	ASE
Advantage	VARCHAR	DOUBLE	TIMESTAMP	Advantage
DB2	VARCHAR	DOUBLE	TIMESTAMP	DB2
InterBase	VARCHAR	DOUBLE PRECISION	TIMESTAMP	InterBase
Microsoft Access	VARCHAR	DOUBLE	DATE	Access
MySQL	VARCHAR	DOUBLE	DATETIME	MySQL
ODBC	VARCHAR	DOUBLE	TIMESTAMP	ODBC
Oracle	VARCHAR2	NUMBER	DATE	Oracle
PostgreSQL	VARCHAR	DOUBLE PRECISION	TIMESTAMP	PostgreSQL
SQLite	VARCHAR	DOUBLE PRECISION	TIMESTAMP	SQLite
SQL Server	VARCHAR	FLOAT(53)	DATETIME	SQL Server
DBF	VARCHAR	DOUBLE	DATE	DBF
NEXUS	VARCHAR	DOUBLE	DATETIME	NexusDB

Working with Macros

Request Support

DAC Forum

Provide Feedback