SQL Escape Sequences for .NET

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 PSQL 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 PSQL ADO.NET Data Provider.

Example

SELECT {fn UCASE(NAME)} FROM EMP

Table 65 lists the scalar functions supported.

Table 65 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 PSQL ADO.NET Data Provider supports the following outer join escape sequences as supported by PSQL 9.x and higher:

•Left outer joins

•Right outer joins

•Full outer joins