Are you saying you can't execute multiple SQL statements for access in one CFQUERY?
The MS SQL books say SCOPE_IDENTITY() Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module -- a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch. I beleive we had a (heated) similiar discussion a long time ago, and everyone seemed to agree that @@IDENTITY was the way to go, I still beleive that when I read the books SCOPE_IDENTITY() is the way to go if you want to make sure you get the right Identity. @@IDENTITY as I understood it was more a global thingy ..... -----Original Message----- From: Barry Beattie [mailto:[EMAIL PROTECTED] Sent: Thursday, 29 July 2004 1:19 PM To: CFAussie Mailing List Subject: [cfaussie] RE: CFTransaction and CFC's so you're using SQLServer as a db? that's the only db I know of that knows @@identity AND have multiple SQL statements in the same cfquery block. in this case, because they are in the same cfquery you probably wouldn't even need a transaction. mind you, that's assuming that: <cfquery> == conn.open(dsn) </cfquery> == conn.execute(SQL); conn.close() that's the point I was trying to make: without knowing what the underlying java is doing, you have no way of knowing for certain. I was asking Gary Menzel about this (he has ADO/MS knowledge so he understood what I was talking about). He doesn't trust doing it this way at all. They work with mission critical systems (other peoples $$$) and they generate their own UUID's - gave up on @@identity as a possible hole for this very reason. Works fine with ADO's connection objects but anything else is a mystery. I'm sorry this isn't a definative answer. I've been asking similar questions for a while and have not gotten a concrete answer. I've only bored you to tears with this because no one else has bothered to give a more accurate answer (today or in the past - and I *have* looked). I was hoping some of the background knowledge might help towards a more specific answer to your question because I (no anyone else it seems) can't give you one. HOWEVER... 99.99% of the time you should be able to get away without a transaction (being in the same cfquery block). You could possibly get away with less strong isolation than serializable (if forced to using multi cfquery's and cftransaction). Everyone I've asked on CFCZone.org list says it should be fine - no one goes to any great lengths to ensure data integrity. Hell, most people don't even bother with cftransaction! but this isn't the 100% answer you were looking for. Sorry. mate, just go ahead and do it. by the time it errors (hopefully never), you should be sipping pina coladas at Acapulco! cheers barry.b -----Original Message----- From: Andrew Scott [mailto:[EMAIL PROTECTED] Sent: Thursday, 29 July 2004 11:49 AM To: CFAussie Mailing List Subject: [cfaussie] RE: CFTransaction and CFC's Barry, Without sounding rude on this, I am no novice to CF so I'll let the newbie's read the extra stuff you posted. All I wanted to know is that if I do this <cfquery> insert into sometable (somevalue) values (data) select @@Identity as RecordId </cfquery> Would it actually be run as one query, if this is then I wouldn't need to do a serialiazable on the transaction in this case. Otherwise you are posting too much information that I already am aware off!! But hey still insightful:-) Regards Andrew Scott Technical Consultant NuSphere Pty Ltd Level 2/33 Bank Street South Melbourne, Victoria, 3205 Phone: 03 9686 0485 - Fax: 03 9699 7976 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Barry Beattie Sent: Thursday, 29 July 2004 11:36 AM To: CFAussie Mailing List Subject: [cfaussie] RE: CFTransaction and CFC's IMHO, for an "insert parent Record" + "get @@identity" + "insert child records", any isolation type would be fine. it's being under the umbrella of a transaction that matters. return the @@identity, store in a variable and use that for the life of the process (the transaction) actually, on second thoughts, "SELECT @@identity from tablename" is designed for returning autonumbers after insert (but you can only do it once). I mean, what's the alternative? "select Max(autonumberField) from tablename"? how can you trust that someone else doing an insert has got the same "Max(autonumberField)" because their thread also did that query before your thread could do anything about it. "isolation=serializable" is sounding better all the time... One thing to keep in mind is that CF is a multi-threaded webserver (LOL! is there any othertype?). cftransaction is a way of controlling which threads are accessing the db at any one time. Imagine three queries one after the other. it looks like big one block of code. But the way the CPU executes the commands, it's as if 1000 lines of code are between each cfquery with the other 900 lines executed under the context of diferent users. Additionally, if it was an update with multiple steps then I'd definatly pick the strongest isolation possible. if this was SQLServer you could put all the SQL in one cfquery seperated by ";" and bypass the whole issue but MSAccess/Jet db can't do that so separate query blocks it is. well, that's pretty much all I know about cftransaction (I've forgoten the rest at the moment...). as I said earlier, if anyone has additional info or corrections please speak up (no, they're all busy hasseling Taco or Steve Onnis - go figure!) good luck Andrew cheers barry.b --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/ Register now for the 3rd National Conference on Tourism Futures, being held in Townsville, North Queensland 4-7 August - www.tq.com.au/tfconf --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/
