Re: [CFCDev] best practices for inserts in clusters

[jeff tapper]

Jay -

This should work.  As others will most likely tell you, this isnt the best 
way to handle it, as it is dependent on the DB Drivers implementation to 
enforce the Transaction.  All told, if you can do it directly in the DB 
(ie. Sequence/AutoNumber, Stored Proc, Trigger, etc) that would be the best 
way, but in a pinch yours should work.

At 11:27 PM 10/1/2003 -0700, you wrote:
Hello again..

Let's say I have an integer field that I'd like to increment for each record
in my database, like a primary key, but not a primary key.  Every record
should be unique, and sequential (in fact, there would be a unique index on
it).
Now, let's say this table will have records inserted by multiple clustered
cfmx machines.
Would this approach be appropriate?

(pardon the obvious lack of error checking etc.. just trying to illustrate
structure here)
<cftransaction action="begin" isolation="serializable">
        <cftry>
                <cfquery name="Q_nextId" ...>
                        SELECT MAX(MY_TABLE.my_column) + 1 'nextId'
                        FROM   MY_TABLE
                </cfquery>
                <cfquery name="Q_theInsert" ...>
                        INSERT INTO MY_TABLE (
                                my_column
                        ) VALUES (
                                #Q_nextId.nextId#
                        )
                </cfquery>
                <cftransaction action="commit"/>

                <cfcatch>
                        <cftransaction action="rollback"/>
                        <cfrethrow/>
                </cfcatch>
        </cftry>
</cftransaction>
Thanks a ton for any advice..

 - j.

----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email
to [EMAIL PROTECTED] with the word 'unsubscribe cfcdev'
in the message of the email.
CFCDev is run by CFCZone (www.cfczone.org) and supported
by Mindtool, Corporation (www.mindtool.com).
An archive of the CFCDev list is available at 
www.mail-archive.com/[EMAIL PROTECTED]
Jeff Tapper
[EMAIL PROTECTED]
Radio Free Astoria
http://radio.tapper.net
==========
"They who would give up an
essential liberty for temporary security,
deserve neither liberty or security"
- Ben Franklin
----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email
to [EMAIL PROTECTED] with the word 'unsubscribe cfcdev' 
in the message of the email.

CFCDev is run by CFCZone (www.cfczone.org) and supported
by Mindtool, Corporation (www.mindtool.com).
An archive of the CFCDev list is available at www.mail-archive.com/[EMAIL PROTECTED]