I don’t prefer using an ODBC datasource, but sometimes it is the easiest way to gain access to a database. Often times when using frameworks(php, not sure about others) an odbc option is available, however who knows if it will deliver sql compatible with the database we’re trying to gain access to. I’ll base this brainstorm on php, though it should relate across many different languages.
We all know that for the most part with a framework if you specify to use MySQL, or SQLite, or SQL Server it will generate the correct query to pull in your data. But what happens when you’re using ODBC? Most frameworks I’ve fiddled with(maybe some are different, I’d like to hear about their method or process) only allow you to specify ODBC, but not what database is being accessed, so how does it know how to build the query? Should there be a syntax abstraction layer similar to the current database connection layer? So maybe in database configs you might specify something like the following:
$db_config = array( "type"=>"ODBC", "syntax"=>"mssql", "username"=>"myusername", "password"=>"password", "database"=>"somedb" );
Same could be true for a PDO connection or ADO. Of course if just a MySQL connection is specified it would know to pull the MySQL syntax class, but this becomes particularly useful in these situations. Now this adds to the flexibility of many environments, as long as you have an ODBC connection you only need a general syntax file, mainly specifying specific differences in standard sql, like using LIMIT vs. TOP. Or you could more easily implement regex within queries like I’ve blogged about in the past.