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 dotConnect Universal 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. dotConnect Universal 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 and Conditions
• Literals and Identifiers
• Comments
• Conditional Execution (IF)
• Outer Joins
• Functions
• Macros Reference

General Information

Universal capabilities of dotConnect Universal 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 and Conditions

Macro is a set of name, condition and value. Macro evaluates to its value if underlying condition is enabled, or to empty string if the condition is not enabled. Conditions are enabled or disabled depending on DBMS server the UniConnection is connected to, and data provider used. For example, if you connect to Oracle server using dotConnect for Oracle, the following conditions will be enabled:

1. Oracle - because Oracle server is used.
2. OraDirect - because dotConnect for Oracle is used.

Consequently, all macros that base on Oracle and OraDirect 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 UniMacro class. Collections of macros are organized into UniMacroCollection, which can be accessed through Macros property of UniConnection. Each connection has individual set of macros.

The following examples demonstrate usage of macros:

UniConnection conn = new UniConnection();
//Connecting to MySQL server through dotConnect for MySQL
conn.ConnectionString = "provider=MySQL;Password=root;User Id=root;Host=localhost;Port=3307;";
//Define macro for table dept in database test
UniMacro mac = new UniMacro("tablename", "test.dept", "MySQL");
conn.Macros.Add(mac);
//Define query that uses the macro
UniCommand cmd = new UniCommand("SELECT Count(*) FROM {tablename}");
cmd.Connection = conn;
conn.Open();
try {
  //Run the query and make sure it is executed correctly
  string response = cmd.ExecuteScalar().ToString();
  MessageBox.Show(response);
}
finally {        
  conn.Close();
}

Dim conn As New UniConnection()
'Connecting to MySQL server through dotConnect for MySQL
conn.ConnectionString = "provider=MySQL;Password=root;User Id=root;Host=localhost;Port=3307;"
'Define macro for table dept in database test
Dim mac As New UniMacro("tablename", "test.dept", "MySQL")
conn.Macros.Add(mac)
'Define query that uses the macro
Dim cmd As New UniCommand("SELECT Count(*) FROM {tablename}")
cmd.Connection = conn
conn.Open()
Try
  'Run the query and make sure it is executed correctly
  Dim response As String = cmd.ExecuteScalar().ToString()
  MessageBox.Show(response)
Finally
  conn.Close()
End Try

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 conn = new UniConnection();
//Connecting to Oracle server through dotConnect for Oracle
conn.ConnectionString = "provider=Oracle;Password=tiger;User Id=Scott;Host=ora920;";
//Define macros for tables dept
UniMacro mac = new UniMacro("tablename", "test.dept", "MySQL");
conn.Macros.Add(mac);
UniMacro mac2 = new UniMacro("tablename", "dept", "Oracle");
conn.Macros.Add(mac2);
//Define query that uses the macro
UniCommand cmd = new UniCommand("SELECT Count(*) FROM {tablename}");
cmd.Connection = conn;
conn.Open();
try {
  //Run the query and make sure it is executed correctly
  string response = cmd.ExecuteScalar().ToString();
  MessageBox.Show(response);
}
finally {        
  conn.Close();
}

Dim conn As New UniConnection()
'Connecting to Oracle server through dotConnect for Oracle
conn.ConnectionString = "provider=Oracle;Password=tiger;User Id=Scott;Host=ora920;"
'Define macro for table dept
Dim mac As New UniMacro("tablename", "test.dept", "MySQL")
conn.Macros.Add(mac)
Dim mac2 As New UniMacro("tablename", "dept", "Oracle")
conn.Macros.Add(mac2)
'Define query that uses the macro
Dim cmd As New UniCommand("SELECT Count(*) FROM {tablename}")
cmd.Connection = conn
conn.Open()
Try
  'Run the query and make sure it is executed correctly
  Dim response As String = cmd.ExecuteScalar().ToString()
  MessageBox.Show(response)
Finally
  conn.Close()
End Try

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 conn = new UniConnection();
//Connecting to MySQL server through dotConnect for MySQL
conn.ConnectionString = "provider=MySQL;Password=root;User Id=root;Host=localhost;Port=3307;";
//Define table name for all databases (blank condition)
//Note the single-line macro creation in the collection
conn.Macros.Add("tablename", "emp");
//For MySQL, prepend database name
conn.Macros.Add("tablename", "test.emp", "MySQL");
//Limit records count where it is easy (MySQL and PostgreSQL)
conn.Macros.Add("limit", "LIMIT 0,5", "mysql");
conn.Macros.Add("limit", "LIMIT 5 OFFSET 0", "PostgreSQL");
//Define default FROM clause
conn.Macros.Add("from", "FROM {tablename}");
//If the limit macro is defined, add extra clause
conn.Macros.Add("from", "FROM {tablename} {limit}", "limit");
//Define query that uses the macro
UniCommand cmd = new UniCommand("SELECT EName, Job, Sal {from}");
cmd.Connection = conn;
conn.Open();
try {
  UniDataReader dataReader = cmd.ExecuteReader();
  ...
  dataReader.Close();
}
finally {
  conn.Close();
}

Dim conn As New UniConnection()
'Connecting to MySQL server through dotConnect for MySQL
conn.ConnectionString = "provider=MySQL;Password=root;User Id=root;Host=localhost;Port=3307;"
'Define table name for all databases (blank condition)
'Note the single-line macro creation in the collection
conn.Macros.Add("tablename", "emp")
'For MySQL, prepend database name
conn.Macros.Add("tablename", "test.emp", "MySQL")
'Limit records count where it is easy (MySQL and PostgreSQL)
conn.Macros.Add("limit", "LIMIT 0,5", "mysql")
conn.Macros.Add("limit", "LIMIT 5 OFFSET 0", "PostgreSQL")
'Define default FROM clause
conn.Macros.Add("from", "FROM {tablename}")
'If the limit macro is defined, add extra clause
conn.Macros.Add("from", "FROM {tablename} {limit}", "limit")
'Define query that uses the macro
Dim cmd As New UniCommand("SELECT EName, Job, Sal {from}")
cmd.Connection = conn
conn.Open()
Try
  'Run the query and make sure it is executed correctly
  Dim response As String = cmd.ExecuteScalar().ToString()
  MessageBox.Show(response)
Finally
  conn.Close()
  Dim dataReader As UniDataReader = cmd.ExecuteReader()
  ...
  dataReader.Close()
End Try

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:

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

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

Literals and Identifiers

dotConnect Universal provides universal syntax for dates, timestamps, intervals LIKE expressions 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:

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')

Intervals

Date/time intervals are formatted similar to date/time constants. The following examples demonstrate how to add interval to or subtract from a date:

  SELECT HIREDATE+{interval '4' DAY} FROM emp;
  SELECT HIREDATE+{interval '4 10:20:30' DAY TO SECOND} FROM emp;
  SELECT HIREDATE-{interval '1-2' YEAR TO MONTH} FROM emp;

The first statement adds 4 days to selected field; the second statement adds 4 days, 10 hours, 20 minutes and 30 seconds; the last statement subtracts 14 months from the field. Note that you cannot combine MONTH or YEAR with any other qualifier in the same interval. Use two intervals instead, or express it in smaller units.

Universal quoting of identifiers

All database servers support quoting for identifiers that contain special symbols like spaces or dots. dotConnect Universal 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.

LIKE Escape symbol

dotConnect Universal allows to perform database-independent escaping in LIKE expressions. To do this, add the following declaration after LIKE expression: {escape 'esc_symbol'} where esc_symbol is the symbol that precedes the special character. For example the following query

SELECT * FROM emp WHERE EName LIKE 'Mike\_S%' {escape '\'}

orders server to return all rows that have EName field beginning with Mike_S constant. Here the underscore symbol is not treated as "any character", while the last symbol is interpreted as wildcard.

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*/

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.

Outer Joins

In some old versions of Oracle server SQL syntax for OUTER JOINs differs from common standard. The problem can be solved using conditional execution, but UniSQL allows to do it even easier with {oj ...} construct:

{oj Outer_Join_Expression}

In other words, if you wrap the OUTER JOIN clause in {oj ... }, and current database server is Oracle (version earlier than 9), the clause is translated to appropriate syntax. For all other servers the OUTER JOIN clause is left as is. The following example demonstrates the rule:

SELECT 
  dept.DName, emp.EName, emp.Job
FROM
  {oj dept 
LEFT OUTER JOIN 
  emp 
ON
  dept.DeptNo = emp.DeptNo}

In Oracle, this query can be transformed to the following one:

SELECT 
  dept.DName, emp.EName, emp.Job
FROM
  dept, emp
WHERE
  dept.DeptNo = emp.DeptNo (+) 

In MySQL, the query is not much modified:

SELECT 
  dept.DName, emp.EName, emp.Job
FROM
  dept 
LEFT OUTER JOIN 
  emp 
ON
  dept.DeptNo = emp.DeptNo

Note that for OLE DB and ODBC the transformation does not take place, even if the connected server is Oracle 8.x.

Functions

dotConnect Universal 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.

Note that function mapping is not available for OLE DB, ODBC, and DB2 data providers.

Macros Reference

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

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.

See Also

UniMacro Class  | UniConnection Class  | dotConnect Universal articles