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
... 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
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 and
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 w...@itisdesign.com wrote:
Just wanted to also mention CF9's result_name.generatedkey.
Please see
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?
Thanks, Che
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
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 TRANSACTION ...
On Wed, Apr 28, 2010 at 1:51 PM, Che Vilnonis ch...@asitv.com 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?
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
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... obtaining
why can't you do this where mytable has an auto incrementing primary key called
id:
cftransaction
cfquery datasource=#dsn# name=insertQuery
insert into mytable(acolumn)
values(1)
/cfquery
cfquery datasource=#dsn# name=getid
select max(id) as maxid from mytable
/cfquery
... obtaining the correct Incremental ID
why can't you do this where mytable has an auto incrementing primary key
called id:
cftransaction
cfquery datasource=#dsn# name=insertQuery
insert into mytable(acolumn)
values(1)
/cfquery
cfquery datasource=#dsn# name=getid
select max(id) as maxid
Gurus... obtaining the correct Incremental ID
From: nvc 1 n...@hotmail.co.uk
Date: Wed, April 28, 2010 3:14 pm
To: cf-talk cf-talk@houseoffusion.com
why can't you do this where mytable has an auto incrementing primary key
called id:
cftransaction
cfquery datasource=#dsn# name=insertQuery
Subject: RE: SQL Gurus... obtaining the correct Incremental ID
From: Che Vilnonis ch...@asitv.com
Date: Wed, April 28, 2010 3:59 pm
To: cf-talk cf-talk@houseoffusion.com
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
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
so is this reliable?
cftransaction isolation=serializable
cfquery datasource=#dsn# name=insertQuery
insert into mytable(acolumn) values(1)
/cfquery
cfquery
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 level,
On Wed, Apr 28, 2010 at 5:34 PM, nvc 1 n...@hotmail.co.uk wrote:
so is this reliable?
cftransaction isolation=serializable
cfquery datasource=#dsn# name=insertQuery
insert into mytable(acolumn) values(1)
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 locks
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
to the cftransaction tag, I get
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 dwa...@figleaf.com
Date: Wed, April 28, 2010 4:54 pm
To: cf-talk cf-talk
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 prevent
Subject: Re: SQL Gurus... obtaining the correct Incremental ID
From: Leigh _ cfsearch...@yahoo.com
Date: Wed, April 28, 2010 4:44 pm
To: cf-talk cf-talk@houseoffusion.com
That would ONLY work if you used SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE or WITH(TABLOCKX HOLDLOCK)
Even
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) ..
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 ch...@asitv.com wrote:
When
25 matches
Mail list logo
