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=44477&DE=1 http://www.sys-con.com/story/?storyid=45569&DE=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=44477&DE=1 http://www.sys-con.com/story/?storyid=45569&DE=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
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=44477&DE=1 http://www.sys-con.com/story/?storyid=45569&DE=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
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
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
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: 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 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
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
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
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... >> >> >> insert stuff; >> select scope_identity() as something; >> >> >> 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=44477&DE=1 http://www.sys-con.com/story/?storyid=45569&DE=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
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... > > > insert stuff; > select scope_identity() as something; > > > 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. > > > >> >> set nocount on; >> insert stuff; >> select @@identity as something; >> set nocount off; >> > > >>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
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... insert stuff; select scope_identity() as something; 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. > > set nocount on; > insert stuff; > select @@identity as something; > set nocount off; > > 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
set nocount on; insert stuff; select @@identity as something; set nocount off; 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
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
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