> 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 qu
> 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
>
> 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
~~~
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 desc
> > > 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
> 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
>
> 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
007 1:37 PM
To: CF-Talk
Subject: Re: Dynamic ID's
>
> 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 s
>
> 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
CF-Talk
Subject: 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 San
-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:
SELECT max(tableID)
as Lastnumber from tablename
>
> 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 on
>>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.
--
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
ssage-
From: 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
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
I'd let the database create the number. But anyway, you can do it like this:
SELECT max(tableID) as Lastnumber from tablename
I have functions where i do this, but generally you'd be best to let
the databaes do it for you.
Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced Co
1-7689
USA: 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 &
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 San
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 ...
On 12/20/07 9:23
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.
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 Messag
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 Sanders
>>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
(Pleas
24 matches
Mail list logo
