ODBC escape syntax

You can use ODBC escape syntax from any ODBC application. This escape syntax allows you to call a set of common functions regardless of the database management system you are using. The general form for the escape syntax is

{ keyword parameters }

The set of keywords includes the following:

{d date-string} The date string is any date value accepted by SQL Anywhere.
{t time-string} The time string is any time value accepted by SQL Anywhere.
{ts date-string time-string} The date/time string is any timestamp value accepted by SQL Anywhere.
{guid uuid-string} The uuid-string is any valid GUID string, for example, 41dfe9ef-db91-11d2-8c43-006008d26a6f.
{oj outer-join-expr} The outer-join-expr is a valid OUTER JOIN expression accepted by SQL Anywhere.
{? = call func(p1,...)} The function is any valid function call accepted by SQL Anywhere.
{call proc(p1,...)} The procedure is any valid stored procedure call accepted by SQL Anywhere.
{fn func(p1,...)} The function is any one of the library of functions listed below.

You can use the escape syntax to access a library of functions implemented by the ODBC driver that includes number, string, time, date, and system functions.

For example, to obtain the current date in a database management system-neutral way, you would execute the following:

SELECT { FN CURDATE() }

The following tables list the functions that are supported by the SQL Anywhere ODBC driver.

SQL Anywhere ODBC driver supported functions

Numeric functions	String functions	System functions	Time/date functions
ABS	ASCII	DATABASE	CURDATE
ACOS	BIT_LENGTH	IFNULL	CURRENT_DATE
ASIN	CHAR	USER	CURRENT_TIME
ATAN	CHAR_LENGTH	CONVERT	CURRENT_TIMESTAMP
ATAN2	CHARACTER_LENGTH		CURTIME
CEILING	CONCAT		DAYNAME
COS	DIFFERENCE		DAYOFMONTH
COT	INSERT		DAYOFWEEK
DEGREES	LCASE		DAYOFYEAR
EXP	LEFT		EXTRACT
FLOOR	LENGTH		HOUR
LOG	LOCATE		MINUTE
LOG10	LTRIM		MONTH
MOD	OCTET_LENGTH		MONTHNAME
PI	POSITION		NOW
POWER	REPEAT		QUARTER
RADIANS	REPLACE		SECOND
RAND	RIGHT		WEEK
ROUND	RTRIM		YEAR
SIGN	SOUNDEX
SIN	SPACE
SQRT	SUBSTRING
TAN	UCASE
TRUNCATE

The ODBC escape syntax is identical to the JDBC escape syntax. In Interactive SQL, which uses JDBC, the braces must be doubled. There must not be a space between successive braces: "{{" is acceptable, but "{ {" is not. As well, you cannot use newline characters in the statement. The escape syntax cannot be used in stored procedures because they are not parsed by Interactive SQL.

For example, to obtain database properties with the sa_db_info procedure using SQL escape syntax, you would execute the following in Interactive SQL:

{{CALL sa_db_info( 0 ) }}

Discuss this page in DocCommentXchange.