[thelist] [OT] SQL and portability.

[Frank]

I was looking at Matt W's post from the list regarding the 
concatenation problem. Rudy, I really love reading your posts on SQL. 
You provide illustrative examples and clear explanations. You really 
know your stuff and that replaces pain with fun.

We've finally made a small step up from Access to MySQL. It's a whole 
new animal, and a lot of my apps break upon the first change over. 
For example Month() is a function in Access but not in MySQL, but 
both share Day() and Year().

We also have Oracle, SQL7, etc... that each has it's own functions 
and syntactical differences. How, I wonder, do you approach 
portability? Do you really think that code must be tied into the DB 
platform of choice, and that portability must be hindered by the 
least common denominator, or is there another approach to writing 
effective AND portable SQL?


<tip type="Auto Incrementing Keys via SQL" author="Frank Marion">

Ever notice that auto incrementing number are not only a pain to port 
over, but cut into your DB's performance? There's a simple little 
trick I figured out. (And later remembered seeing in the FuseBox 
docs).

Leave your key field as an INT of some type, and use the SQL function 
MAX() to add to the key field. Here's the CF version.

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

</tip>
-- 

Frank Marion                      Loofah Communications
frank at loofahcom.com               http://www.loofahcom.com