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, SAP ASE, 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)))