Special Table and Column Names with ODBC API 1

[RichardRahl]

In most DBMS's I've used, you can create Tables and Columns with names
enclosed in quotes which can include special characters and is case
sensitive. Using SQL (both natively and from ODBC), you usually have
to include the quotes in the call (i.e. SELECT "I,D 1" FROM "I,AM A
Special Table"

This is fine, but when using SQLTables (from the ODBC API,

http://msdn2.microsoft.com/en-us/library/ms711831.aspx)

to get a list of tables, ODBC gives you NO indication that the table is "special", and needs quotes in its table name when used in SQL. The table name returned by

SQLTables contains all the special characters, but without the quotes!
This is a big problem. Does anyone know how to determine if a table or
column is a "special" (quoted) one?

Thanks!

 

[k5tm]

Of course, it isn't just special characters. You must also be concerned about tables that have names that conflict with the SQL grammar's reserved words.

Microsoft, in the ODBC Programmer's Reference, Volume I, Chapter 8, says:

To be safe, interoperable applications often quote all identifiers except those for psuedo-columns, such as ROWID column in Oracle. SQLSpecialColumns returns a list of pseudo-columns.

You can use SQLGetInfo to get the list of special characters, then use the characters thus returned to investigate a table name.

Tom Morrison

http://www.liant.com

 

[RichardRahl]

Hey, Thanks for replying!

Yes, that is what I am considering doing, but it isn't fool proof. Flagging a table name for special characters will only work as long as special characters are used in a quoted name. The quoted name "TEST" will not work, but also won't get flagged since there are no special characters even though its quoted. However, I suppose this would route out 90% or so, and the other 10% I could simply test to see if it needs the quotes. Somewhat of a hassle though .

 

[RichardRahl]

I feel like an idiot lol. You can simply quote everything, even if it's not quoted. It wasn't working before because
I was saying SELECT

"Special Table.Special Column" FROM "Special Table"

which kept not working on me....then I woke up and realized that the separator needs to be on the outside so SELECT "Special Table"."Special Column" FROM "Special Table" works fine, even if they aren't special.

 

[k5tm]

Glad you are over this hurdle!

 

[RichardRahl]

Alas, quoting everything seems to work fine for Oracle and PostgresSQL but does NOT function on MySQL....