PREVNEXTINDEX

A SQL Escape Sequences

Language features, such as outer joins and scalar function calls, are commonly implemented by database systems. The syntax for these features is often database-specific, even when a standard syntax has been defined. ODBC and JDBC define escape sequences that contain standard syntaxes for the language features in Table A-1.

NOTE: The .NET data provider supports ODBC/JDBC SQL escape sequences, except where otherwise noted.

Table A-1. Language Features 

Language Feature
SequeLink Client for ODBC
SequeLink Client for JDBC
SequeLink
Client for .NET
Date, time, and timestamp literals
X
X
X
Scalar functions such as numeric, string, and date type conversion functions
X
X
X
LIKE predicate escape characters
X
X
 
Outer joins
X
X
X
Procedure call escape sequences
X
X
 

The escape sequence is recognized and parsed by the driver or 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
yyy-mm-dd
t
Time
hh:mm:ss
ts
Timestamp
yyyy-mm-dd hh:mm:ss[.f...]

Example: 

UPDATE Orders SET OpenDate={d '1995-01-15'}  
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 ODBC driver, JDBC driver, and .NET data provider, as shown in the following tables.

scalar-function

Example: 

SELECT {fn UCASE(NAME)} FROM EMP

Table A-2. Scalar Functions Supported on DB2 

Data Store
String
Functions
Numeric Functions
Timedate Functions
System Functions
DB2 UDB on z/OS
CHAR
CONCAT
DIFFERENCE
INSERT
LCASE
LEFT
LENGTH
LOCATE
LOCATE_2
LTRIM
REPEAT
REPLACE
RIGHT
RTRIM
SOUNDEX
SPACE
SUBSTRING
UCASE
ABS
ACOS
ASIN
ATAN
ATAN2
CEILING
COS
DEGREES
EXP
FLOOR
LOG
LOG10
MOD
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
TAN
TRUNCATE
CURDATE
CURRENT_DATE
CURTIME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
HOUR
MINUTE
MONTH
NOW
SECOND
WEEK
YEAR
DBNAME
IFNULL
USERNAME
DB2 UDB on Windows and UNIX
ASCII
CHAR
CONCAT
DIFFERENCE
INSERT
LCASE
LEFT
LENGTH
LOCATE
LOCATE_2
LTRIM
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
CURRENT_DATE
CURTIME
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
HOUR
MINUTE
MONTH
MONTHNAME
NOW
QUARTER
SECOND
TIMESTAMPADD
TIMESTAMPDIFF
WEEK
YEAR
DATABASE
IFNULL
USERNAME

Table A-3. Scalar Functions Supported on Informix 

Data Store
String
Functions
Numeric Functions
Timedate Functions
System Functions
Informix
BIT_LENGTH
CHAR_LENGTH
CONCAT
LENGTH
LTRIM
RTRIM
STR_LENGTH
ABS
ACOS
ASIN
ATAN
ATAN2
COS
COT
EXP
FLOOR
LOG
LOG10
MOD
POWER
ROUND
SQRT
TAN
TRUNCATE
CURDATE
CURRENT DATE
CURTIME
DAYOFMONTH
DAYOFWEEK
MONTH
NOW
QUARTER
YEAR
DBNAME
USERNAME

Table A-4. Scalar Functions Supported on Microsoft SQL Server 

Data Store
String
Functions
Numeric Functions
Timedate Functions
System Functions
Microsoft
SQL Server
ASCII
BITLENGTH
CHAR
CONCAT
DIFFERENCE
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
DEGREES
EXP
FLOOR
LOG
LOG10
MOD
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
TAN
TRUNCATE
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_ TIMESTAMP
CURTIME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
DAYNAME
EXTRACT
HOUR
MINUTE
MONTH
MONTHNAME
NOW
QUARTER
SECOND
TIMESTAMPADD
TIMESTAMPDIFF
WEEK
YEAR
DBNAME
IFNULL
USERNAME

Table A-5. Scalar Functions Supported on Oracle 

Data Store
String
Functions
Numeric 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
CURRENT_DATE
CURRENT_TIME
CURRENT TIMESTAMP
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
DAYNAME
HOUR
MINUTE
MONTH
MONTHNAME
NOW
QUARTER
SECOND
TIMESTAMP_ADD
TIMESTAMP_DIFF
WEEK
YEAR
IFNULL
USER
USERNAME

Table A-6. Scalar Functions Supported on Sybase

Data Store
String
Functions
Numeric Functions
Timedate Functions
System Functions
Sybase
ASCII
CHAR
CONCAT
DIFFERENCE
INSERT
LCASE
LEFT
LENGTH
LOCATE1
LTRIM
REPEAT
RIGHT
RTRIM
SOUNDEX
SPACE
SUBSTRING
UCASE
ABS
ACOS
ASIN
ATAN
ATAN2
CEILING
COS
COT
EXP
FLOOR
LOG
LOG10
MOD
NUM_DEGREES
NUM_RADIANS
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
TAN
TRUNCATE
CURDATE
CURRENT_DATE
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
DAYNAME
HOUR
MINUTE
MONTH
MONTHNAME
NOW
QUARTER
SECOND
TIMESTAMPADD
TIMESTAMPDIFF
WEEK
YEAR
DATABASE
DBNAME
IFNULL
USER
USERNAME

String Functions

Table A-7 lists string functions. The following arguments can be used with these functions:

The string functions are one-based; that is, the first character in the string is the character 1. Character string literals must be enclosed by single quotation marks.

Table A-7. Scalar String Functions 
Function
Returns
ASCII(string_exp)
The ASCII code of the leftmost character of string_exp as an integer.
BIT_LENGTH(string_exp)
The length, in bits, of the string expression.
CHAR(code)
The character with the ASCII code specified by code. code should be between 0 and 255; otherwise, the return value depends on the data source.
CHAR_LENGTH(string_exp)
The length, in characters, of the string expression, when the string expression is a character data type; otherwise, the length, in bytes, of the string expression (the lowest integer that is not less than the number of bits divided by 8). (This function is the same as the CHARACTER_LENGTH function.)
CHARACTER_LENGTH(string_exp)
The length, in characters, of the string expression, when the string expression is a character data type; otherwise, the length, in bytes, of the string expression (the lowest integer that is not less than the number of bits divided by 8). (This function is the same as the CHAR_LENGTH function.)
CONCAT(string_exp1, string_exp2)
The string resulting from concatenating string_exp2 and string_exp1. The string is system dependent.
DIFFERENCE(string_exp1, string_exp2)
An integer indicating the difference between the values returned by the SOUNDEX function for string_exp1 and string_exp2.
INSERT(string_exp1, start, length, string_exp2)
A string where length characters have been deleted from string_exp1 beginning at start and where string_exp2 has been inserted into string_exp beginning at start.
LCASE(string_exp)
Uppercase characters in string_exp converted to lowercase.
LEFT(string_exp,count)
The count of characters of string_exp.
LENGTH(string_exp)
The number of characters in string_exp, excluding trailing blanks and the string termination character.
LOCATE(string_exp1, string_exp2[,start])
The starting position of the first occurrence of string_exp1 in string_exp2. If start is not specified, the search begins with the first character position in string_exp2. If start is specified, the search begins with the character position indicated by start. The first character position in string_exp2 is indicated by 1. If string_exp1 is not found, 0 is returned.
LTRIM(string_exp)
The characters of string_exp, with leading blanks removed.
OCTET_LENGTH(string_exp)
The length, in bytes, of the string expression. The result is the lowest integer that is not less than the number of bits divided by 8.
POSITION(character_exp IN character_exp)
The position of the first character expression in the second character expression. The result is a numeric with an implementation-defined precision and a scale of 0.
REPEAT(string_exp, count)
A string composed of string_exp repeated count times.
REPLACE(string_exp1, string_exp2, string_exp3)
Replaces all occurrences of string_exp2 in string_exp1 with string_exp3.
RIGHT(string_exp, count)
The rightmost count of characters in string_exp.
RTRIM(string_exp)
The characters of string_exp with trailing blanks removed.
SOUNDEX(string_exp)
A data-source dependent string representing the sound of the words in string_exp.
SPACE(count)
A string consisting of count spaces.
SUBSTRING(string_exp, start, length)
A string derived from string_exp, beginning at the character position start for length characters.
UCASE(string_exp)
Lowercase characters in string_exp converted to uppercase.

Numeric Functions

Table A-8 lists numeric functions. The following arguments can be used with numeric functions:

Table A-8. Scalar Numeric Functions 
Function
Returns
ABS(numeric_exp)
Absolute value of numeric_exp.
ACOS(float_exp)
Arccosine of float_exp as an angle in radians.
ASIN(float_exp)
Arcsine of float_exp as an angle in radians.
ATAN(float_exp)
Arctangent of float_exp as an angle in radians.
ATAN2(float_exp1, float_exp2)
Arctangent of the x and y coordinates, specified by float_exp1 and float_exp2 as an angle in radians.
CEILING(numeric_exp)
Smallest integer greater than or equal to numeric_exp.
COS(float_exp)
Cosine of float_exp as an angle in radians.
COT(float_exp)
Cotangent of float_exp as an angle in radians.
DEGREES(numeric_exp)
Number if degrees converted from numeric_exp radians.
EXP(float_exp)
Exponential value of float_exp.
FLOOR(numeric_exp)
Largest integer less than or equal to numeric_exp.
LOG(float_exp)
Natural log of float_exp.
LOG10(float_exp)
Base 10 log of float_exp.
MOD(integer_exp1, integer_exp2)
Remainder of integer_exp1 divided by integer_exp2.
PI()
Constant value of pi as a floating-point number.
POWER(numeric_exp, integer_exp)
Value of numeric_exp to the power of integer_exp.
RADIANS(numeric_exp)
Number of radians converted from numeric_exp degrees.
RAND([integer_exp])
Random floating-point value using integer_exp as the optional seed value.
ROUND(numeric_exp, integer_exp)
numeric_exp rounded to integer_exp places right of the decimal (left of the decimal if integer_exp is negative).
SIGN(numeric_exp)
Indicator of the sign of numeric_exp.
If numeric_exp < 0, -1 is returned.
If numeric_exp = 0, 0 is returned.
If numeric_exp > 0, 1 is returned.
SIN(float_exp)
Sine of float_exp, where float_exp is an angle in radians.
SQRT(float_exp)
Square root of float_exp.
TAN(float_exp)
Tangent of float_exp, where float_exp is an angle in radians.
TRUNCATE(numeric_exp, integer_exp)
numeric_exp truncated to integer_exp places right of the decimal. (If integer_exp is negative, truncation is to the left of the decimal.)

Date and Time Functions

Table A-9 lists date and time functions. The following arguments can be used with the date and time functions:

Table A-9. Scalar Time and Date Functions 
Function
Returns
CURDATE()
Current date as a date value.
CURRENT_DATE()
Current date.
CURRENT_TIME[(time-precision)]
Current local time. The time-precision argument determines the seconds precision of the returned value.
CURRENT_TIMESTAMP[(timestamp- precision)]
Current local date and local time as a timestamp value. The timestamp-precision argument determines the seconds precision of the returned timestamp.
CURTIME()
Current local time as a time value.
DAYNAME(date_exp)
Character string containing a data-source-specific name of the day for the day portion of date_exp.
DAYOFMONTH(date_exp)
Day of the month in date_exp as an integer value (1-31).
DAYOFWEEK(date_exp)
Day of the week in date_exp as an integer value
(1-7).
DAYOFYEAR(date_exp)
Day of the year in date_exp as an integer value
(1-366).
EXTRACT(extract-field FROM extract-source)
Extract-field portion of the extract-source. The extract-source argument is a datetime or interval expression. The extract-field argument can be one of the following keywords:
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
The precision scale of the returned value is 0 unless SECOND is specified, in which case, the scale is not less than the fractional seconds precision of the extract-source field.
HOUR(time_exp)
Hour in time_exp as an integer value (0-23).
MINUTE(time_exp)
Minute in time_exp as an integer value (0-59).
MONTH(date_exp)
Month in date_exp as an integer value (1-12).
MONTHNAME(date_exp)
Character string containing the data source-specific name of the month.
NOW()
Current date and time as a timestamp value.
QUARTER(date_exp)
Quarter in date_exp as an integer value (1-4).
SECOND(time_exp)
Second in time_exp as an integer value (0-59).
TIMESTAMPADD(interval, integer_exp, time_exp)
Timestamp calculated by adding integer_exp intervals of type interval to time_exp. interval can be one of the following values:
SQL_TSI_FRAC_SECOND
SQL_TSI_SECOND
SQL_TSI_MINUTE
SQL_TSI_HOUR
SQL_TSI_DAY
SQL_TSI_WEEK
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_YEAR
Fractional seconds are expressed in billionths of a second.
TIMESTAMPDIFF(interval, time_exp1, time_exp2)
Integer number of intervals of type interval by which time_exp2 is greater than time_exp1. interval has the same value as TIMESTAMPADD. Fractional seconds are expressed in billionths of a second.
WEEK(date_exp)
Week of the year in date_exp as an integer value (1-53).
YEAR(date_exp)
Year in date_exp. The range is data-source dependent.

System Functions

Table A-10 lists system functions.

Table A-10. Scalar System Functions
Function
Returns
DATABASE()
Name of the database, corresponding to the connection handle (hdbc).
IFNULL(exp,value)
value, if exp is null.
USER()
Authorization name of the user.

Like Predicate Escape Characters

In a LIKE predicate, the percent sign (%) matches zero or more of any character and the underscore (_) matches any one character. To match an actual percent sign or underscore in a LIKE predicate, an escape character must precede the % or _. The escape sequence that defines the LIKE predicate escape character is: 

{escape 'escape-character'}

where escape-character is any character supported by the data source.

Example: 

SELECT Name FROM Customers
WHERE Name LIKE '\%AAA%' {escape '\'}

Returns all the customers for which the name starts with "%AAA".

Outer Join Escape Sequences

ODBC and JDBC support 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'

Table A-11 lists the outer join escape sequences supported by SequeLink for each data store.

Table A-11. Outer Join Escape Sequences Supported 
Data Store
Outer Join Escape Sequences
DB2 on z/OS
Left outer joins
Right outer joins
Full outer joins
Nested outer joins
Inner outer joins
DB2 on Windows and UNIX
Left outer joins
Right outer joins
Full outer joins
Nested outer joins
Unordered outer joins
Inner outer joins
Informix
Left outer joins
Unordered outer joins
Microsoft SQL Server
Left outer joins
Right outer joins
Full outer joins
Nested outer joins

Procedure Call Escape Sequences

A procedure is an executable object stored in the data store. Generally, it is one or more SQL statements that have been precompiled.

The ODBC and JDBC drivers use the following escape sequence for calling a procedure: 

{[?=]call procedure-name[([parameter][,[parameter]]...)]}

where:

procedure-name is the name of a stored procedure. You can call stored procedures with or without the schema name qualification.

parameter is a stored procedure parameter.

PREVNEXTINDEX