Re: Return id after insert
S. Isaac Dealey wrote: I'm just saying that if the table being inserted into contains a no what you said was any session cross the server. that's wrong. I was pretty sure I had corrected myself, but that's neither here nor there... Do you not consider Oracle a major db? Oracle has sequences which are not the same thing. The problem with identities using SQL Server is oracle sequences are similar enough to include them. probably safer than identiy column in that you have to call them before use, but it still requires a bit of thought on the developers part same as identity. that they're only useful / helpful during the insert -- they're a hideous, horrible, awful nightmare if you ever have to try and migrate yes but that's not a very common occurance. idents work well enough for the most part. Heh... well my own personal experience is that if something is a nightmare, I will automatically need to use it frequently, no matter how infrequently anyone else uses it. :P s. isaac dealey 954.927.5117 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.sys-con.com/story/?storyid=44477DE=1 http://www.sys-con.com/story/?storyid=45569DE=1 http://www.fusiontap.com ~| Special thanks to the CF Community Suite Silver Sponsor - RUWebby http://www.ruwebby.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187248 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Return id after insert
S. Isaac Dealey wrote: I'm just saying that if the table being inserted into contains a no what you said was any session cross the server. that's wrong. Do you not consider Oracle a major db? Oracle has sequences which are not the same thing. The problem with identities using SQL Server is oracle sequences are similar enough to include them. probably safer than identiy column in that you have to call them before use, but it still requires a bit of thought on the developers part same as identity. that they're only useful / helpful during the insert -- they're a hideous, horrible, awful nightmare if you ever have to try and migrate yes but that's not a very common occurance. idents work well enough for the most part. ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187247 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Return id after insert
S. Isaac Dealey wrote: include the entire server -- it's real easy if you just create a trigger that inserts into a table in another db on the same server only in your db session. if what you're saying is true, no multi-user db would ever work period. I'm just saying that if the table being inserted into contains a trigger that inserts into a table in any database on the server that contains another identity column, @@identity will return the value from that trigger-inserted table. If there aren't any triggers involved in the statement or they don't insert data into tables with identity columns, or if you don't use the @@identity variable, then there's no reason to have any problem with the db related to @@identity. That being the case, the only solution that makes sense (to me) is either to avoid @@identity without question, or to avoid triggers without question, because if you use both, you always have the potential of it becoming a problem when someone needs to insert a value with a trigger and the code is chock-full of references to @@identity which may be in CF or may be in stored procedures or may be in other places. Given that MS created an alternative to @@identity specifically because of this problem, I chose to use that alternative rather than give up the possibility of using triggers. question. Although by and large I avoid identity columns all-together these days. every major db has them, these are by large pretty useful. Do you not consider Oracle a major db? Oracle has sequences which are not the same thing. The problem with identities using SQL Server is that they're only useful / helpful during the insert -- they're a hideous, horrible, awful nightmare if you ever have to try and migrate data from one server to another -- which I'm guessing is why SQL Server's DTS drops the identities by default when migrating data (which in my experience only causes more problems). We were dealing with that just not too long ago at my 9-5 job. Trying to find an elegant way to create copies of our prototype database when we set up a new project (which -- the prototype db should be our model db so that new db's are created with its structure automatically, but that's a whole other story). In any event, because we couldn't find a good solution to the problem with SQL Server identity columns, our current solution is a CF script that detaches the db from the sql server and then reattaching it with a new name. This means hollering through the office for everyone to disconnect from Enterprise Manager or Query Analyzer each time we create a new client project. s. isaac dealey 954.927.5117 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.sys-con.com/story/?storyid=44477DE=1 http://www.sys-con.com/story/?storyid=45569DE=1 http://www.fusiontap.com ~| Special thanks to the CF Community Suite Silver Sponsor - RUWebby http://www.ruwebby.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187205 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Return id after insert
I cant remember how to return the id from an insert. I have searched but I can't find what I'm looking for. Not to mention people keep buggin me making my job harder than it should be. :-| Anyway, it is some thing like @@identity but I cant remember. Little help? -- Phillip B. ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187071 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Return id after insert
Sounds like you are using SQL Server, so ... SELECT @@IDENTITY AS newid You can also put that in an INSERT trigger, so that the INSERT itself will return that new id. --- Ben -Original Message- From: Phill B [mailto:[EMAIL PROTECTED] Sent: Friday, December 10, 2004 2:59 PM To: CF-Talk Subject: Return id after insert I cant remember how to return the id from an insert. I have searched but I can't find what I'm looking for. Not to mention people keep buggin me making my job harder than it should be. :-| Anyway, it is some thing like @@identity but I cant remember. Little help? -- Phillip B. ~| Special thanks to the CF Community Suite Silver Sponsor - RUWebby http://www.ruwebby.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187073 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Return id after insert
cfquery set nocount on; insert stuff; select @@identity as something; set nocount off; /cfquery Just did this day before yesterday. :-) --Ben Phill B wrote: I cant remember how to return the id from an insert. I have searched but I can't find what I'm looking for. Not to mention people keep buggin me making my job harder than it should be. :-| Anyway, it is some thing like @@identity but I cant remember. Little help? ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187075 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Return id after insert
i'm not sure if that's thread-safe tho... I don't remember offhand what set nocount does... but the @@identity variable is from any table in any sql server session across the database or possibly the server, not sure... SQL Server 2000 introduced a SCOPE_IDENTITY() function which _is_ thread-safe... cfquery insert stuff; select scope_identity() as something; /cfquery This function ensures that the value returned is from the last table inserted in your currently executing batch -- so if it's not in your cfquery, it won't affect the result. (iirc including triggers on the table you're inserting into which has been known to cause problems with @@identity) ... so the rule of thumb is to use scope_identity() if you can. cfquery set nocount on; insert stuff; select @@identity as something; set nocount off; /cfquery Just did this day before yesterday. :-) --Ben Phill B wrote: I cant remember how to return the id from an insert. I have searched but I can't find what I'm looking for. Not to mention people keep buggin me making my job harder than it should be. :-| Anyway, it is some thing like @@identity but I cant remember. Little help? ~| Special thanks to the CF Community Suite Silver Sponsor - New Atlanta http://www.newatlanta.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187114 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Return id after insert
Hmm. Hadn't heard that before. Thanks for the tip! --Ben S.Isaac Dealey wrote: i'm not sure if that's thread-safe tho... I don't remember offhand what set nocount does... but the @@identity variable is from any table in any sql server session across the database or possibly the server, not sure... SQL Server 2000 introduced a SCOPE_IDENTITY() function which _is_ thread-safe... cfquery insert stuff; select scope_identity() as something; /cfquery This function ensures that the value returned is from the last table inserted in your currently executing batch -- so if it's not in your cfquery, it won't affect the result. (iirc including triggers on the table you're inserting into which has been known to cause problems with @@identity) ... so the rule of thumb is to use scope_identity() if you can. cfquery set nocount on; insert stuff; select @@identity as something; set nocount off; /cfquery Just did this day before yesterday. :-) --Ben Phill B wrote: I cant remember how to return the id from an insert. I have searched but I can't find what I'm looking for. Not to mention people keep buggin me making my job harder than it should be. :-| Anyway, it is some thing like @@identity but I cant remember. Little help? ~| Special thanks to the CF Community Suite Silver Sponsor - New Atlanta http://www.newatlanta.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187119 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Return id after insert
Welcome. :) I seem to be giving that one out a lot lately -- or at least, noticing people still using @@identity a lot lately. :) Hmm. Hadn't heard that before. Thanks for the tip! --Ben S.Isaac Dealey wrote: i'm not sure if that's thread-safe tho... I don't remember offhand what set nocount does... but the @@identity variable is from any table in any sql server session across the database or possibly the server, not sure... SQL Server 2000 introduced a SCOPE_IDENTITY() function which _is_ thread-safe... cfquery insert stuff; select scope_identity() as something; /cfquery This function ensures that the value returned is from the last table inserted in your currently executing batch -- so if it's not in your cfquery, it won't affect the result. (iirc including triggers on the table you're inserting into which has been known to cause problems with @@identity) ... so the rule of thumb is to use scope_identity() if you can. s. isaac dealey 954.927.5117 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.sys-con.com/story/?storyid=44477DE=1 http://www.sys-con.com/story/?storyid=45569DE=1 http://www.fusiontap.com ~| Special thanks to the CF Community Suite Silver Sponsor - New Atlanta http://www.newatlanta.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187121 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Return id after insert
It has nothing to do with threads; rather if there's a trigger on the table, the trigger might result in you getting the incorrect ID. Always use SCOPE_IDENTITY() if you're using SQL Server 2000. Never use triggers if you need to use @@IDENTITY in SQL Server 7. :) ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187125 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Return id after insert
Aye, you'll want to do SCOPE_IDENTITY() 95% of the time. And only do the other 5% if you know why you're doing it that way :). @@Identity looks across all tables, so if your insert has fired a trigger that inserts into another table, you'll get the identity from the second table (that the trigger inserted into), not the first. Oops! There's also IDENT_CURRENT(tablename) which looks tempting because it returns the identity of the last row enterered into the specific table. This should probably be avoided in a multi-user system, as it's possible for someone to do another insert between your INSERT statement and SELECT IDENT_CURRENT(tablename) AS... statement. -joe On Fri, 10 Dec 2004 16:51:04 -0400, Kwang Suh [EMAIL PROTECTED] wrote: It has nothing to do with threads; rather if there's a trigger on the table, the trigger might result in you getting the incorrect ID. Always use SCOPE_IDENTITY() if you're using SQL Server 2000. Never use triggers if you need to use @@IDENTITY in SQL Server 7. :) ~| Special thanks to the CF Community Suite Silver Sponsor - CFDynamics http://www.cfdynamics.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187163 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Return id after insert
S. Isaac Dealey wrote: i'm not sure if that's thread-safe tho... I don't remember offhand what set nocount does... but the @@identity variable is from any table in any sql server session across the database or possibly the server, no, that's an urban myth. it's any table with a column that has an identity characteristic that you insert data into in your current session, not the db, not the server certainly nobody else's session. scope_identity is mainly used when you have triggers such that can change the @@identiy var value before you get at it. SET NOCOUNT ON/OFF tells sql server to return/not return metadata about how many rows were affected by the last sq statement. is often used to shave some time off stuff was used w/4.5 cf5 to pack more than 1 sql server statement into a cfquery. sql server returning row counts would confuse cf as if it were legitimate results. ~| Special thanks to the CF Community Suite Silver Sponsor - RUWebby http://www.ruwebby.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187173 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Return id after insert
S. Isaac Dealey wrote: i'm not sure if that's thread-safe tho... I don't remember offhand what set nocount does... but the @@identity variable is from any table in any sql server session across the database or possibly the server, no, that's an urban myth. it's any table with a column that has an identity characteristic that you insert data into in your current session, not the db, not the server certainly nobody else's session. scope_identity is mainly used when you have triggers such that can change the @@identiy var value before you get at it. I didn't remember the specifics -- but the current session _can_ include the entire server -- it's real easy if you just create a trigger that inserts into a table in another db on the same server that happens to have an identity column in it. At the time I read it, I considered it bad enough to simply avoid @@identity without question. Although by and large I avoid identity columns all-together these days. SET NOCOUNT ON/OFF tells sql server to return/not return metadata about how many rows were affected by the last sq statement. is often used to shave some time off stuff was used w/4.5 cf5 to pack more than 1 sql server statement into a cfquery. sql server returning row counts would confuse cf as if it were legitimate results. That sounds familiar. s. isaac dealey 954.927.5117 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.sys-con.com/story/?storyid=44477DE=1 http://www.sys-con.com/story/?storyid=45569DE=1 http://www.fusiontap.com ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187174 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Return id after insert
In a web environment, if you've got any pages that add anything to the
database, you're in dangerous territory using @@IDENTITY. It returns
the identity of the last record entered, which might or might NOT be
the one you just entered.
You can use SCOPE_IDENTITY() which returns the last item entered in
the scope of the database you're working in but which still might not
be the table you're interested in, if you have a lot of activity on
your database. The best to use (at least in SQLServer2000 anyway) is
the last identity created on the table you are inserting into. If you
still have it locked by putting your identity call in the same query,
you are guaranteed to get the identity of the record you just entered.
The way to do it is:
cfquery name=insert datasource=#request.DSN#
SET NOCOUNT OFF
INSERT into TABLENAME
(field1, field2, field3)
VALUES
('#form.field1#', '#form.field2#', #form.field3#')
SELECT IDENT_CURRENT('tablename') AS ordernumber
SET NOCOUNT ON
/cfquery
Now the value #insert.ordernumber# will be the identity of that
inserted record. Guaranteed.
The Set NOCOUNT OFF allows you to have more than one database
operation in the same CFQUERY because it supresses the SQLServer from
returning messages to the query. They'll break the query if they
appear midway through the operation.
Cheers
Mike Kear
Windsor, NSW, Australia
AFP Webworks
http://afpwebworks.com
.com,.net,.org domains from AUD$20/Year
On Fri, 10 Dec 2004 19:23:18 -0800, Joe Rinehart [EMAIL PROTECTED] wrote:
Aye, you'll want to do SCOPE_IDENTITY() 95% of the time. And only do
the other 5% if you know why you're doing it that way :).
@@Identity looks across all tables, so if your insert has fired a
trigger that inserts into another table, you'll get the identity from
the second table (that the trigger inserted into), not the first.
Oops!
There's also IDENT_CURRENT(tablename) which looks tempting because it
returns the identity of the last row enterered into the specific
table. This should probably be avoided in a multi-user system, as
it's possible for someone to do another insert between your INSERT
statement and SELECT IDENT_CURRENT(tablename) AS... statement.
-joe
On Fri, 10 Dec 2004 16:51:04 -0400, Kwang Suh [EMAIL PROTECTED] wrote:
It has nothing to do with threads; rather if there's a trigger on the
table, the trigger might result in you getting the incorrect ID.
Always use SCOPE_IDENTITY() if you're using SQL Server 2000.
Never use triggers if you need to use @@IDENTITY in SQL Server 7. :)
~|
Special thanks to the CF Community Suite Silver Sponsor - CFDynamics
http://www.cfdynamics.com
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187172
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Return id after insert
S. Isaac Dealey wrote: include the entire server -- it's real easy if you just create a trigger that inserts into a table in another db on the same server only in your db session. if what you're saying is true, no multi-user db would ever work period. question. Although by and large I avoid identity columns all-together these days. every major db has them, these are by large pretty useful. ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187175 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
