RE: Upper Case in Informix

---------- Nicholas Palmer (nick@kcicorp.com) asked a simple-sounding question: >It seems that whenever I create a column in a table, either thru >dbaccess, or thru an ODBC program that I'm using, the column name always >ends up being lowercased. That is if I create a column named 'A', >either in dbaccess, or thru the client/server ODBC program, when I query >the table, I have to use 'a' as the column identifier and thru dbaccess >if I look at the info for the table, the column name is named 'a'. Is >there any way to get Informix to do uppercase? * Yes, it is possible (at least with DB-Access). * No, you don't want to do it. Why do you think you have to worry about it? You can write your SQL in whichever case you like and Informix will case-convert for you, normally. If your ODBC code manages to fail because it is in lower case, then your ODBC code - either your source or their implementation - is doing something pretty peculiar. I'd guess it is using delimited identifiers, somehow, and it really didn't ought to be. You might have to track down the actual SQL source which is being sent to the database engine by your ODBC program, which will be rather hard if the statements don't prepare, because you can't use SET EXPLAIN or the SysMaster database to track queries which don't prepare. * How do you do it? To create names with upper case letters, you need to let the programs and the database know that you want delimited identifiers (which you don't, but we'll provide the answer anyway). To indicate that you will use delimited identifiers, you set the DELIMIDENT variable in the environment (easy with DB-Access; might be harder with ODBC from a PC, which is one reason you don't want to do it). You then use the correct notation in your CREATE statements: CREATE TABLE "UPPER" ( "A" INTEGER NOT NULL, "B" VARCHAR(30) NOT NULL, [...] ); The only way to access this table is with the DELIMIDENT variable in the environment, and then run queries such as: SELECT "A", "B" FROM "UPPER" WHERE "A" > 100 AND "B" MATCHES 'A*'; Note carefully the use of double quotes around the identifiers and the single quotes around the character string. If you do it wrong - for example, if you wrote '...MATCHES "A*"', then you'd get an error -217 (no such column in any table in the query), because you have specified a column name of "A*" rather than a string. If you try running the statement without the double quotes around the capitalized identifiers, it won't work. In the past, I've shown examples of tables with punctuation and spaces in the table and column names - it still isn't a good idea. Lunacy and baldness will surely be close companions to anybody who tries to exploit delimited identifiers. Yours opinionatedly, Jonathan Leffler (jleffler@visa.com) #include <bother.ms-exchange.h>