If you're unsure about the max_id stuff.... try UUIDs.
<cfquery ... >
INSERT INTO table(ID, Field1, Field2 etc)
VALUES('#createuuid()#','#Field1#', '#Field2#')
</cfquery>
No transaction needed and it's database independent. As long as you've
got a clustered index on that table you won't have any speed problems
with searches even though it's a text string.
The only issue which is only a psychological issue is the fact that the
PKs will be 35 characters long.
Steve Nelson
Ross Keatinge 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
