The SQL Escape Syntax in Interactive SQL

Interactive SQL supports JDBC escape syntax, which allows you to call stored procedures from Interactive SQL regardless of the database management system you are using.

The general form for the escape syntax is:

{{ keyword parameters }}

You must use double braces. This doubling is specific to Interactive SQL. There must not be a space between successive braces: "{{" is acceptable, but "{ {" is not. As well, you cannot use newline characters in the statement. You cannot use the escape syntax in stored procedures because they are not executed by Interactive SQL.

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

For example, to obtain the name of the current user in a database management system-neutral way, type:

select {{ fn user() }}

The functions that are available depend on the JDBC driver that you are using. The numeric functions that are supported by jConnect are:

abs	cos	log10	sign
acos	cot	pi	sin
asin	degrees	power	sqrt
atan	exp	radians	tan
atan2	floor	rand
ceiling	log	round

The string functions that are supported by jConnect are:

ascii	difference	repeat	space
char	lcase	right	substring
concat	length	soundex	ucase

The system functions that are supported by jConnect are:

database

ifnull

user

convert

The datetime functions that are supported by jConnect are:

curdate	dayofweek	monthname	timestampadd
curtime	hour	now	timestampdiff
dayname	minute	quarter	year
dayofmonth	month	second

A statement using the escape syntax should work in Adaptive Server Anywhere, Adaptive Server Enterprise, Oracle, SQL Server, or another database management system that you are connected to from Interactive SQL. For example, to obtain database properties with the sa_db_info procedure using SQL escape syntax, type this in the SQL Statements pane in Interactive SQL:

((CANN sa_db_info(1)))