Tips on Writing Portable SQL
Tips on Writing Portable SQL |
+ |
Updated 18 Sep 2003. Added Portable Native SQL section. +
+ + If you are writing an application that is used in multiple environments and + operating systems, you need to plan to support multiple databases. This article + is based on my experiences with multiple database systems, stretching from 4th + Dimension in my Mac days, to the databases I currently use, which are: Oracle, + FoxPro, Access, MS SQL Server and MySQL. Although most of the advice here applies + to using SQL with Perl, Python and other programming languages, I will focus on PHP and how + the ADOdb database abstraction library + offers some solutions.
+Most database vendors practice product lock-in. The best or fastest way to + do things is often implemented using proprietary extensions to SQL. This makes + it extremely hard to write portable SQL code that performs well under all conditions. + When the first ANSI committee got together in 1984 to standardize SQL, the database + vendors had such different implementations that they could only agree on the + core functionality of SQL. Many important application specific requirements + were not standardized, and after so many years since the ANSI effort began, + it looks as if much useful database functionality will never be standardized. + Even though ANSI-92 SQL has codified much more, we still have to implement portability + at the application level.
+The SELECT statement has been standardized to a great degree. Nearly every + database supports the following:
+SELECT [cols] FROM [tables]
+ [WHERE conditions]
+ [GROUP BY cols]
+ [HAVING conditions]
+ [ORDER BY cols]
But so many useful techniques can only be implemented by using proprietary + extensions. For example, when writing SQL to retrieve the first 10 rows for + paging, you could write...
+| Database | +SQL Syntax | +
| DB2 | +select * from table fetch first 10 rows only | +
| Informix | +select first 10 * from table | +
| Microsoft SQL Server and Access | +select top 10 * from table | +
| MySQL and PostgreSQL | +select * from table limit 10 | +
| Oracle 8i | +select * from (select * from table) where rownum <= 10 | +
This feature of getting a subset of data is so useful that in the PHP class + library ADOdb, we have a SelectLimit( ) function that allows you to hide the + implementation details within a function that will rewrite your SQL for you:
+$connection->SelectLimit('select * from table', 10);
+
+Selects: Fetch Modes
+PHP allows you to retrieve database records as arrays. You can choose to have + the arrays indexed by field name or number. However different low-level PHP + database drivers are inconsistent in their indexing efforts. ADOdb allows you + to determine your prefered mode. You set this by setting the variable $ADODB_FETCH_MODE + to either of the constants ADODB_FETCH_NUM (for numeric indexes) or ADODB_FETCH_ASSOC + (using field names as an associative index).
+The default behaviour of ADOdb varies depending on the database you are using. + For consistency, set the fetch mode to either ADODB_FETCH_NUM (for speed) or + ADODB_FETCH_ASSOC (for convenience) at the beginning of your code.
+Selects: Counting Records
+Another problem with SELECTs is that some databases do not return the number + of rows retrieved from a select statement. This is because the highest performance + databases will return records to you even before the last record has been found. +
+In ADOdb, RecordCount( ) returns the number of rows returned, or will emulate + it by buffering the rows and returning the count after all rows have been returned. + This can be disabled for performance reasons when retrieving large recordsets + by setting the global variable $ADODB_COUNTRECS = false. This variable is checked + every time a query is executed, so you can selectively choose which recordsets + to count.
+If you prefer to set $ADODB_COUNTRECS = false, ADOdb still has the PO_RecordCount( + ) function. This will return the number of rows, or if it is not found, it will + return an estimate using SELECT COUNT(*):
+$rs = $db->Execute("select * from table where state=$state");
+$numrows = $rs->PO_RecordCount('table', "state=$state");
+Selects: Locking
+SELECT statements are commonly used to implement row-level locking of tables. + Other databases such as Oracle, Interbase, PostgreSQL and MySQL with InnoDB + do not require row-level locking because they use versioning to display data + consistent with a specific point in time.
+Currently, I recommend encapsulating the row-level locking in a separate function, + such as RowLock($table, $where):
+$connection->BeginTrans( ); +$connection->RowLock($table, $where);+
# some operation+
if ($ok) $connection->CommitTrans( ); +else $connection->RollbackTrans( ); ++
Selects: Outer Joins
+Not all databases support outer joins. Furthermore the syntax for outer joins + differs dramatically between database vendors. One portable (and possibly slower) + method of implementing outer joins is using UNION.
+For example, an ANSI-92 left outer join between two tables t1 and t2 could + look like:
+SELECT t1.col1, t1.col2, t2.cola+
FROM t1 LEFT JOIN t2 ON t1.col = t2.col
This can be emulated using:
+SELECT t1.col1, t1.col2, t2.cola FROM t1, t2+
WHERE t1.col = t2.col + UNION ALL +SELECT col1, col2, null FROM t1
WHERE t1.col not in (select distinct col from t2) +
Since ADOdb 2.13, we provide some hints in the connection object as to legal + join variations. This is still incomplete and sometimes depends on the database + version you are using, but is useful as a general guideline:
+$conn->leftOuter: holds the
+ operator used for left outer joins (eg. '*='), or false if not known or not
+ available.
+ $conn->rightOuter: holds the
+ operator used for right outer joins (eg '=*'), or false if not known or not
+ available.
+ $conn->ansiOuter: boolean
+ that if true means that ANSI-92 style outer joins are supported, or false if
+ not known.
When you create records, you need to generate unique id's for each record. + There are two common techniques: (1) auto-incrementing columns and (2) sequences. +
+Auto-incrementing columns are supported by MySQL, Sybase and Microsoft Access + and SQL Server. However most other databases do not support this feature. So + for portability, you have little choice but to use sequences. Sequences are + special functions that return a unique incrementing number every time you call + it, suitable to be used as database keys. In ADOdb, we use the GenID( ) function. + It has takes a parameter, the sequence name. Different tables can have different + sequences.
+$id = $connection->GenID('sequence_name');
$connection->Execute("insert into table (id, firstname, lastname)
values ($id, $firstname, $lastname)");
+For databases that do not support sequences natively, ADOdb emulates sequences + by creating a table for every sequence.
+Binding variables in an SQL statement is another tricky feature. Binding is + useful because it allows pre-compilation of SQL. When inserting multiple records + into a database in a loop, binding can offer a 50% (or greater) speedup. However + many databases such as Access and MySQL do not support binding natively and + there is some overhead in emulating binding. Furthermore, different databases + (specificly Oracle!) implement binding differently. My recommendation is to + use binding if your database queries are too slow, but make sure you are using + a database that supports it like Oracle.
+ADOdb supports portable Prepare/Execute with:
+$stmt = $db->Prepare('select * from customers where custid=? and state=?');
+$rs = $db->Execute($stmt, array($id,'New York'));
+Oracle uses named bind placeholders, not "?", so to support portable binding, we have Param() that generates +the correct placeholder (available since ADOdb 3.92): +
$sql = 'insert into table (col1,col2) values ('.$DB->Param('a').','.$DB->Param('b').')';
+# generates 'insert into table (col1,col2) values (?,?)'
+# or 'insert into table (col1,col2) values (:a,:b)'
+$stmt = $DB->Prepare($sql);
+$stmt = $DB->Execute($stmt,array('one','two'));
+
+
+ADOdb provides the following functions for portably generating SQL functions + as strings to be merged into your SQL statements (some are only available since + ADOdb 3.92):
+| Function | +Description | +
| DBDate($date) | +Pass in a UNIX timestamp or ISO date and it will convert it to a date + string formatted for INSERT/UPDATE | +
| DBTimeStamp($date) | +Pass in a UNIX timestamp or ISO date and it will convert it to a timestamp + string formatted for INSERT/UPDATE | +
| SQLDate($date, $fmt) | +Portably generate a date formatted using $fmt mask, for use in SELECT + statements. | +
| OffsetDate($date, $ndays) | +Portably generate a $date offset by $ndays. | +
| Concat($s1, $s2, ...) | +Portably concatenate strings. Alternatively, for mssql use mssqlpo driver, + which allows || operator. | +
| IfNull($fld, $replaceNull) | +Returns a string that is the equivalent of MySQL IFNULL or Oracle NVL. | +
| Param($name) | +Generates bind placeholders, using ? or named conventions as appropriate. | +
| $db->sysDate | Property that holds the SQL function that returns today's date | +
| $db->sysTimeStamp | Property that holds the SQL function that returns the current +timestamp (date+time). + | +
| $db->concat_operator | Property that holds the concatenation operator + | +
| $db->length | Property that holds the name of the SQL strlen function. + |
| $db->upperCase | Property that holds the name of the SQL strtoupper function. + |
| $db->random | Property that holds the SQL to generate a random number between 0.00 and 1.00. + | +
| $db->substr | Property that holds the name of the SQL substring function. + |
+
+However if you prefer to use a PHP-based table creation scheme, adodb provides you with this feature. Here is the code to generate the SQL to create a table with: +
+Also create a compound index consisting of 'NAME' and 'AGE': +
+$datadict = NewDataDictionary($connection);
+$flds = "
+ ID I AUTOINCREMENT PRIMARY,
+ NAME C(32) DEFAULT '' NOTNULL,
+ CREATED T DEFTIMESTAMP,
+ AGE N(16) DEFAULT 0
+";
+$sql1 = $datadict->CreateTableSQL('tabname', $flds);
+$sql2 = $datadict->CreateIndexSQL('idx_name_age', 'tabname', 'NAME,AGE');
+
+
+Stick to a few data types that are available in most databases. Char, varchar + and numeric/number are supported by most databases. Most other data types (including + integer, boolean and float) cannot be relied on being available. I recommend + using char(1) or number(1) to hold booleans.
+Different databases have different ways of representing dates and timestamps/datetime. + ADOdb attempts to display all dates in ISO (YYYY-MM-DD) format. ADOdb also provides + DBDate( ) and DBTimeStamp( ) to convert dates to formats that are acceptable + to that database. Both functions accept Unix integer timestamps and date strings + in ISO format.
+$date1 = $connection->DBDate(time( ));+
$date2 = $connection->DBTimeStamp('2002-02-23 13:03:33');
We also provide functions to convert database dates to Unix timestamps:
+$unixts = $recordset->UnixDate('#2002-02-30#'); # MS Access date => unix timestamp
+The maximum length of a char/varchar field is also database specific. You can + only assume that field lengths of up to 250 characters are supported. This is + normally impractical for web based forum or content management systems. You + will need to be familiar with how databases handle large objects (LOBs). ADOdb + implements two functions, UpdateBlob( ) and UpdateClob( ) that allow you to + update fields holding Binary Large Objects (eg. pictures) and Character Large + Objects (eg. HTML articles):
+# for oracle
+$conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1,empty_blob())');
+$conn->UpdateBlob('blobtable','blobcol',$blobvalue,'id=1');
+
+# non-oracle databases
+$conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1, null)');
+$conn->UpdateBlob('blobtable','blobcol',$blobvalue,'id=1');
+
+Null handling is another area where differences can occur. This is a mine-field, + because 3-value logic is tricky. +
In general, I avoid using nulls except for dates and default all my numeric + and character fields to 0 or the empty string. This maintains consistency with + PHP, where empty strings and zero are treated as equivalent, and avoids SQL + ambiguities when you use the ANY and EXISTS operators. However if your database + has significant amounts of missing or unknown data, using nulls might be a good + idea. +
+ ADOdb also supports a portable IfNull function, so you can define what to display + if the field contains a null. +
Stored procedures are another problem area. Some databases allow recordsets + to be returned in a stored procedure (Microsoft SQL Server and Sybase), and + others only allow output parameters to be returned. Stored procedures sometimes + need to be wrapped in special syntax. For example, Oracle requires such code + to be wrapped in an anonymous block with BEGIN and END. Also internal sql operators + and functions such as +, ||, TRIM( ), SUBSTR( ) or INSTR( ) vary between vendors. +
+An example of how to call a stored procedure with 2 parameters and 1 return + value follows:
+ switch ($db->databaseType) {
+ case 'mssql':
+ $sql = 'SP_RUNSOMETHING'; break;
+ case 'oci8':
+ $sql =
+ "declare RETVAL integer;begin :RETVAL := SP_RUNSOMETHING(:myid,:group);end;";
+ break;
+ default:
+ die('Unsupported feature');
+ }
+ # @RETVAL = SP_RUNSOMETHING @myid,@group
+ $stmt = $db->PrepareSP($sql);
$db->Parameter($stmt,$id,'myid');
+ $db->Parameter($stmt,$group,'group');
+ # true indicates output parameter
$db->Parameter($stmt,$ret,'RETVAL',true);
+ $db->Execute($stmt);
+As you can see, the ADOdb API is the same for both databases. But the stored + procedure SQL syntax is quite different between databases and is not portable, + so be forewarned! However sometimes you have little choice as some systems only + allow data to be accessed via stored procedures. This is when the ultimate portability + solution might be the only solution: treating portable SQL as a localization + exercise...
+In general to provide real portability, you will have to treat SQL coding + as a localization exercise. In PHP, it has become common to define separate + language files for English, Russian, Korean, etc. Similarly, I would suggest + you have separate Sybase, Intebase, MySQL, etc files, and conditionally include + the SQL based on the database. For example, each MySQL SQL statement would be + stored in a separate variable, in a file called 'mysql-lang.inc.php'.
+$sqlGetPassword = 'select password from users where userid=%s'; +$sqlSearchKeyword = "SELECT * FROM articles WHERE match (title,body) against (%s)";+
In our main PHP file:
+# define which database to load...
+$database = 'mysql';
+include_once("$database-lang.inc.php");
+
+$db = &NewADOConnection($database);
+$db->PConnect(...) or die('Failed to connect to database');
+
+# search for a keyword $word
+$rs = $db->Execute(sprintf($sqlSearchKeyWord,$db->qstr($word)));
+Note that we quote the $word variable using the qstr( ) function. This is because + each database quotes strings using different conventions.
++
The best way to ensure that you have portable SQL is to have your data tables designed using +sound principles. Learn the theory of normalization and entity-relationship diagrams and model +your data carefully. Understand how joins and indexes work and how they are used to tune performance. +
Visit the following page for more references on database theory and vendors: + http://php.weblogs.com/sql_tutorial. + Also read this article on Optimizing PHP. +
+(c) 2002-2003 John Lim. + + + -- cgit v1.2.3