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