Interactive SQL supports JDBC escape syntax. This escape syntax 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 }}
The braces must be doubled. 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. The escape syntax cannot be used 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 that includes number, string, time, date, and system functions.
For example, to obtain the name of the current user in a database management system-neutral way, you would type the following:
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 system 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 the following in the SQL Statements pane in Interactive SQL:
((CANN sa_db_info(1)))
