Stored Procedure

The escape syntax to call a stored procedure is:

{call <procedure_name> [(<argument-list>)]}
{call procedure_name (?, ?)}

or, where a procedure returns a result parameter:

{? = call <procedure_name> [(<argument-list>)]}
{? = call procedure_name (?, ?)}

Scalar function

The escape syntax to access a scalar function is:

{fn <function-name> (argument list)}

For example,

• concat with two arguments to be concatenated:

-- called as procedure
{fn concat("Hello", " ", "World")}
-- called with a select
select {fn concat("Hello", " ", "World")}

• Gets the name of the current database user:

-- called as procedure
{fn user()}
-- called with a select
select {fn user()}

• Substring

{fn SUBSTRING(text, 1, 10)}

• Upper

{fn upper( ? ) }

• All other functions are listed in the JDBC specification section Scalar function

They get translated into database-specific functions

"Hello" || " " || "World" -- Oracle concat
SUBSTR(text, 1, 10) -- Oracle substring
"Hello" + " " + "World" -- SQL Server concat
SUBSTRING(text, 1, 10) -- SQL Server substring

Date/Time literals

{d 'yyyy-mm-dd'} -- date
{t 'hh:mm:ss'} -- time
{ts 'yyyy-mm-dd hh:mm:ss.f...'}  -- timestamp

Example:

select {d '2023-12-25'}        // Date
select {t '14:30:00'}          // Time  
select {ts '2023-12-25 14:30:00'} // Timestamp

This syntax gets translated to database-specific format

DATE '2023-12-25' -- Oracle Date
'2023-12-25' -- SQL Server Date

Outer Join

The escape syntax for an outer join is:

{oj <outer-join>}

where outer-join has the form:

table {LEFT|RIGHT|FULL} OUTER JOIN {table | <outer-join>} ON search-condition

Example:

SELECT * FROM {oj TABLE1 LEFT OUTER JOIN TABLE2 ON DEPT_NO = 003420930}

LIKE Escape

The following syntax can be used at the end of a LIKE predicate:

{escape '<escape-character>'}

Example:

SELECT name FROM Identifiers WHERE Id LIKE '\\_%' {escape '\\'}

Limit Escape

The escape syntax for limiting the number of rows returned by a query is:

{limit <limit clause>}

where the format for the limit clause is:

rows [offset row_offset]

Example:

SELECT * FROM TABLE1 WHERE F1 >100 {limit 20}