SQL Escape

Introduction

ADO.NET does not define a query language; therefore, data providers are not required to mask the differences in supported SQL statements between databases. DataDirect Technologies™ provides support for ODBC/JDBC SQL escapes for scalar functions, outer joins, and stored procedures in our providers. This means that developers can pass standardized SQL statements to all our data providers.

Why Use SQL Escape Sequences?

A number of language features, such as outer joins and scalar function calls, are commonly implemented by DBMSs. The syntax for these features is often DBMS-specific, even when a standard syntax has been defined. .NET supports escape sequences that contain standard syntaxes for the following language features:

Date, time, and timestamp literals
Scalar functions such as numeric, string, and data type conversion functions
Outer joins

The escape sequence used by .NET is:

{extension}

The escape sequence is recognized and parsed by the DataDirect Connect for ADO.NET data provider, which replaces the escape sequences with data store-specific grammar.

Date, Time and Timestamp Literals

The escape sequence for date, time, and timestamp literals is:

{literal-type 'value'}

where literal-type is one of the following options:

literal-type	Description	Value Format
d	Date	yyyy-mm-dd
t	Time	hh:mm:ss [1]
ts	Timestamp	yyyy-mm-dd hh:mm:ss[.f...]

Example:

UPDATE Orders SET OpenDate={d '1997-01-29'}

WHERE OrderID=1023

Scalar Functions

You can use scalar functions in SQL statements with the following syntax:

{fn scalar-function}

where scalar-function is a scalar function supported by the DataDirect Connect for ADO.NET providers, as listed in the following table.

Example:

SELECT {fn UCASE(NAME)} FROM EMP

Data Store	String Functions	Value FormatNumeric Functions	Timedate Functions	System Functions
DB2 V6.1 for iSeries	CHAR _LENGTH CHARACTER _LENGTH CONCAT DIFFERENCE LCASE LEFT LENGTH LOCATE LTRIM POSITION RTRIM SOUNDEX SPACE SUBSTRING UCASE	ABS or ABSVAL ACOS ASIN ATAN ATAN2 BIGINT CEILING or CEIL COS COT DECIMAL DEGREES DIGITS DOUBLE EXP FLOAT FLOOR INTEGER LN LOG LOG10 MOD POWER RADIANS RAND REAL ROUND SIGN SIN SMALLINT SQRT TAN TRUNCATE	CURDATE CURTIME CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP DAYNAME DAYOFWEEK DAYOFYEAR HOUR MINUTE MONTH MONTHNAME NOW QUARTER SECOND WEEK YEAR	DATABASE NULLIF USER

Data Store	String Functions	Value FormatNumeric Functions	Timedate Functions	System Functions
DB2 for z/OS	CHAR _LENGTH CHARACTER _LENGTH CONCAT INSERT LCASE LEFT LENGTH LOCATE LTRIM POSITION REPEAT REPLACE RIGHT RTRIM SPACE SUBSTRING UCASE	ABS or ABSVAL ACOS ASIN ATAN ATAN2 BIGINT CEILING or CEIL COS COT DECIMAL DEGREES DIGITS DOUBLE EXP FLOAT FLOOR INTEGER LN LOG LOG10 MOD POWER RADIANS RAND REAL ROUND SIGN SIN SMALLINT SQRT TAN TRUNCATE	CURDATE CURTIME CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR HOUR MINUTE MONTH MONTHNAME NOW QUARTER SECOND WEEK YEAR	DATABASE NULLIF USER

Data Store	String Functions	Value FormatNumeric Functions	Timedate Functions	System Functions
DB2 for Windows/ UNIX/Linux	ASCII CHAR CHAR _LENGTH CHARACTER _LENGTH CONCAT DIFFERENCE INSERT LCASE LEFT LENGTH LOCATE LTRIM POSITION REPEAT REPLACE RIGHT RTRIM SOUNDEX SPACE SUBSTRING UCASE	ABS or ABSVAL ACOS ASIN ATAN ATAN2 BIGINT CEILING or CEIL COS COT DECIMAL DEGREES DIGITS DOUBLE EXP FLOAT FLOOR INTEGER LN LOG LOG10 MOD POWER RADIANS RAND REAL ROUND SIGN SIN SMALLINT SQRT TAN TRUNCATE	CURDATE CURTIME CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP DAYNAME DAYOFWEEK DAYOFYEAR HOUR MINUTE MONTH MONTHNAME NOW QUARTER SECOND WEEK YEAR	DATABASE NULLIF USER

Data Store	String Functions	Value FormatNumeric Functions	Timedate Functions	System Functions
Oracle	ASCII BIT_LENGTH CHAR CONCAT INSERT LCASE LEFT LENGTH LOCATE LOCATE2 LTRIM OCTET_LENGTH REPEAT REPLACE RIGHT RTRIM SOUNDEX SPACE SUBSTRING UCASE	ABS ACOS ASIN ATAN ATAN2 CEILING COS COT EXP FLOOR LOG LOG10 MOD PI POWER ROUND SIGN SIN SQRT TAN TRUNCATE	CURDATE DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR HOUR MINUTE MONTH MONTHNAME NOW QUARTER SECOND WEEK YEAR	IFNULL USER
SQL Server	ASCII BIT_LENGTH CHAR CONCAT DIFFERENCE INSERT LCASE LEFT LENGTH LOCATE LTRIM OCTET_LENGTH REPEAT REPLACE RIGHT RTRIM SOUNDEX SPACE SUBSTRING UCASE	ABS ACOS ASIN ATAN ATAN2 CEILING COS COT DEGREES EXP FLOOR LOG LOG10 MOD PI POWER RADIANS RAND ROUND SIGN SIN SQRT TAN TRUNCATE	CURDATE CURTIME CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT HOUR MINUTE MONTH MONTHNAME NOW QUARTER SECOND TIMESTAMPADD TIMESTAMPDIFF WEEK YEAR	CONVERT DATABASE IFNULL USER

Data Store	String Functions	Value FormatNumeric Functions	Timedate Functions	System Functions
Sybase	ASCII CHAR CONCAT DIFFERENCE INSERT LCASE LEFT LENGTH LOCATE LTRIM REPEAT RIGHT RTRIM SOUNDEX SPACE SUBSTRING UCASE	ABS ACOS ASIN ATAN ATAN2 CEILING COS COT DEGREES EXP FLOOR LOG LOG10 MOD PI POWER RADIANS RAND ROUND SIGN SIN SQRT TAN	DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR HOUR MINUTE MONTH MONTHNAME NOW QUARTER SECOND TIMESTAMPADD TIMESTAMPDIFF WEEK YEAR	DATABASE IFNULL USER

Stored Procedure Escape Sequences

A stored procedure is an executable object stored in the data store. Generally, it is one or more SQL statements that have been precompiled. The escape sequence for calling a procedure is:

{{[?=]call procedure-name [(parameter[,parameter]...)]}

where procedure-name specifies the name of a stored procedure. parameter specifies a stored procedure parameter.

The data provider translates the escape to the underlying DBMS's format for executing a stored procedure when both of the following conditions are true:

The CommandType property of the data provider's Command object is set to either CommandType.StoredProcedure or to CommandType.Text.
The Text property of the Command object conforms to the defined escape syntax.

NOTE: Using a stored procedure escape does not change the existing behavior of CommandType.StoredProcedure (that is, if Command.Text is set only to the procedure name). It only adds to the existing support for calling stored procedures.

Outer Join Escape Sequences

NET supports the SQL92 left, right, and full outer join syntax. The escape sequence for outer joins is:

{oj outer-join}

where outer-join is

table-reference {LEFT | RIGHT | FULL} OUTER JOIN

{table-reference | outer-join} ON search-condition

Example:

SELECT Customers.CustID, Customers.Name, Orders.OrderID,

Orders.Status

FROM {oj Customers LEFT OUTER JOIN

Orders ON Customers.CustID=Orders.CustID}

WHERE Orders.Status='OPEN'

The following table lists the outer join escape sequences supported by DataDirect Connect for ADO.NET for each data store.

Data Store	Outer Join Escape Sequences
DB2	Left outer joins Right outer joins Full outer joins
Oracle	Left outer joins Right outer joins Nested outer joins
SQL Server	Left outer joins Right outer joins Full outer joins
Sybase	Left outer joins Right outer joins Nested outer joins

SQL Extension Escape

The RowSetSize property of the data provider's Command object allows applications to limit the size of the result set returned.

Developers must set the property explicitly, for example:

OracleCommand.RowSetSize = 10;

Although using the provider-specific RowSetSize property is convenient, it means that the programmer cannot code to generic ADO.NET interfaces such as IDBCommand.

To increase the interoperability of the code, developers can use the SQL extension escape for the RowSetSize property instead. For example:

my_SQL_statement {ext RowSetSize x}

Where my_SQL_statement is a SQL statement, and x is the number of rows to which the application wants the result set limited.

This extension can be used with any SQL statement. However, if the statement generates no results, for example, a DELETE statement, then the extension has no effect.

NOTE: The SQL extension escape must be placed at the end of the SQL statement. Otherwise, the database server may return a syntax error when the statement is executed.

Using the RowSetSize SQL escape extension has the same effect as setting the Command.RowSetSize property. However, the effect is limited to the result set created by the SQL statement. The RowSetSize SQL escape extension does not set the RowSetSize property on the Command object.

Example

SELECT * FROM mytable WHERE mycolumn2 > 100 {ext RowSetSize 100}

A maximum of 100 rows are returned from the result set. If the result set contains less than 100 rows, then the SQL extension escape has no affect. The size of the result sets created by subsequent SQL statements is not limited.

If the application contains both the RowSetSize SQL extension escape and the RowSetSize property for a command, the escape takes precedence.

Conclusion

At DataDirect Technologies, we build our ADO.NET data providers using a common approach and architecture. Interoperability features include support for ODBC/JDBC stored procedure escape sequences including date/time literals, scalar functions, and outer joins. In addition, you can code for result sets returned from stored procedures in Oracle the same way you code for them in Microsoft SQL Server, Sybase, and DB2.

By keeping our interfaces and functionality the same in our ADO.NET data providers, we minimize the amount of code that must change when you use the providers across different databases.

DataDirect Technologies offers the following ADO.NET data providers built with 100% managed code that support the .NET Framework Version 2.0:

DataDirect Connect® for ADO.NET 3.0 DB2 data provider
DataDirect Connect® for ADO.NET 3.0 Oracle data provider
DataDirect Connect® for ADO.NET 3.0 SQL Server data provider
DataDirect Connect® for ADO.NET 3.0 Sybase data provider

Existing code written for earlier versions of the .NET Framework and earlier versions of DataDirect Connect for .NET is compatible with the 3.0 version of the data providers. Note that the applications must be re-compiled using the .NET Framework 2.0.

However, if your applications require Windows 98 and Windows Me and/or the .NET Framework 1.x, you can use the DataDirect Connect for .NET 2.2 data providers, which DataDirect will continue to make available.

DataDirect
connectors

Connect any data source to any application

DataDirect
Tutorials

Discussions, tips and tricks for
DataDirect Connect drivers