Portable data types

Unfortunately, adapting SQL queries to the various syntaxes required by an application’s database types is only half of the problem. The other side of the coin is the need to massage data types into formats acceptable by the database in question. In particular, date and time data types are a regular cause of incompatibility. To get around this issue, it is a good idea to build data format functions into the database abstraction layer. Once again, ADOdb sheds some light on how this might be done effectively by providing the DBDate(), DBTimeStamp() and SQLDate() functions. These all accept UNIX timestamp values which are subsequently converted into the string format expected by the database in question. In the case of SQLDate(), it is also possible to specify a format mask to be used in GROUP BY clauses of SELECT queries.

In addition to date and time handling, there is also a portability issue regarding NULL values and empty strings. In particular, Oracle will equate an empty string with NULL, whereas MySQL differentiates between the two. To handle this discrepancy, it is necessary to write a function to convert NULL values to a string or vice versa, depending on the need at the time. In ADOdb, the routine is handled elegantly using the IfNull() method which mimics the behaviour of the function in MySQL with the same name.

function IfNull( $field, $ifNull )

{

  return “ CASE WHEN $field is null THEN $ifNull ELSE $field END “;

}

Although this function will assist with the handling of SELECT queries, it won’t solve data storage problems. Getting around this is simply a matter of design. If possible, avoid using NULL values except for dates. Instead, use 0 or empty strings for default values in other fields. This may incur a performance hit in large tables where storage is an issue, but the cost may be outweighed by the additional portability gained in the process.

Formatting data to meet the requirements of multiple database types goes hand-in-hand with using vendor-specific implementations of common functions. For instance, the function that converts a string to uppercase in MySQL is UCASE(), but in Oracle it is UPPER(). Likewise, a random number is generated in MySQL with RAND() but in Oracle you might use something like this instead.

abs(mod(DBMS_RANDOM.RANDOM,10000001)/10000000)

 


When designing a database abstraction layer, these idiosyncrasies can be managed simply by assigning the relevant function call to a class property. At runtime, the DAL simply replaces the property name with the relevant function as shown here.

$sql = “INSERT into random_number (value) VALUES (“ . $db->random . “)”;