View source for Writing Portable SQL

== "GOTCHAs" within Geeklog and plugin SQL coding ==

''(These tips were originally posted by Randy Kolenko, who [http://www.geeklog.net/article.php/20060918173353100 implemented] the MS SQL Server support for Geeklog)''

* Stray away from <code>DB_save</code>. Use the appropriate <tt>UPDATE</tt> or <tt>INSERT</tt> statements as necessary. Although this works in the MSSQL class, it's not a standard SQL call.
* Avoid <tt>REPLACE INTO</tt> at all costs. While <code>DB_save</code> 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 <tt>UPDATE</tt> or <tt>DELETE</tt> + <tt>INSERT</tt> combination to perform the <tt>REPLACE INTO</tt> functionality.
* While very handy, <tt>LIMIT</tt> is not a standard SQL statement. However a statement such as: <pre>SELECT * FROM table LIMIT 1</pre> will be translated into: <pre>SELECT TOP 1 * FROM table</pre> and will not incurr any in-code overhead to approximate the limit command. <tt>LIMIT</tt>-ing your result sets like this: <pre>SELECT * FROM table LIMIT 100,10</pre> 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 <tt>LIMIT</tt> 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 <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.

== 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 ==

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


[[Category:Development]]