ADO.NET TUTORIAL

SQL Escape Configuration Tutorial

Updated: 05 Oct 2021

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.

Connect any application to any data source anywhere

Explore all DataDirect Connectors

Need additional help with your product?

Get Customer Support