The end result is that either you write a separate statement for each database vendor you intend to support, or you relinquish this efficient and useful function altogether.
Another problem with SQL portability is the issue of auto-incrementing fields. This feature is supported by some modern databases such as MS SQL, MySQL and Sybase, but it is not part of the SQL-92 specification. Subsequently, if you plan to support databases that don’t natively use auto-increment fields, a contingency must be established. This usually involves using a separate sequence table for each table requiring unique identifiers.
Using a database abstraction layer
Limited select statements and auto-incrementing fields. These are just two very common examples of how the ideal of database portability can be undermined by the need for optimisation and efficient design. The most common solution to this problem is the use of a database abstraction layer (DAL). At the most basic level, a DAL provides a model for manipulating data that originates in a database. Often object oriented design is used to create DAL classes that map to physical or logical abstractions of the data. Using classes or arrays, a DAL provides a means for a data driven application to work with disconnected data sets in a more efficient manner. This partially solves situations where queries need to be refined by limits or other filters, but it puts extra load on the application. Instead, a good DAL will provide access to vendor-specific functions where applicable while providing alternate mechanisms for the other supported DBMS systems.
Select limiting
Take, for instance, the ADOdb library for PHP. This DAL exposes a function called SelectLimit() which works like a black box. This function accepts three parameters - a SQL query, the row number of the first record to retrieve and the number of rows to retrieve altogether. Whether you are working with MySQL, SQL Server, Oracle Informix or any other supported database, the result will be identical. If the database being used supports query limiting then this feature will be utilised by ADOdb. Otherwise, ADOdb will simply discard the records at the beginning and/or end of the record set to meet the requirements. The end result is identical, but the application will be able to leverage the features of the database where applicable. So long as you know the syntax requirements for each of your supported databases, the DAL merely has to use the one applicable to the currently configured vendor. Here’s an example of how you might achieve this:
function Select ($sql, $start = 1, $records = 0){
global $dbtype;
$rs = array();
switch ($dbtype){
case “MSSQL”:
$rs = execute_query($sql);
if ($start > 1){
array_slice($rs, $start);
}
if ($records > 0){
array_slice($rs, 0, $records);
}
Break;
case “MySql”:
$rs = execute_query($sql . “ “ . $start . $records);
break;
}
return $rs;
}