Writing Portable SQL - Revision history

Dirk: prefer array notation

2009-05-30T19:05:08Z

prefer array notation

Dirk: added section on DBMS-specific SQL

2009-05-30T18:19:26Z

added section on DBMS-specific SQL

Dirk: dumped Randy's tips for MS SQL into the wiki

2009-05-30T17:52:20Z

dumped Randy's tips for MS SQL into the wiki

New page

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

== Also see ==

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

[[Category:Development]]

← Older revision		Revision as of 19:05, 30 May 2009
Line 20:		Line 20:

	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.		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.
		+
		+	Use of the array notation (see above) is recommended instead of checking the content of <code>$_DB_dbms</code> when writing DBMS-specific SQL request.

	== Also see ==		== Also see ==

@@ 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.
 * 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.
 == Also see ==