Boost logo

Boost :

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 :-)


Boost list run by bdawes at, gregod at, cpdaniel at, john at