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

From: Thorsten Ottosen (thorsten.ottosen_at_[hidden])
Date: 2007-03-16 17:25:07

Rene Rivera wrote:
>>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.
> 
> 
> [snip]
> 
> Sure... But we could use that same argument to propose that we write C++ 
> programs by writing an abstraction on top of C++ compilers in Python so 
> that we can remove the incompatibilities in the various C++ 
> implementations. 

I guess you could do that. Like C++, porting can be a major problem.

Note, however, that is not an argument against having portible SQL DSL.
Some C++ coders actually prefer to use a portable subset (STLSOFT libs 
for one).

>Having had the displeasure of dealing with PL/SQL there 
> is one serious drawback to the language embedded approach, it ties ones 
> to the capabilities of the abstraction.

Right. The SQL library should provide a backdoor so you can still use
string queries if you need to be non-portable or cutting egde etc.

I some of our company tools, we support multiple server backends: at 
least oracle, mssql and mysql. I'm surpised just how diffucult it is to
write portable SQL (even for very very simple queries).

If you want to use different functions, there's a pretty good change 
they are different on different systems. I have end up writing my own 
abstractionlayer in php to compensate.

Here's some of the functions I had to provide my own abstraction around:

//   // encapsulate sql differences
//   locateSyntax( $substr, $str );
//   inverseLocateSyntax( $substr, $str );
//   lastIdSyntax( $seq = "" );
//   lengthSyntax();
//   timeSyntax();
//   timeExpiredSyntax();
//   replaceWildcard( $value );
//   toupperSyntax( $column );
//   maxSyntax( $column );
//   sumSyntax( $column );
//   countSyntax( $column );
//   limitSelectSyntax( $limit, $queryWithoutSelect );
//   dropIfExistsSyntax( $table );
//   createEmptyTableCopySyntax( $oldTable, $newTable );
//   renameTableSyntax( $oldTable, $newTable );
//   escapedStringSyntax( $string );
//   insertBlobSyntax( $table, $column, $id, $data );
//   deEscapedStringSyntax( $string );
//   unixtimeToDateSyntax( $column );
//   nullDateSyntax();

It's a nightmare :-)

-Thorsten