[geeklog-devel] Fwd: MSSQL Class - SQL Coding

[Dirk Haun]

I dug out Randy's old post (below) for Tim, who asked for advice on
writing DBMS-agnostic SQL requests.

Shouldn't this be on the wiki? It could serve as the basis for a "guide
on writing SQL requests", to which Stan would surely want to add a few
Postgres Gotchas ...

---------------- Anfang Weiterleitung ----------------
Betreff: [geeklog-devel] MSSQL Class - SQL Coding 
Gesendet: Donnerstag, 23. März 2006 8:53 Uhr
Von: Randy Kolenko <randy at nextide.ca>
An: geeklog-devel at lists.geeklog.net


"GOTCHAs" within GL and plugin SQL coding:

- Stray away from DB_save.  Use the appropritate Update or Insert statements
as necessary.  Although this works in the MSSQL class, its 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:
      selet 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 absolutelly 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.


_______________________________________________
geeklog-devel mailing list
geeklog-devel at lists.geeklog.net
http://lists.geeklog.net/listinfo/geeklog-devel


----------------- Ende Weiterleitung -----------------

-- 
http://www.geeklog.net/
http://geeklog.info/