Portable SQL/Common/Quoting - Revision history

Nose: removed backlink

2008-06-22T13:06:23Z

removed backlink

Nose: Portable SQL - Quoting moved to Portable SQL/Common/Quoting

2007-05-17T14:26:26Z

Portable SQL - Quoting moved to Portable SQL/Common/Quoting

Nose: header hierarchy

2007-05-06T16:52:59Z

header hierarchy

Nose: complete content

2007-04-25T22:51:37Z

complete content

Nose: content split

2007-04-25T22:48:52Z

content split

New page

__TOC__

= 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 [[OpenDBX|Overview]]

← Older revision		Revision as of 13:06, 22 June 2008
Line 38:		Line 38:

	If the temp pointer doesn't point to the zero character of the end of the input string afterwards, then the application is sure that the input can't be used without risking serious harm.		If the temp pointer doesn't point to the zero character of the end of the input string afterwards, then the application is sure that the input can't be used without risking serious harm.
−
−
−	~~----~~
−	~~Back to [[OpenDBX\|Overview]]~~

← Older revision		Revision as of 16:52, 6 May 2007
Line 1:		Line 1:
	__TOC__		__TOC__
−
−
−	~~= Quoting in statements =~~

	== Quoting identifiers ==		== Quoting identifiers ==

← Older revision	Revision as of 14:26, 17 May 2007
(No difference)

@@ Line 1: / Line 1: @@
 __TOC__
 = Quoting in statements =
@@ Line 22: / Line 23: @@
 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 [[OpenDBX|Overview]]