Will {fn concat} work without issue in MySQL, SQL Server and Oracle?

Question

According to this (quite old) article the principle behind ODBC escape sequences is that

The ODBC driver reads the escape sequence and translates it into the DBMS-specific syntax before sending the query to the database

However if I try the following

SQL Server

CREATE PROCEDURE foo_bar
AS
BEGIN
  SELECT  {fn concat ('foo', 'bar')};
END

My SQL

DELIMITER $$

CREATE PROCEDURE foo_bar()
BEGIN
  SELECT  {fn concat ('foo', 'bar')};
END

The escape sequences remain untranslated in the object definition and calling the routines works fine. That indicates to me that the RDBMSs themselves natively understand the syntax rather than relying on translation so will work independent of connection method. Is that understanding correct and if so does this also apply to Oracle?

Answer 1

This seems to work only through a JDBC connection. Your SELECT works with Oracle, PostgreSQL and DB2 when using a Java/JDBC based query tool.

Using the native tools this statement throws an error:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and OLAP options

SQL> SELECT  {fn concat ('foo', 'bar')} from dual;
SELECT  {fn concat ('foo', 'bar')} from dual
        *
ERROR at line 1:
ORA-00911: invalid character

The same is true for PostgreSQL:

psql (9.1.1)
Type "help" for help.

postgres=> SELECT  {fn concat ('foo', 'bar')};
ERROR:  syntax error at or near "{"
LINE 1: SELECT  {fn concat ('foo', 'bar')};

and DB2:

(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.7.0

db2 => SELECT  {fn concat ('foo', 'bar')} from sysibm.sysdummy1;
SQL0104N  An unexpected token "SELECT  {" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<values>".
SQLSTATE=42601