Column/table delimiters in Access 2007, Oracle, and SQL Server

The Problem

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.

The Question

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+)

Answers


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.


Need Your Help


About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.