Developer Reference : Data Access Methods : Data Provider for .NET : SQL Escape Sequences for .NET
 
SQL Escape Sequences for .NET
A number of language features, such as outer joins and scalar function calls, are commonly implemented by database management systems. The syntax for these features is often DBMS-specific, even when a standard syntax has been defined. The .NET support for escape sequences 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 ADO.NET Data Provider, which replaces the escape sequences with data store-specific grammar.
Date, Time, and Timestamp Escape Sequences
The escape sequence for date, time, and timestamp literals is:
{literal-type 'value'}
where literal-type is one of the following:
 
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...]
Note: If you receive an error while running a query in Visual Studio to insert data into the Date field of a table, ensure that your system's Date format is set to yyyy-mm-dd. If not, change it to yyyy-mm-dd.
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 ADO.NET Data Provider.
Example
SELECT {fn UCASE(NAME)} FROM EMP
Table 73 lists the scalar functions supported.
 
Table 73 Scalar Functions Supported 
String
Functions
Numeric Functions
Timedate
Functions
System
Functions
ASCII
BIT_LENGTH
CHAR
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
LCASE or LOWER
LEFT
LENGTH
LOCATE
LTRIM
OCTET_LENGTH
POSITION
REPLACE
REPLICATE
RIGHT
RTRIM
SPACE
STUFF
SUBSTRING
UCASE or UPPER
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
CURRENT_DATE
CURTIME
CURRENT_TIME
CURRENT_TIMESTAMP
DAYNAME
DAYOFMONTH
DAYOFYEAR
EXTRACT
HOUR
MINUTE
MONTH
MONTHNAME
NOW
QUARTER
SECOND
TIMESTAMPADD
TIMESTAMPDIFF
WEEK
YEAR
DATABASE
USER
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
where:
table-reference is a table name and search-condition is the join condition you want to use for the tables.
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 ADO.NET Data Provider supports the following outer join escape sequences as supported by Zen 9.x and higher:
Left outer joins
Right outer joins
Full outer joins