If you're going to use the Max_ID route, don't use MAX(ID). Create a table
with one row to store your Max_ID. Here's what I do:
<CFQUERY NAME="AddRow" DATASOURCE="#REQUEST.DSN#" USERNAME="#REQUEST.USER#"
PASSWORD="#REQUEST.PASS#">
SET NOCOUNT ON
BEGIN TRANSACTION
UPDATE MAX_ID_Clients SET MAX_ID_Client =
MAX_ID_Client + 1
DECLARE @MAX INT
SELECT @MAX = MAX_ID_Client FROM MAX_ID_Clients
INSERT INTO ClientData(CFID,CFTOKEN)
VALUES(@MAX,#CFT#)
DELETE FROM ClientData
WHERE DATEDIFF(hour,HitLast,GetDate()) > 1
COMMIT TRANSACTION
SET NOCOUNT OFF
</CFQUERY>
where MAX_ID_Clients is the table with the Max ID in it.
best, paul
At 04:43 PM 5/30/01 -0700, you wrote:
>Hi folks
>
>I know this is an old recurring discussion but I still can't find a
>good answer in the various CF mailing lists.
>
>I'm soon to start building a new web application with CF and XFB. I'm
>convinced that meaningless integer primary keys for db tables are the
>way to go (although that is another discussion in itself). Our database
>is MS SQL Server 7. I'm still tossing up the question of whether to use
>the db provided identity functionality or roll my own using something
>like Steve's cf_max_id tag. I think identity fields work fine in SQL
>Server 7 and they are certainly tempting. However, I like the feeling
>of 'freedom' from db dependent features so a normal int field with
>SELECT MAX(ID) is appealing to some extent. As Steve's docs say, moving
>to another db is a lot easier without autonumbers.
>
>The recommended use of cf_max_id results in something like this:
>
><cftransaction>
><cfquery name="GetMaxID">
>SELECT MAX(ID) FROM table
></cfquery>
>
><cfquery>
>INSERT INTO table
>(ID, Field1, Field2 etc)
>VALUES(#GetMaxID.Max_D#,'#Field1#', '#Field2#')
></cfquery>
></cftransaction>
>
>Is that really safe in a multi threaded environment? It doesn't look
>like it to me. As far as I know <cftransaction> doesn't stop me losing
>the CPU between the two queries. If I am interupted there, what would
>happen to a second page hitting the same code? Would it just hang at
>the cftransaction or what? What odbc isolation lock type is
>appropriate? I know cflock would fix it but that seems like a bad idea
>or is it really any worse?
>
>Any comments would be appreciated.
>
>Thanks
>Ross
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
