Chapter 3: Using Interactive SQL in Graphics Mode

Using SQL escape syntax in Interactive SQL

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. Table 3-2 list the numeric functions that are supported by jConnect.

**Table 3-2: jConnect-supported numeric functions**
Function name
abs	cos	log10	sign
acos	cot	pi	sin
asin	degrees	power	sqrt
atan	exp	radians	tan
atan2	floor	rand
ceiling	log	round

Table 3-3 list the string functions that are supported by jConnect.

**Table 3-3: jConnect-supported string functions**
Numeric function names
ascii	difference	repeat	space
char	lcase	right	substring
concat	length	soundex	ucase

Table 3-4 list the system functions that are supported by jConnect.

**Table 3-4: jConnect-supported system functions**
System function names
database	ifnull	user	convert

Table 3-5 list the system functions that are supported by jConnect.

**Table 3-5: jConnect-supported system functions**
System function names
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)))

View this book as PDF