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

[ȘϮⱥȵіṥʟɑ√]

Hey,

Just of the top of my head, a nice thing to do would be to pass the sql to
dbError(). If you don't, then pgSQL will have to run an extra function call
to sniff out the last execution query, which wastes some precious cycles.

Also you should preferably use timestamp instead of datetime because
timestamp is ansi sql, while datetime is MySQL's little invention. :P

Those two things arent critical, but the next one is, which has been
mentioned..

Avoid any variations of REPLACE like the plague. Seriously :P It's only
supported by MySQL, and while it may make MySQL queries a hair faster, it
will make msssql and pgsql queries alot slower because they dont support it.


Your best bet is to just code ANSI SQL compliant code. If you've only been
used to Mysql, it can be very tempting to cheat, but it will pay off in the
end ;)

On Thu, May 28, 2009 at 4:45 PM, Dirk Haun <dirk at haun-online.de> wrote:

> 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/
>
> _______________________________________________
> geeklog-devel mailing list
> geeklog-devel at lists.geeklog.net
> http://eight.pairlist.net/mailman/listinfo/geeklog-devel
>



-- 
Warmly,

Stanislav
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://pairlist8.pair.net/pipermail/geeklog-devel/attachments/20090528/4fe60c91/attachment.html>