[thelist] [OT] SQL and portability.

[rudy]

>  How, I wonder, do you approach portability?

hi frank

stick to standard sql, and all should be well     =o)

the sql3 (a.k.a. sql-93) standard has something called "core" sql which is
pretty safe

unfortunately, it's not that easy to find good info on standard sql

i've searched the web several times to no avail looking for a definitive
standard sql reference (as opposed to specific vendor implementations)

i did see a good sql reference book at the chapters outlet down by the
skydome, but it was $110Cdn and i'm not really that keen


><cfquery name="GetMaxID" datasource="">
>    SELECT Max(MyTableKey)
>    FROM MyTable
>    </cfquery>
>
>then
>
><cfquery name="MyInsert" datasource="">
>    INSERT INTO MyTable (MyTableKey, MyOtherFields)
>    VALUES (#GetMaxID.MyTableKey#, #MyOtherStuff#)
>    </cfquery>
>
>Neat. Now if only I could  combine the select and the insert. That
>would be even neater.


i would caution against selecting max() and then using this value later,
unless your site gets, like, only five hits a month   =o)

the danger of concurrent hits has been covered previously in a couple of
threads regarding the @@identity feature of sql/server, which also works in
access2k apparently --
http://www.aspwatch.com/c/200043/d1AED4F05B84711D4AF0200A0C9E95208.asp
(thanks, cory)

anyhow, there *might* be a way to combine your two queries --

      INSERT INTO MyTable (MyTableKey, MyOtherFields)
        SELECT Max(MyTableKey) + 1, #MyOtherStuff#
             FROM MyTable

this is certainly not a problem if you're selecting from one table to
insert into another, but some databases won't let you insert into the same
table you're selecting from in the same query

also, don't forget to increment the max you get, otherwise you'll be trying
to insert the max value again, which would probably fail


rudy

p.s.  thanks for the kinds words, frank