Dominic Watson wrote:
Is that definately true with the select insert as I exampled? I imagine (but
that is all I do, quite willing to be wrong) that the single SQL statement
must complete before another is allowed to run..? I.e. I imagine that this
SQL will not run into the problem you
I haven't tested your statements, but extrapolating from the basics of
the concurrency control algorithms used by different databases I think
that is not correct.
Thanks Jochem, good to know :)
Good job to isaac pointing out the need for IsNull doing it this way too ;)
Dominic
Thanks Jochem, good to know :)
Good job to isaac pointing out the need for IsNull doing it this way too ;)
tnx
p.s. I never know how to handle complements. :P
--
s. isaac dealey ^ new epoch
isn't it time for a change?
ph: 503.236.3691
http://onTap.riaforge.org
With databases that use a locking approach to concurrency (DB2, MS
SQL Server, Sybase) you will not have the problem because multiple
occurences of this query will be serialized. With databases that use
multiversioning (Oracle, PostgreSQL and MS SQL Server with snapshot
isolation) this query
If someone has a better idea for a unique ID, I'm open to suggestions.
Well, if the id is for records in a table, how about using an auto
counter field?
--
___
REUSE CODE! Use custom tags;
See http://www.contentbox.com/claude/customtags/tagstore.cfm
(Please
If you need an absolute unique id that isn't tied to a database, feel free
to use the createUUID() function. Otherwise, what you're asking for can be
done via SQL... so... not sure why this is overcomplicated. Do you have a
unique situation? care to explain?
On Dec 20, 2007 9:23 AM, Rick
What exactly are you trying to do?
Robert B. Harrison
Director of Interactive services
Austin Williams
125 Kennedy Drive, Suite 100 Hauppauge NY 11788
T : 631.231.6600 Ext. 119
F : 631.434.7022
www.austin-williams.com
Great advertising can't be either/or... It must be .
-Original
Your db platform doesn't support autonumber?
Steve Cutter Blades
Adobe Certified Professional
Advanced Macromedia ColdFusion MX 7 Developer
_
http://blog.cutterscrossing.com
Rick Sanders wrote:
Hello list,
I've done dynamic ID's before in .net and xml. I
cfquery
declare @newid int
select @newid = max(id) from Members
if @newid is null
begin
set @newid=1
end
else
begin
set @[EMAIL PROTECTED]
end
INSERT INTO Members ...
/CFQUERY
I know of someone who uses a Sequence table for the ID functions:
Sequence table:
table_name (varchar)
last_sequence (int)
next_sequence (int)
Query the table based on table name get the next_sequence and then
update
The sequence table after the insert
-Original Message-
From: Rick
: 919-799-9076
Canada: www.webenergy.ca
USA: www.webenergyusa.com
-Original Message-
From: Cutter (CFRelated) [mailto:[EMAIL PROTECTED]
Sent: December-20-07 10:31 AM
To: CF-Talk
Subject: Re: Dynamic ID's
Your db platform doesn't support autonumber?
Steve Cutter Blades
Adobe
I'd let the database create the number. But anyway, you can do it like this:
cfquery name=getnumbers datasource=#variables.dsn#
SELECT max(tableID) as Lastnumber from tablename
/cfquery
cfset newID = getNumbers.Lastnumber + 1 /
I have functions where i do this, but generally you'd be best to
How about an Identity field (in MS SQL)
Or alternatively CreateUUID() ?
On 20/12/2007, Rick Sanders [EMAIL PROTECTED] wrote:
Hello list,
I've done dynamic ID's before in .net and xml. I can't seem to figure out
how to do it in CF. Here's what I want to do:
- Query the
: Todd [mailto:[EMAIL PROTECTED]
Sent: December-20-07 10:31 AM
To: CF-Talk
Subject: Re: Dynamic ID's
If you need an absolute unique id that isn't tied to a database, feel free
to use the createUUID() function. Otherwise, what you're asking for can be
done via SQL... so... not sure why
Or, if you really need to do it the way you describe, the SQL could look
like this:
-- SELECT A NEW ID
SELECT Max(idField) + 1 FROM myTable
But yeh, if you can set an auto id in the db I'd go with that.
Dominic
On 20/12/2007, JediHomer [EMAIL PROTECTED] wrote:
How about an Identity field (in
The database does support autonumber,
What database is it?
there are many times where autonumber isn't useful because when you
delete a record, the database re-numbers everything
Really? I've never seen such a database that re-numbers auto numbers.
--
___
Yup, this is a unique situation, it's a new application on top of an old
database built in Pervasive SQL. The Primary key holds ID numbers that
aren't auto generated.
I think I see the situation and you're approach is valid but long winded ;).
You can insert a record with a new ID with one
-Original Message-
From: Mike Kear [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 20, 2007 8:47 AM
To: CF-Talk
Subject: Re: Dynamic ID's
I'd let the database create the number. But anyway, you can do it like
this:
cfquery name=getnumbers datasource=#variables.dsn# SELECT max
: RE: Dynamic ID's
The database does support autonumber, but this is a legacy app and database.
Also, there are many times where autonumber isn't useful because when you
delete a record, the database re-numbers everything changing the ID which we
didn't want.
Rick Sanders
Webenergy
Canada: 902-401
Beware of doing it this way... You will need cftransaction or some other
way of ensuring that the ID is unique. Consider the situation where 2
inserts from 2 different people are happening nearly simultaneously. The
second request for the max(ID) could occur before the first is
inserted
or autonumber field is
doing something quite similar to this under the hood. Perhaps Jochem or one
of the other super SQL guru's could tell us more :)
-Mark
-Original Message-
From: Dominic Watson [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 20, 2007 1:37 PM
To: CF-Talk
Subject: Re: Dynamic
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
The database does support autonumber, but this is a legacy app and
database. Also, there are many times where autonumber isn't useful
because when you delete a record, the database re-numbers everything
changing the ID which we didn't want.
Oh I misread that the first time... I thought you'd
Beware of doing it this way... You will need cftransaction or some
other way of ensuring that the ID is unique. Consider the situation
where 2 inserts from 2 different people are happening nearly
simultaneously. The second request for the max(ID) could occur
before the first is
24 matches
Mail list logo
