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:
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:
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:
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.