Portable SQL/Common/Quoting

Quoting in statements

Quoting identifiers

Looking for good identifiers for tables, columns or views can often be a serious pain when your application should work with different database servers. There are a lot of reserved words which can't be used and each database vendor constantly extends his list. How can an application developer be sure that there will be no name clashes in the future? Similarly, databases tend to change the character case of identifiers. Some of them convert all to upper case, some to lower case while many preserve the case. If you want to access the value of a column by using the name instead of the position, things get really tricky.

To avoid both situations, the OpenDBX library forces all databases to operate in ANSI standard mode. Thus, they all support double quotes (") for quoting identifiers, e.g.

CREATE TABLE "order" ( "id" INTEGER, "limit" DECIMAL(10,2) )

SELECT o."limit" FROM "order" o WHERE o."id" = 1

Quoted identifiers can't clash anymore with reserved words (like "order" with ORDER BY) when they are quoted and they always keep their case. The disadvantage is that using them unquoted won't work any more in most cases.

The second possibility is to use unquoted names which are unlikely to get in conflict with reserved SQL words like so_limit

CREATE TABLE shop_order ( so_id INTEGER, so_limit DECIMAL(10,2) )

SELECT so.so_limit FROM shop_order so WHERE so.so_id = 1

This also works if other applications will access the tables which aren't using the OpenDBX library and don't adjust the connections to ANSI mode if necessary.

---

Back to Overview