Re: SQL Gurus... obtaining the correct Incremental ID
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 not exist. All nice, neat, and transaction controlled by SQL. CREATE PROCEDURE getinsertnewssourceid @newssource varchar(256), @newssourcehref varchar(256) AS Set NOCOUNT ON; Select newssourceid from newssources where newssource = @newssource and href = @newssourcehref; if @@ROWCOUNT = 0 begin Insert into newssources(newssource, href) values(@newssource, @newssourcehref); select newssourceid= scope_identity(); end SET NOCOUNT OFF; GO -- Michael Dinowitz On Wed, Apr 28, 2010 at 4:14 PM, nvc 1 n...@hotmail.co.uk wrote: 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 /cftransaction ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333224 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Gurus... obtaining the correct Incremental ID
This might help everyone... Regardless of DBMS... http://www.forta.com/blog/index.cfm/2007/7/6/ColdFusion-8-Can-Return-Identit y-Values -Original Message- From: James Holmes [mailto:james.hol...@gmail.com] Sent: Wednesday, April 28, 2010 9:43 PM To: cf-talk Subject: Re: SQL Gurus... 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 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? Is their a SQl equivalent to CF's cflock tag? Thanks, Che ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333225 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Gurus... obtaining the correct Incremental ID
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 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 ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333257 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Gurus... obtaining the correct Incremental ID
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 comment @ bottom of online help for cfquery: http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7fae.html ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333277 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Gurus... obtaining the correct Incremental ID
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 transactions, you can also do various types of locking on the database to serialize access (exactly like CFLOCK), but they obviously have performance and concurrency ramifications. Transactions are typically a better way to solve it those types of problems because their semantics are slightly different and can be implemented in a more performant manner in most cases. cheers, barneyb On Wed, Apr 28, 2010 at 10:51 AM, 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? Is their a SQl equivalent to CF's cflock tag? Thanks, Che -- Barney Boisvert bboisv...@gmail.com http://www.barneyb.com/ ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333187 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Gurus... obtaining the correct Incremental ID
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 ... END TRANSACTION If you're using a newer version of SQL Server, you may want to use SCOPE_IDENTITY() instead of the @@IDENTITY global variable. http://msdn.microsoft.com/en-us/library/ms190315.aspx Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333188 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Gurus... obtaining the correct Incremental ID
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? Is their a SQl equivalent to CF's cflock tag? Thanks, Che On MS SQL server I believe anyting above 2000 use the output clause cfquery insert into table (col1, col2, col3) output inserted.ID values (col1Val,col2Val,col3Val) /cfquery ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333189 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Gurus... obtaining the correct Incremental ID
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 Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333190 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Gurus... obtaining the correct Incremental ID
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 the correct Incremental ID 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? Is their a SQl equivalent to CF's cflock tag? Thanks, Che On MS SQL server I believe anyting above 2000 use the output clause cfquery insert into table (col1, col2, col3) output inserted.ID values (col1Val,col2Val,col3Val) /cfquery ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333191 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Gurus... 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 from mytable /cfquery /cftransaction ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333192 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Gurus... obtaining the correct Incremental ID
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 original question. -Original Message- From: nvc 1 [mailto:n...@hotmail.co.uk] Sent: Wednesday, April 28, 2010 4:14 PM To: cf-talk Subject: Re: SQL Gurus... 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 from mytable /cfquery /cftransaction ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333195 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Gurus... obtaining the correct Incremental ID
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. ~Brad Original Message Subject: Re: SQL 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 insert into mytable(acolumn) values(1) /cfquery cfquery datasource=#dsn# name=getid select max(id) as maxid from mytable /cfquery /cftransaction ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333198 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Gurus... obtaining the correct Incremental ID
Transactions control when data gets committed as well as how long some locks are held. They do not necessarily keep other processes from modifying parts of a table you are touching. That's when isolation levels and locking behavior comes into play. ~Brad Original Message 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 my original question. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333199 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Gurus... obtaining the correct Incremental ID
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 http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333201 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Gurus... obtaining the correct Incremental ID
so is this reliable? cftransaction isolation=serializable 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 /cftransaction ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333202 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Gurus... obtaining the correct Incremental ID
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, it is not necessary in MSSQL 2005 or CF8+. As use mentioned use SCOPE_IDENTITY() or cfquery's result attribute. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333203 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Gurus... obtaining the correct Incremental ID
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) /cfquery cfquery datasource=#dsn# name=getid select max(id) as maxid from mytable /cfquery /cftransaction It's way simpler! This is totally transaction safe since you are only interested in the ID form one table not many and are running MS SQL server 2005 or above. On 2000 you can't use output clause. cfquery NAME=request.qName DATASOURCE=#request.dsn# INSERT INTO TableName (Stuff) OUTPUT inserted.ID, inserted.Stuff VALUES (cfqueryparam cfsqltype=cf_sql_varchar value=#request.stuff#) /cfquery cfdump var=#request.qName# As you see, you can output any column you just inserted, and they are returned in a query ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333204 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
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 locks while reading data. It ensures that a physically corrupt data will not be read and will never read data that another application has changed and not yet committed, but it not ensures that the data will not be changed before the end of the transaction. Wouldn't READ COMMITTED do the trick? ...will never read data that another application has changed and not yet committed ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333205 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Gurus... obtaining the correct Incremental ID
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: 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 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, it is not necessary in MSSQL 2005 or CF8+. As use mentioned use SCOPE_IDENTITY() or cfquery's result attribute. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333206 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Gurus... obtaining the correct Incremental ID
I'm on 8.0.1 and it appears that CF doesn't override the default SQL Server isolation level if you don't specify one in your cftransaction tag. I ran the following code whilst tracing: cftransaction cfquery name=test datasource=test select * from table /cfquery /cftransaction This is what I got: SET IMPLICIT_TRANSACTIONS ON select * from test IF @@TRANCOUNT 0 COMMIT TRAN IF @@TRANCOUNT 0 COMMIT TRAN SET IMPLICIT_TRANSACTIONS OFF SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET TRANSACTION ISOLATION LEVEL READ COMMITTED When I added isolation=serializable 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@houseoffusion.com 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 http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333207 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Gurus... obtaining the correct Incremental ID
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 ALL OTHER processes from touching the table. Or, just make life easy on yourself and use scope_identity(). ~Brad ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333209 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Gurus... obtaining the correct Incremental ID
On Wed, Apr 28, 2010 at 6:19 PM, b...@bradwood.com wrote: 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: 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 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, it is not necessary in MSSQL 2005 or CF8+. As use mentioned use SCOPE_IDENTITY() or cfquery's result attribute. YO! 2005 + USE OUTPUT Duh! http://msdn.microsoft.com/en-us/library/ms177564.aspx OUTPUT Clause (Transact-SQL) Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333211 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Gurus... obtaining the correct Incremental ID
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) .. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333217 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Gurus... 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 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? Is their a SQl equivalent to CF's cflock tag? Thanks, Che ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333218 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
