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