Difference between revisions of "Writing Portable SQL"

From GeeklogWiki
Jump to: navigation, search
(dumped Randy's tips for MS SQL into the wiki)
 
(added section on DBMS-specific SQL)
Line 8: Line 8:
 
* Ensure all selected columns show up in the <tt>GROUP BY</tt> clause - there are a few instances of missing columns in <tt>GROUP BY</tt> clauses. MySQL is a little more forgiving than SQL Server is.
 
* Ensure all selected columns show up in the <tt>GROUP BY</tt> clause - there are a few instances of missing columns in <tt>GROUP BY</tt> clauses. MySQL is a little more forgiving than SQL Server is.
 
* Note that <nowiki>''</nowiki> (single quotes with nothing between them) does not represent <tt>NULL</tt> in SQL Server. The triggers I've implemented are to cleanse any data that appears to be <tt>NULL</tt> and replace the empty <tt>VARCHAR</tt> data with <tt>NULL</tt>. This way, when PHP tests <code>empty()</code> on a column, it returns empty properly.
 
* Note that <nowiki>''</nowiki> (single quotes with nothing between them) does not represent <tt>NULL</tt> in SQL Server. The triggers I've implemented are to cleanse any data that appears to be <tt>NULL</tt> and replace the empty <tt>VARCHAR</tt> data with <tt>NULL</tt>. This way, when PHP tests <code>empty()</code> 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:
 +
 +
<pre>
 +
$sql['mysql'] = "SELECT * WHERE dateend >= (NOW() - INTERVAL 24 HOUR)";
 +
$sql['mssql'] = "SELECT * WHERE dateend >= DATEADD(hh, 24, NOW())";
 +
$result = DB_query($sql);
 +
</pre>
 +
 +
The index name must conform to the value used in the global <code>$_DB_dbms</code> variable, which contains the name of the currently used DBMS.
  
 
== Also see ==
 
== Also see ==

Revision as of 18:19, 30 May 2009

"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