Writing Portable SQL

"GOTCHAs" within Geeklog and plugin SQL coding

(These tips were originally posted by Randy Kolenko, who implemented the MS SQL Server support for Geeklog)

• Stray away from DB_save. Use the appropriate UPDATE or INSERT statements as necessary. Although this works in the MSSQL class, it's not a standard SQL call.
• Avoid REPLACE INTO at all costs. While DB_save approximates this functionality, it only uses one primary key to match the incoming columns and data against rather than all incoming primary key columns. Just use an UPDATE or DELETE + INSERT combination to perform the REPLACE INTO functionality.
• While very handy, LIMIT is not a standard SQL statement. However a statement such as:

  SELECT * FROM table LIMIT 1

  will be translated into:

  SELECT TOP 1 * FROM table

  and will not incurr any in-code overhead to approximate the limit command. LIMIT-ing your result sets like this:

  SELECT * FROM table LIMIT 100,10

  to pick off the 100th to 109th rows is absolutely not supported by SQL Server and there is no equivalent. While handy for paging, the LIMIT approximation for this scenario is handled in the MSSQL class code and thus may not perform as quickly on extremely large result sets.
• Ensure all selected columns show up in the GROUP BY clause - there are a few instances of missing columns in GROUP BY clauses. MySQL is a little more forgiving than SQL Server is.
• Note that '' (single quotes with nothing between them) does not represent NULL in SQL Server. The triggers I've implemented are to cleanse any data that appears to be NULL and replace the empty VARCHAR data with NULL. This way, when PHP tests empty() on a column, it returns empty properly.

DBMS-specific SQL

In the (hopefully) rare case where you need to use SQL requests specific to a certain DBMS, all the database layer functions also accept an array of SQL requests, like so:

$sql['mysql'] = "SELECT * WHERE dateend >= (NOW() - INTERVAL 24 HOUR)";
$sql['mssql'] = "SELECT * WHERE dateend >= DATEADD(hh, 24, NOW())";
$result = DB_query($sql);

The index name must conform to the value used in the global $_DB_dbms variable, which contains the name of the currently used DBMS.

Also see

• http://www.geeklog.net/filemgmt/index.php?id=757