How to use DELIMIDENT with IBM Informix ODBC Driver on Windows

Question

Here is the query we are trying to run inside of SQLRunner over an IBM Informix ODBC Driver on a Windows system. The DB itself is hosted elsewhere.

SELECT keycolumnA AS 'Entity', datacol1, datacol2 WHERE keycolumnA IN ('ABC', 'DE f')

The query fails with "ODBC Error"

However, if we remove the quotes from Entity, the query works but the column name is returned in lower case. It is important that the first column be returned as Entity with the uppercase E.

We have tried setting the connection string as "DSN=myDSN; DELIMIDENT=y;" without success.

Setup: IBM Informix Driver version: 3.7 on Windows 2003.

Jonathan Leffler Jonathan Leffler · Accepted Answer · 2012-10-12T17:27:19

To be a delimited identifier, the name must be in double quotes, not single quotes. So, with the connection string shown, writing the query as shown below 'should' work (famous last words):

SELECT keycolumnA AS "Entity", datacol1, datacol2
  FROM RandomlyNamedTable   -- NB: Not present in original query!
 WHERE keycolumnA IN ('ABC', 'DE f');

Tested on a database containing a table called Elements (storing data about the Table of Elements):

 SELECT Atomic_Number AS "Entity", Name AS Name, Symbol AS "Symbol"
  FROM Elements   -- NB: Not present in original query!
 WHERE Symbol IN ('Fe', 'He');

Entity  | name      | Symbol
INTEGER | CHAR(20)  | CHAR(3)
26      | Iron      | Fe
2       | Helium    | He

Note that the names 'Entity' and 'Symbol' are capitalized as you require. When the double quotes were replaced by single quotes, the query failed

SELECT Atomic_Number AS 'Entity', Name AS Name, Symbol AS 'Symbol'
  FROM Elements   -- NB: Not present in original query!
 WHERE Symbol IN ('Fe', 'He');
SQL -201: A syntax error has occurred.

This was with DELIMIDENT set...without DELIMIDENT set, both single quotes and double quotes around the 'display labels' failed with a syntax error.

Testing against IBM Informix Dynamic Server 11.70.FC2 on Mac OS X 10.7.5. (Program was written using ESQL/C, but that should be immaterial.)

How to use DELIMIDENT with IBM Informix ODBC Driver on Windows

1 Answers