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

From: Jorge Lodos (lodos_at_[hidden])
Date: 2007-03-16 11:33:30

Sebastian Redl wrote:
> 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.
> 

Security is another reason to go away from sql queries as strings. 
Prevent SQL injection attacks.

Best regards
Jorge