C. 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:
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
The following table lists the scalar functions supported.
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
Last modified date: 11/04/2024