Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 3 down vote accepted

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
share|improve this answer
    
+1 Thanks for the comprehensive answer. BTW - Did you try creating a stored routine in any of those DBMSs? I presume in that instance the JDBC driver just rewrites it as || as the article I referenced suggests? –  Martin Smith Oct 16 '11 at 13:54
1  
No, I didn't create a stored procedure. If the statement does not work on it's own it will not work inside a stored procedure either –  a_horse_with_no_name Oct 16 '11 at 13:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.