Re: [boost] SQL library with full embedded SQL-syntax

From: Sebastian Redl (sebastian.redl_at_[hidden])
Date: 2007-03-16 11:04:22

Rene Rivera wrote:
> Thorsten Ottosen wrote:
>   
>> Portability is another reason to go away from sql queries as strings.
>>     
>
> Since SQL is a standard language I don't see how using it in strings 
> makes it non-portable. Could you explain you reasoning?
>   
Different DBs actually use different "dialects" of SQL. There is a
subset of SQL that can be used pretty portably, but also a large amount
of stuff that can't.
Example: quoting. Postgres (and I think Oracle) use single quotes for
strings, double quotes for identifiers. MySQL uses either double or
single quotes for strings and backticks for identifiers. MSSQL uses
single quotes for strings (not sure about double) and square brackets
for identifiers. Also, embedded quotes are escaped by a backslash in
some systems, by a double quote in others.
Data types have subtly different names. Oracle discourages the use of
the standard VARCHAR in favour of their own VARCHAR2, even though
they're aliases. ("But they might not be in the future!")
DDL is very poor in terms of standard support. Table definitions can
differ quite significantly between databases.

Hibernate solves this problem by having a "Dialect" class for every
database. This class is responsible for generating the DB-specific SQL.

Sebastian Redl