> <cfquery>
> declare @newid int
>
> select @newid = max(id) from Members
>
> if @newid is null
Oddly enough that's actually more complicated than is necessary if
you're using SQL Server -- i.e.
insert into members (memberid,x,y,z)
values (
(select 1+isnull(max(memberid),0) from members),
valx,
valy,
valz
)
I'm pretty certain that (or some variation) works for SQL Server...
which of course is totally unnecessary on SQL Server if you're using
identity columns. :) But then Rick replied later to say that he's not
using an identity/autonumber capable database with his legacy app...
although if the db has some equivalent of isnull() then the above might
work out fairly well. In failing that, this should work:
<cftransaction isolation="serializable">
<cfquery name="next" datasource="blah">
select max(memberid) from members
</cfquery>
<cfquery datasource="blah">
insert into members (memberid,x,y,z)
values (
<cfqueryparam value="#next.memberid+1#" cfsqltype="integer" />,
xval,
yval,
zval
)
</cfquery>
</cftransaction>
The serializable transaction is important to ensure that there's not a
race condition between when the max is retrieved and when it's inserted
into the table. However I will say one other thing about this. There's
another "race-condition-esque" problem with this strategy that may crop
up. If a user deletes the record which is currently last in the database,
then the next record that gets inserted will have the same id as the
deleted record, which may be confusing if someone has bookmarked a page
with that id in the url and/or may cause some data related to the
deleted record to show up erroneously as having been associated to the
new record if the application doesn't use foreign key constraints (which
I personally almost always use) and doesn't do a great job of cleaning
up the data in those related tables when a record is deleted.
This may or may not be a big concern for you, given that it's a legacy
application and it may not have a huge user database and the likelyhood
of these things happening (or being problematic) may not be very high.
However if you want to avoid that situation, what I would recommend is
as at least one other guy on the list mentioned, creating a second table
just to hold the new id's. The very simple table would just contain two
columns - tablename varchar(x) & currentidentity int - and instead of
select max(memberid) from members, you would use select currentidentity
from [identitytable] where tablename = 'members' (or whatever your table
is). You would also need to add a 3rd query in your transaction between
the 2 above to insert or update that row of the identity table, i.e.
<cfquery dsn="blah">
<cfif val(next.identity)>
update [identitytable] set currentidentity = currentidentity+1
where tablename = 'members'
<cfelse>
insert into [identitytable] (tablename,currentidentity)
values ('members',1)
</cfif>
</cfquery>
Although this does involved 3 queries instead of 2, its performance
won't change as the size of the database grows (as opposed to max()
getting slower as the table gets larger, which, with a legacy app is
probably not a real big issue). If you end up doing this sort of thing for
several tables you may want to create a CFC to abstract this so you
don't have so much duplicated or copy/pasted code.
hth,
ike
--
s. isaac dealey ^ new epoch
isn't it time for a change?
ph: 503.236.3691
http://onTap.riaforge.org
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295223
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
