That's quite an omission from the docs. I'll have to try this on Oracle.
mxAjax / CFAjax docs and other useful articles:
http://www.bifrost.com.au/blog/
On 30 April 2010 02:20, Aaron Neff wrote:
>
> Just wanted to also mention CF9's result_name.generatedkey.
>
> Please see comment @ bottom of
Just wanted to also mention CF9's result_name.generatedkey.
Please see comment @ bottom of online help for cfquery:
http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7fae.html
Thanks!,
-Aaron Neff
>When using "set nocount on", "select @@identity as xyz" an
... obtaining the correct Incremental ID
You can of course avoid the whole issue by using the result_name.IDENTITYCOL
value returned from the cfquery tag (if you are on CF8 or higher).
mxAjax / CFAjax docs and other useful articles:
http://www.bifrost.com.au/blog/
On 29 April 2010 01:51, Che Vilnonis
The same basic issue came up on the SQL list.
http://www.houseoffusion.com/groups/sql/thread.cfm/threadid:898
While the question and result is based on a stored procedure, the same
code can go in a single cfquery statement. The operation is a simple
check for data existence and insert if it does
You can of course avoid the whole issue by using the result_name.IDENTITYCOL
value returned from the cfquery tag (if you are on CF8 or higher).
mxAjax / CFAjax docs and other useful articles:
http://www.bifrost.com.au/blog/
On 29 April 2010 01:51, Che Vilnonis wrote:
>
> When using "set nocou
>YO! 2005 +
>
>USE OUTPUT Duh!
Yes, if needed, OUTPUT is an option as well. (Though IIRC it does not work with
the jTDS driver. At least not the last time I checked.) However, the primary
point was there are safer alternatives to SELECT MAX(ID) ..
age ----
> Subject: Re: SQL Gurus... obtaining the correct Incremental ID
> From: Leigh _
> Date: Wed, April 28, 2010 4:44 pm
> To: cf-talk
>
>
> >That would ONLY work if you used SET TRANSACTION ISOLATION LEVEL
> >SERIALIZABLE or WITH(TABLOCKX HOLDLOCK)
> >
> Wouldn't READ COMMITTED do the trick? ...will never read data that another
> application has changed and not yet committed
No. Another process could insert a record into the table and commit its
change before you select out the max. The ONLY way for the max() record
to be yours is to preven
quot;SET TRANSACTION ISOLATION LEVEL SERIALIZABLE" immediately prior to the
select.
~Brad
-------- Original Message --------
Subject: Re: SQL Gurus... obtaining the correct Incremental ID
From: Dave Watts
Date: Wed, April 28, 2010 4:54 pm
To: cf-talk
> That would ONLY work if you used SET TRANSA
Yes, I would agree that is a much simpler and safer alternative. since
scope_identity() is already specific to your connection you don't have
to worry about locking and such altogether.
~Brad
Original Message
Subject: Re: SQL Gurus... obtaining the correct Incremental ID
from cfquickdocs for cf8: "If you do not specify a value for the isolation
attribute, ColdFusion uses the default isolation level for the associated
database."
from mssqlcity.com: "READ COMMITTED
This is the default isolation level in SQL Server. When it's used, SQL Server
will use shared lock
On Wed, Apr 28, 2010 at 5:34 PM, nvc 1 wrote:
>
> so is this reliable?
>
>
>
>
>insert into mytable(acolumn) values(1)
>
>
>select max(id) as
>That would ONLY work if you used SET TRANSACTION ISOLATION LEVEL
>SERIALIZABLE or WITH(TABLOCKX HOLDLOCK)
>
>Even with with rowlock, another spid can insert additional records into
>the table and the select max() might return a bogus value.
Aside from being unsafe without the right isolation lev
so is this reliable?
insert into mytable(acolumn) values(1)
select max(id) as maxid from mytable
> That would ONLY work if you used SET TRANSACTION ISOLATION LEVEL
> SERIALIZABLE or WITH(TABLOCKX HOLDLOCK)
This is one of those things I never am completely clear on, but I
believe that the default isolation level for CFTRANSACTION is
serializable in CF 7+.
Dave Watts, CTO, Fig Leaf Software
h
ssage
Subject: RE: SQL Gurus... obtaining the correct Incremental ID
From: "Che Vilnonis"
Date: Wed, April 28, 2010 3:59 pm
To: cf-talk
I am using the cftransaction tags. Honestly, I was not absolutely sure
that
I needed to do anything else and that is why I posted my or
Gurus... obtaining the correct Incremental ID
From: nvc 1
Date: Wed, April 28, 2010 3:14 pm
To: cf-talk
why can't you do this where mytable has an auto incrementing primary key
called id:
insert into mytable(acolumn)
values(1)
select max(id) as maxid from my
... obtaining the correct Incremental ID
why can't you do this where mytable has an auto incrementing primary key
called id:
insert into mytable(acolumn)
values(1)
select max(id) as maxid from mytable
~|
why can't you do this where mytable has an auto incrementing primary key called
id:
insert into mytable(acolumn)
values(1)
select max(id) as maxid from mytable
~|
Want to reach the ColdFusion community with
Dave/Barney/Casey... I'm using SQL 2005.
I just wanted to cover all bases and was looking for a code snippet. Thanks.
-Original Message-
From: Casey Dougall [mailto:ca...@uberwebsitesolutions.com]
Sent: Wednesday, April 28, 2010 2:13 PM
To: cf-talk
Subject: Re: SQL Gurus... obta
> Transactions, transactions, transactions. Don't write another line of
> SQL until you learn about transactions.
I really just want to second this. There should be a law. With
penalties including jail time for violation. (But perhaps time off
would be granted for understanding indexes.)
Dave Wa
On Wed, Apr 28, 2010 at 1:51 PM, Che Vilnonis wrote:
>
> When using "set nocount on", "select @@identity as xyz" and "set nocount
> off" in a cfquery, how can I be certain that two transactions that occur at
> roughly the same time obtain the proper incremental id from an Identity
> column? Is t
> When using "set nocount on", "select @@identity as xyz" and "set nocount
> off" in a cfquery, how can I be certain that two transactions that occur at
> roughly the same time obtain the proper incremental id from an Identity
> column? Is their a SQl equivalent to CF's cflock tag?
BEGIN TRANSACT
Transactions, transactions, transactions. Don't write another line of
SQL until you learn about transactions.
In this case it doesn't matter because @@identity is bound to the
active connection (and connections are single threaded), but you
should still be transactionally aware.
In addition to
24 matches
Mail list logo
