<cfquery name="insertPeople" datasource="#dsn1#">
set nocount on
insert into people
(password, username)
values
('#thepassword#', '#theusername#'')
SELECT lastid = @@identity
set nocount off
</cfquery>
Good Fortune,
Rick Walters
Webmaster, Davita Laboratory Services
Office: (800) 604-5227
Cell: (407) 491-9848
-----Original Message-----
From: Rick Walters [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 15, 2002 10:48 AM
To: CF-Talk
Subject: RE: Inserting and CFTransaction
It seems that many problems I see in this list revolve around
determining
the next record for an insert and methods to use cftransaction as a
form of
database locking to achieve this purpose. So, I figured I would add
two
cents that might help some of you.
First, what you can do is determined by what product you are using.
Oracle has a nifty little table called DUAL. It's a system table that
will
always have only one row. So, if you "Select nextval from Dual" you
will
have a unique id you can then store in a variable and use for inserts.
If
you use this for all your tables, then your ID values will be different
in
all of your tables, an interesting thing to play with in joins or table
merges. No record will ever have the same ID. Beware, I have heard of
resetting the Dual Table. Never had it happen to me.
Access doesn't have any good way to prevent the multiple simultaneous
insert
problem. So, by using CFCatch, and CFTransaction you can catch the
error of
the second insert, rollback the changes and try again with a new value.
CFTransaction seems to suggest that your set of queries will all run
sequentially and then allow others to access the instance. But, in
truth, I
don't think the tables are locked. Surrounding the code with CFLock
will
supposedly single thread your server's requests, and if your server has
exclusive access to the datasource, that's great. If others also
access
your datasource, then even that won't help you. But, let's get
realistic.
If you're using Access, you can achieve a more than reasonable level of
success using CFLock, CFCatch and CFTransaction. (does CFTransaction
roll
back in Access, I can't remember)
SQL Server is a bit different. You could use the Access method above
with
reasonable success. However, you would be better off to use something
like
the following code.
<cfquery name="insertPeople" datasource="#dsn1#">
set nocount on
insert into people
values
('#thepassword#', '#theusername#'')
SELECT lastid = @@identity
set nocount off
</cfquery>
Surround this query with CFLock, CFCatch, and CFTransaction to make it
almost bulletproof. Simply refer to #insertPeople.lastid# in the
subsequent
queries wherever you want to sync the new id.
Of course, the very best way to make 100% certain that you are
preserving
the ID in SQL server or Oracle is to use a Stored Procedure and lock
the
tables while executing the methods above.
Good Fortune,
Rick Walters
Webmaster, Davita Laboratory Services
Office: (800) 604-5227
Cell: (407) 491-9848
______________________________________________________________________
Get Your Own Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation · $99/Month · Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
