Column/table delimiters in Access 2007, Oracle, and SQL Server
I'm trying to dynamically build a few SQL statements that need to use column/table delimiters so that we can have table names with two words, or special reserved keywords, etc. (I don't know, because the tables it maps to, will be existing data on the client-side)
When I was only supporting Access 2007 and SQL Server, I was using square brackets [ ]:
select [Unit Price] from [Products] where [Unit Price] < @UnitPriceLimit
But now I have to support Oracle too, and Oracle doesn't allow me to use the square brackets.
I've found Oracle does allow me to use double quotes:
select "Unit Price" from "Products" where "Unit Price" < @UnitPriceLimit
This is also supported in SQL Server, but not by default, only if you flip a certain option, quoted identifiers; and double quotes aren't at all supported (from what I can tell, maybe I'm missing an option?) in Access 2007.
Does anyone know if there is an identifier I can use cross-platform? Or at least common to all 3 of the platforms in question? (Oracle, Access 2007+, SQL Server 2008+)
You won't find one, unfortunately. You're best bet is to implement a decorator pattern and delimit the names as appropriate for each DBMS.