RE: cftransaction quick question
> 1) You must never, never, _never_ prompt for user response > inside a transaction! The Database 101 Fairy will smack you > upside your head with his widdoo wand! Adam, do you mind if I borrow that line? It's a keeper! Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftransaction quick question
> Are you saying that this type of code is > wrong/inefficient/bad practice? > > > > > > some action sql > > > > > > > > > > > > > > > What would be a proper example of the above type of code > if so? While I doubt I can better Adam Churvis' response, this has got to be one of my pet peeves - I see it all the time, and it's completely unnecessary in most cases. The use of CFTRY/CFCATCH isn't necessary within the transaction, even if there were multiple queries within the CFTRY problem code area, because if any of the queries fail, the transaction will automatically roll back the changes made by previous queries to the initial state before the transaction began. In fact, there's rarely a need to use CFTRY/CFCATCH or other conditional CFML logic within a transaction, unless your goal is to use outside factors (by outside factors, I mean information not stored by the database) to affect the processing of the transaction. For example, you might have something like this: ... SQL ... ... SQL ... But even with this usage, I've never seen a case where it made any sense to do this, rather than simply restructuring the logic a bit. If the transaction itself fails, it'll return an error, and you could wrap the entire CFTRANSACTION within a CFTRY/CFCATCH block, but in many cases you might be better served to just capture it with CFERROR TYPE="DATABASE". Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cftransaction quick question
> hmm, I think you will be shocked by what db system schema people will > generate etc... ;-) What do you mean? Respectfully, Adam Phillip Churvis Member of Team Macromedia Advanced Intensive ColdFusion MX Training ColdFusion MX Master Class: July 14 - 18, 2003 http://www.ColdFusionTraining.com Download CommerceBlocks V2.1 and LoRCAT from http://www.ProductivityEnhancement.com The ColdFusion MX Bible is in bookstores now! ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cftransaction quick question
> Are you saying that this type of code is wrong/inefficient/bad practice? It is wrong. You only have one statement, so it is an atomic action in and of itself. This makes the statement an implicit transaction as far as the database is concerned, so it need not be defined as part of an explicit transaction. The mechanism you're using for CFTRANSACTION is fine, though. > Wot do you mean by long and short form of CFTRANSACTION tho? The short form of CFTRANSACTION uses only a simple paired CFTRANSACTION tag surrounding multiple statements. The long form adds attributes for the action being performed, which further enables you to nest CFTRANSACTION tags (with "slash-bracket" shorthand closing) inside that commit or rollback based on your own logic. You're doing that in your example. Respectfully, Adam Phillip Churvis Member of Team Macromedia Advanced Intensive ColdFusion MX Training ColdFusion MX Master Class: July 14 - 18, 2003 http://www.ColdFusionTraining.com Download CommerceBlocks V2.1 and LoRCAT from http://www.ProductivityEnhancement.com The ColdFusion MX Bible is in bookstores now! ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftransaction quick question
hmm, I think you will be shocked by what db system schema people will generate etc... ;-) -Original Message- From: Adam Churvis [mailto:[EMAIL PROTECTED] Sent: 09 July 2003 17:23 To: CF-Talk Subject: Re: cftransaction quick question > what if you want to perform an action, open a transaction , view the results > and the commit/rollback depending on status? 1) You must never, never, _never_ prompt for user response inside a transaction! The Database 101 Fairy will smack you upside your head with his widdoo wand! Think about what's happening on the backend while your user makes up his mind (or goes to lunch). This isn't an ISAM system, and he isn't the only user. Besides, you can't do this from a ColdFusion interface; this is typically a mistake made by people running stateful connections to a database, such as with Query Analyzer or a program written in Java or C++ that maintains a constant connection throughout the user session. 2) All decisions to commit or rollback must be made inside the transaction, and be derived from data available to the transaction. 3) What you're trying to accomplish should be implemented through a multi-step process, where all interim steps keep some status attribute in the pending rows to some "incomplete" value, then a later step either updates that status value to "complete" or deletes them (or demotes their status values). Respectfully, Adam Phillip Churvis Member of Team Macromedia Advanced Intensive ColdFusion MX Training ColdFusion MX Master Class: July 14 - 18, 2003 http://www.ColdFusionTraining.com Download CommerceBlocks V2.1 and LoRCAT from http://www.ProductivityEnhancement.com The ColdFusion MX Bible is in bookstores now! ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cftransaction quick question
> what if you want to perform an action, open a transaction , view the results > and the commit/rollback depending on status? 1) You must never, never, _never_ prompt for user response inside a transaction! The Database 101 Fairy will smack you upside your head with his widdoo wand! Think about what's happening on the backend while your user makes up his mind (or goes to lunch). This isn't an ISAM system, and he isn't the only user. Besides, you can't do this from a ColdFusion interface; this is typically a mistake made by people running stateful connections to a database, such as with Query Analyzer or a program written in Java or C++ that maintains a constant connection throughout the user session. 2) All decisions to commit or rollback must be made inside the transaction, and be derived from data available to the transaction. 3) What you're trying to accomplish should be implemented through a multi-step process, where all interim steps keep some status attribute in the pending rows to some "incomplete" value, then a later step either updates that status value to "complete" or deletes them (or demotes their status values). Respectfully, Adam Phillip Churvis Member of Team Macromedia Advanced Intensive ColdFusion MX Training ColdFusion MX Master Class: July 14 - 18, 2003 http://www.ColdFusionTraining.com Download CommerceBlocks V2.1 and LoRCAT from http://www.ProductivityEnhancement.com The ColdFusion MX Bible is in bookstores now! ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftransaction quick question
You can certainly do SELECT statements within a transaction, and then depending on what is returned, decided whether or not to rollback or commit. If you want to get user input, that's impossible. There's no way to bridge a transaction across multiple requests. You'd have to manually undo the transaction if the user decided they didn't want it. barneyb --- Barney Boisvert, Senior Development Engineer AudienceCentral [EMAIL PROTECTED] voice : 360.756.8080 x12 fax : 360.647.5351 www.audiencecentral.com > -Original Message- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: Wednesday, July 09, 2003 9:10 AM > To: CF-Talk > Subject: RE: cftransaction quick question > > > what if you want to perform an action, open a transaction , view > the results > and the commit/rollback depending on status? > > -Original Message- > From: Adam Churvis [mailto:[EMAIL PROTECTED] > Sent: 09 July 2003 17:02 > To: CF-Talk > Subject: Re: cftransaction quick question > > > > I would test this carefully because I'm not convinced that CFTRANSACTION > can be used to control the actions of a SP. I believe it is for CFQUERY > only. Can anyone confirm this? > > > > Yes, you can return success/failure codes from the SP to CF, but at that > point all the SP's queries have executed and committed (or rolledback, if > the code to handle the transaction was in the SP code) and I don't see how > CFTRANSACTION can alter that. > > > > It's important because if someone assumes they can rollback what a SP > does, but then can't...KA-BOOM! > > Guys, > > I'm sorry to say that most of this conversation is way off base, and there > are many misconceptions being thrown about here, both about > transactions and > about exception handling. > > While I can't repeat the entire sections on these topics from our Advanced > Database course, I can give you a couple of important pointers: > > 1) A transaction defines an atomic action, such that all work defined as > part of the transaction must successfully complete or none of that work > should have any effect whatsoever. > > 2) A single update, insert, or delete is performed on a single > set of data, > and since SQL is a set-based language, the success or failure of such a > statement is itself an atomic action and needs no further > definition, i.e., > it does not have to be part of a transaction if it is to be executed in > isolation of other statements. > > 3) Transactions must be controlled as close to the data as possible, so > stored procedures must have their own transactional controls and either > succeed or fail as a single call. Don't "return an error code"; > just throw > an exception and let structured exception handling "handle" it. And don't > use CFTRY for what CFERROR is supposed to do. > > 4) CFTRANSACTION is for those times when you must make separate calls > between CF and the DB. For example, inserting a sales order row and then > looping over the line items in a shopping cart structure and repeatedly > inserting line item rows related to the sales order just > inserted, where the > sales order ID is equal to the identity column value returned by > the insert > trigger on the sales order table. In this case, you would wrap both the > single sales order insert and the loop with the line item inserts in a > CFTRANSACTION (always use the long form, rather than a simple paired > CFTRANSACTION tag); this signals through the database driver that > you intend > for the database to consider these statements as a single atomic action. > > I hope this helps, but if you're looking for more details, I > cover all this > in the ColdFusion MX Bible. I also show the only reliable way to > structure > a CFTRANSACTION call in MX. > > For those interested, we have one remaining seat in our Advanced Database > class in August. We were full, but one student's company ran out > of funding > at the last minute and he had to drop. We limit our classes to only three > students, and you have two instructors (David and I). The class is a > killer, and goes Monday-Friday, 8AM to 7PM each day. > > Respectfully, > > Adam Phillip Churvis > Member of Team Macromedia > > Advanced Intensive ColdFusion MX Training > Advanced Development with ColdFusion MX and SQL Server 2000: > August 11 - 15, 2003 > http://www.ColdFusionTraining.com > > Download CommerceBlocks V2.1 and LoRCAT from > http://www.ProductivityEnhancement.com > > The ColdFusion MX Bible is in bookstores now! > > > > ~
RE: cftransaction quick question
Hi Adam, Cheers for the pointers, as usual a few queries > 3) Transactions must be controlled as close to the data as > possible, so stored procedures must have their own > transactional controls and either succeed or fail as a single > call. Don't "return an error code"; just throw an exception > and let structured exception handling "handle" it. And don't > use CFTRY for what CFERROR is supposed to do. Are you saying that this type of code is wrong/inefficient/bad practice? some action sql What would be a proper example of the above type of code if so? > 4) CFTRANSACTION is for those times when you must make > separate calls between CF and the DB. For example, inserting > a sales order row and then looping over the line items in a > shopping cart structure and repeatedly inserting line item > rows related to the sales order just inserted, where the > sales order ID is equal to the identity column value returned > by the insert trigger on the sales order table. In this > case, you would wrap both the single sales order insert and > the loop with the line item inserts in a CFTRANSACTION > (always use the long form, rather than a simple paired > CFTRANSACTION tag); this signals through the database driver > that you intend for the database to consider these statements > as a single atomic action. I am kinda getting this point. but still a bit foggy Wot do you mean by long and short form of CFTRANSACTION tho? Cheers DC (Developing in CF5, Oracle 8.1.7, IIS 5) ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftransaction quick question
what if you want to perform an action, open a transaction , view the results and the commit/rollback depending on status? -Original Message- From: Adam Churvis [mailto:[EMAIL PROTECTED] Sent: 09 July 2003 17:02 To: CF-Talk Subject: Re: cftransaction quick question > I would test this carefully because I'm not convinced that CFTRANSACTION can be used to control the actions of a SP. I believe it is for CFQUERY only. Can anyone confirm this? > > Yes, you can return success/failure codes from the SP to CF, but at that point all the SP's queries have executed and committed (or rolledback, if the code to handle the transaction was in the SP code) and I don't see how CFTRANSACTION can alter that. > > It's important because if someone assumes they can rollback what a SP does, but then can't...KA-BOOM! Guys, I'm sorry to say that most of this conversation is way off base, and there are many misconceptions being thrown about here, both about transactions and about exception handling. While I can't repeat the entire sections on these topics from our Advanced Database course, I can give you a couple of important pointers: 1) A transaction defines an atomic action, such that all work defined as part of the transaction must successfully complete or none of that work should have any effect whatsoever. 2) A single update, insert, or delete is performed on a single set of data, and since SQL is a set-based language, the success or failure of such a statement is itself an atomic action and needs no further definition, i.e., it does not have to be part of a transaction if it is to be executed in isolation of other statements. 3) Transactions must be controlled as close to the data as possible, so stored procedures must have their own transactional controls and either succeed or fail as a single call. Don't "return an error code"; just throw an exception and let structured exception handling "handle" it. And don't use CFTRY for what CFERROR is supposed to do. 4) CFTRANSACTION is for those times when you must make separate calls between CF and the DB. For example, inserting a sales order row and then looping over the line items in a shopping cart structure and repeatedly inserting line item rows related to the sales order just inserted, where the sales order ID is equal to the identity column value returned by the insert trigger on the sales order table. In this case, you would wrap both the single sales order insert and the loop with the line item inserts in a CFTRANSACTION (always use the long form, rather than a simple paired CFTRANSACTION tag); this signals through the database driver that you intend for the database to consider these statements as a single atomic action. I hope this helps, but if you're looking for more details, I cover all this in the ColdFusion MX Bible. I also show the only reliable way to structure a CFTRANSACTION call in MX. For those interested, we have one remaining seat in our Advanced Database class in August. We were full, but one student's company ran out of funding at the last minute and he had to drop. We limit our classes to only three students, and you have two instructors (David and I). The class is a killer, and goes Monday-Friday, 8AM to 7PM each day. Respectfully, Adam Phillip Churvis Member of Team Macromedia Advanced Intensive ColdFusion MX Training Advanced Development with ColdFusion MX and SQL Server 2000: August 11 - 15, 2003 http://www.ColdFusionTraining.com Download CommerceBlocks V2.1 and LoRCAT from http://www.ProductivityEnhancement.com The ColdFusion MX Bible is in bookstores now! ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cftransaction quick question
> I would test this carefully because I'm not convinced that CFTRANSACTION can be used to control the actions of a SP. I believe it is for CFQUERY only. Can anyone confirm this? > > Yes, you can return success/failure codes from the SP to CF, but at that point all the SP's queries have executed and committed (or rolledback, if the code to handle the transaction was in the SP code) and I don't see how CFTRANSACTION can alter that. > > It's important because if someone assumes they can rollback what a SP does, but then can't...KA-BOOM! Guys, I'm sorry to say that most of this conversation is way off base, and there are many misconceptions being thrown about here, both about transactions and about exception handling. While I can't repeat the entire sections on these topics from our Advanced Database course, I can give you a couple of important pointers: 1) A transaction defines an atomic action, such that all work defined as part of the transaction must successfully complete or none of that work should have any effect whatsoever. 2) A single update, insert, or delete is performed on a single set of data, and since SQL is a set-based language, the success or failure of such a statement is itself an atomic action and needs no further definition, i.e., it does not have to be part of a transaction if it is to be executed in isolation of other statements. 3) Transactions must be controlled as close to the data as possible, so stored procedures must have their own transactional controls and either succeed or fail as a single call. Don't "return an error code"; just throw an exception and let structured exception handling "handle" it. And don't use CFTRY for what CFERROR is supposed to do. 4) CFTRANSACTION is for those times when you must make separate calls between CF and the DB. For example, inserting a sales order row and then looping over the line items in a shopping cart structure and repeatedly inserting line item rows related to the sales order just inserted, where the sales order ID is equal to the identity column value returned by the insert trigger on the sales order table. In this case, you would wrap both the single sales order insert and the loop with the line item inserts in a CFTRANSACTION (always use the long form, rather than a simple paired CFTRANSACTION tag); this signals through the database driver that you intend for the database to consider these statements as a single atomic action. I hope this helps, but if you're looking for more details, I cover all this in the ColdFusion MX Bible. I also show the only reliable way to structure a CFTRANSACTION call in MX. For those interested, we have one remaining seat in our Advanced Database class in August. We were full, but one student's company ran out of funding at the last minute and he had to drop. We limit our classes to only three students, and you have two instructors (David and I). The class is a killer, and goes Monday-Friday, 8AM to 7PM each day. Respectfully, Adam Phillip Churvis Member of Team Macromedia Advanced Intensive ColdFusion MX Training Advanced Development with ColdFusion MX and SQL Server 2000: August 11 - 15, 2003 http://www.ColdFusionTraining.com Download CommerceBlocks V2.1 and LoRCAT from http://www.ProductivityEnhancement.com The ColdFusion MX Bible is in bookstores now! ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftransaction quick question
Point noted;-) > -Original Message- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: 09 July 2003 15:38 > To: CF-Talk > Subject: RE: cftransaction quick question > > > you do in the UK...it will land you a fine! > > -Original Message- > From: David Collie (itndac) [mailto:[EMAIL PROTECTED] > Sent: 09 July 2003 15:35 > To: CF-Talk > Subject: RE: cftransaction quick question > > > Thanks Neil, I do agree with you though, > > Crap analogy but you don't always need to indicate in > your car, but you always do don't you? > > > -Original Message- > > From: Robertson-Ravo, Neil (RX) > > [mailto:[EMAIL PROTECTED] > > Sent: 09 July 2003 15:22 > > To: CF-Talk > > Subject: RE: cftransaction quick question > > > > > > no, its not required, but its simply good practices > > > > -----Original Message----- > > From: David Collie (itndac) [mailto:[EMAIL PROTECTED] > > Sent: 09 July 2003 15:21 > > To: CF-Talk > > Subject: RE: cftransaction quick question > > > > > > > well then, from my experience with Oracle etc...you should always > > > need to use a transaction type method > > > > Sorry for labouring the point, but you say 'you should always > > need' This implies that the transactions is REQUIRED, > > whereas I do have legacy code (oh ... allright some of my > > lazy code as well :-) that does not have cftransaction.. > > and it works.. > > > > Chris, I am assuming that you are right when you say that > > CFTRANSACTION will have no effect over the workings of a > > stored procedure? > > > > Cheers for the info everyone... > > > > DC (Developing in CF5, Oracle 8.1.7, IIS 5) > > > > > > > Hi > > > > > > Soz guys woz in a meeting.. > > > > > > I was talking originally about Oracle NOT MySQL (although did > > > MySQL not get to handle transactions by version 4ish?) > Yup, I agree > > > that cftransaction needs to have a cftry/cfcatch inside > it I wasn't > > > wanting to comment on the effect cftransaction had on a > SP cos not > > > used them much in my lifetime other than to call them. DBA takes > > > care of that type of stuff on our Enterprise database > > > > > > I guess my thinking was, 'Is there any point to the > cftransaction in > > > the following code?' > > > > > > > > > > > > > > > > > > some action sql > > > > > > > > > > > > > > > > > > > > > > > > > > > Guys, would you suggest wrapping your SELECT SQL in > cftry's as well? > > > > > > On the note about making sure that the database supports > > > transactions, I would recommend coding for it all the > time no matter > > > the database, as in my experience using the tag on a > database that > > > does not support it does not cause any errors and will > bide well for > > > any future upgrades > > > > > > DC (Developing in CF5, Oracle 8.1.7, IIS 5) > > > > > > > -Original Message- > > > > From: Robertson-Ravo, Neil (RX) > > > > > > > > Sorry, is this for mySQL? if so, ignore all of the stuff > > regarding > > > > SP's! > > > > > > > > > > > > > > > > -Original Message- > > > > From: Jordan Thomas > > > > > > > > Yes, but if you want it to work then make sure to use > > either InnoDB > > > > or one of the other transaction safe tables. You will need to > > > > configure this manually. There is a good amound of docs > > on how to do > > > > this on www.mysql.com > > > > > > > > cheers > > > > > > > > Jordan > > > > > > -Original Message- > > > > From: Lofback, Chris > > > > > - No need for cftransaction on a single SQL query > > > > > > > > True, but I would always use a CFCATCH to trap any errors. > > > > > > > > > - cftansaction is good practice for a single stored > > > > procedure call in > > > > > case the stored procedure is doing multiple SQL queries >
RE: cftransaction quick question
you do in the UK...it will land you a fine! -Original Message- From: David Collie (itndac) [mailto:[EMAIL PROTECTED] Sent: 09 July 2003 15:35 To: CF-Talk Subject: RE: cftransaction quick question Thanks Neil, I do agree with you though, Crap analogy but you don't always need to indicate in your car, but you always do don't you? > -Original Message- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: 09 July 2003 15:22 > To: CF-Talk > Subject: RE: cftransaction quick question > > > no, its not required, but its simply good practices > > -Original Message- > From: David Collie (itndac) [mailto:[EMAIL PROTECTED] > Sent: 09 July 2003 15:21 > To: CF-Talk > Subject: RE: cftransaction quick question > > > > well then, from my experience with Oracle etc...you should > > always need to use a transaction type method > > Sorry for labouring the point, but you say 'you should always > need' This implies that the transactions is REQUIRED, > whereas I do have legacy code (oh ... allright some of my > lazy code as well :-) that does not have cftransaction.. > and it works.. > > Chris, I am assuming that you are right when you say that > CFTRANSACTION will have no effect over the workings of a > stored procedure? > > Cheers for the info everyone... > > DC (Developing in CF5, Oracle 8.1.7, IIS 5) > > > > Hi > > > > Soz guys woz in a meeting.. > > > > I was talking originally about Oracle NOT MySQL (although > > did MySQL not get to handle transactions by version 4ish?) > > Yup, I agree that cftransaction needs to have a cftry/cfcatch > > inside it I wasn't wanting to comment on the effect > > cftransaction had on a SP cos not used them much in my > > lifetime other than to call them. DBA takes care of that > > type of stuff on our Enterprise database > > > > I guess my thinking was, 'Is there any point to the > > cftransaction in the following code?' > > > > > > > > > > > > some action sql > > > > > > > > > > > > > > > > > > Guys, would you suggest wrapping your SELECT SQL in cftry's as well? > > > > On the note about making sure that the database supports > > transactions, I would recommend coding for it all the time no > > matter the database, as in my experience using the tag on a > > database that does not support it does not cause any errors > > and will bide well for any future upgrades > > > > DC (Developing in CF5, Oracle 8.1.7, IIS 5) > > > > > -Original Message- > > > From: Robertson-Ravo, Neil (RX) > > > > > > Sorry, is this for mySQL? if so, ignore all of the stuff > regarding > > > SP's! > > > > > > > > > > > > -Original Message- > > > From: Jordan Thomas > > > > > > Yes, but if you want it to work then make sure to use > either InnoDB > > > or one of the other transaction safe tables. You will need to > > > configure this manually. There is a good amound of docs > on how to do > > > this on www.mysql.com > > > > > > cheers > > > > > > Jordan > > > > -Original Message- > > > From: Lofback, Chris > > > > - No need for cftransaction on a single SQL query > > > > > > True, but I would always use a CFCATCH to trap any errors. > > > > > > > - cftansaction is good practice for a single stored > > > procedure call in > > > > case the stored procedure is doing multiple SQL queries > > > > > > I don't know if you read my previous post, but I don't > think this is > > > true. CFTRANSACTION cannot "talk" to the code inside a > SP. You must > > > write the transaction handling *within* the SP. So if > the SP does > > > 10 mods to the database, each mod within the SP must be > checked for > > > success/failure and then rolledback/committed by the SP > code itself. > > > CFTRANSACTION cannot be used to do this. > > > > > > Chris > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftransaction quick question
Thanks Neil, I do agree with you though, Crap analogy but you don't always need to indicate in your car, but you always do don't you? > -Original Message- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: 09 July 2003 15:22 > To: CF-Talk > Subject: RE: cftransaction quick question > > > no, its not required, but its simply good practices > > -Original Message- > From: David Collie (itndac) [mailto:[EMAIL PROTECTED] > Sent: 09 July 2003 15:21 > To: CF-Talk > Subject: RE: cftransaction quick question > > > > well then, from my experience with Oracle etc...you should > > always need to use a transaction type method > > Sorry for labouring the point, but you say 'you should always > need' This implies that the transactions is REQUIRED, > whereas I do have legacy code (oh ... allright some of my > lazy code as well :-) that does not have cftransaction.. > and it works.. > > Chris, I am assuming that you are right when you say that > CFTRANSACTION will have no effect over the workings of a > stored procedure? > > Cheers for the info everyone... > > DC (Developing in CF5, Oracle 8.1.7, IIS 5) > > > > Hi > > > > Soz guys woz in a meeting.. > > > > I was talking originally about Oracle NOT MySQL (although > > did MySQL not get to handle transactions by version 4ish?) > > Yup, I agree that cftransaction needs to have a cftry/cfcatch > > inside it I wasn't wanting to comment on the effect > > cftransaction had on a SP cos not used them much in my > > lifetime other than to call them. DBA takes care of that > > type of stuff on our Enterprise database > > > > I guess my thinking was, 'Is there any point to the > > cftransaction in the following code?' > > > > > > > > > > > > some action sql > > > > > > > > > > > > > > > > > > Guys, would you suggest wrapping your SELECT SQL in cftry's as well? > > > > On the note about making sure that the database supports > > transactions, I would recommend coding for it all the time no > > matter the database, as in my experience using the tag on a > > database that does not support it does not cause any errors > > and will bide well for any future upgrades > > > > DC (Developing in CF5, Oracle 8.1.7, IIS 5) > > > > > -Original Message- > > > From: Robertson-Ravo, Neil (RX) > > > > > > Sorry, is this for mySQL? if so, ignore all of the stuff > regarding > > > SP's! > > > > > > > > > > > > -Original Message- > > > From: Jordan Thomas > > > > > > Yes, but if you want it to work then make sure to use > either InnoDB > > > or one of the other transaction safe tables. You will need to > > > configure this manually. There is a good amound of docs > on how to do > > > this on www.mysql.com > > > > > > cheers > > > > > > Jordan > > > > -Original Message- > > > From: Lofback, Chris > > > > - No need for cftransaction on a single SQL query > > > > > > True, but I would always use a CFCATCH to trap any errors. > > > > > > > - cftansaction is good practice for a single stored > > > procedure call in > > > > case the stored procedure is doing multiple SQL queries > > > > > > I don't know if you read my previous post, but I don't > think this is > > > true. CFTRANSACTION cannot "talk" to the code inside a > SP. You must > > > write the transaction handling *within* the SP. So if > the SP does > > > 10 mods to the database, each mod within the SP must be > checked for > > > success/failure and then rolledback/committed by the SP > code itself. > > > CFTRANSACTION cannot be used to do this. > > > > > > Chris > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftransaction quick question
I would test this carefully because I'm not convinced that CFTRANSACTION can be used to control the actions of a SP. I believe it is for CFQUERY only. Can anyone confirm this? Yes, you can return success/failure codes from the SP to CF, but at that point all the SP's queries have executed and committed (or rolledback, if the code to handle the transaction was in the SP code) and I don't see how CFTRANSACTION can alter that. It's important because if someone assumes they can rollback what a SP does, but then can't...KA-BOOM! Chris > -Original Message- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: Wednesday, July 09, 2003 9:43 AM > To: CF-Talk > Subject: RE: cftransaction quick question > > > -- No need for cftransaction on a single SQL query > > >> True, but to avoid ugly error I would suggest using > cftry/cfcatch to > catch db errors > > - cftansaction is good practice for a single stored procedure call in > -- case the stored procedure is doing multiple SQL queries > > >> cftransaction should ALWAYS be used for db calls / procedures which > perform updates/detes or inserts. its simple good practice and avoids > errors. > > > > > > > > -Original Message- > From: David Collie (itndac) [mailto:[EMAIL PROTECTED] > Sent: 09 July 2003 14:26 > To: CF-Talk > Subject: RE: cftransaction quick question > > > Oops, good point Neil... > > I was initially thinking that it would rather than > > > So a little bit like this? > > - No need for cftransaction on a single SQL query > - cftansaction is good practice for a single stored procedure call in > case the stored procedure is doing multiple SQL queries > > DC > > > -Original Message- > > From: Robertson-Ravo, Neil (RX) > > [mailto:[EMAIL PROTECTED] > > Sent: 09 July 2003 14:07 > > To: CF-Talk > > Subject: RE: cftransaction quick question > > > > > > No true (well at least for Stored Procedures) as what if the > > SP does 10 other things before the actual failure? i.e. if > > you are simply doing one update - then no need for a rollback > > / update etc BUT for an SP you do require at least a commit > > or rollback. > > > > > > > > > > -Original Message- > > From: Lofback, Chris [mailto:[EMAIL PROTECTED] > > Sent: 09 July 2003 14:04 > > To: CF-Talk > > Subject: RE: cftransaction quick question > > > > > > > My thinking is that if the single query fails, there > > wouldn't be any > > > need for a rollback how does that sound? > > > > Sounds good to me. AFIK, a query either succeeds or > > fails--there's no such thing as partial success in a SQL > > statement. So if the query fails, there's no change in the > > DB that would need to be rolled back. > > > > Chris > > > > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftransaction quick question
no, its not required, but its simply good practices -Original Message- From: David Collie (itndac) [mailto:[EMAIL PROTECTED] Sent: 09 July 2003 15:21 To: CF-Talk Subject: RE: cftransaction quick question > well then, from my experience with Oracle etc...you should > always need to use a transaction type method Sorry for labouring the point, but you say 'you should always need' This implies that the transactions is REQUIRED, whereas I do have legacy code (oh ... allright some of my lazy code as well :-) that does not have cftransaction.. and it works.. Chris, I am assuming that you are right when you say that CFTRANSACTION will have no effect over the workings of a stored procedure? Cheers for the info everyone... DC (Developing in CF5, Oracle 8.1.7, IIS 5) > Hi > > Soz guys woz in a meeting.. > > I was talking originally about Oracle NOT MySQL (although > did MySQL not get to handle transactions by version 4ish?) > Yup, I agree that cftransaction needs to have a cftry/cfcatch > inside it I wasn't wanting to comment on the effect > cftransaction had on a SP cos not used them much in my > lifetime other than to call them. DBA takes care of that > type of stuff on our Enterprise database > > I guess my thinking was, 'Is there any point to the > cftransaction in the following code?' > > > > > > some action sql > > > > > > > > > Guys, would you suggest wrapping your SELECT SQL in cftry's as well? > > On the note about making sure that the database supports > transactions, I would recommend coding for it all the time no > matter the database, as in my experience using the tag on a > database that does not support it does not cause any errors > and will bide well for any future upgrades > > DC (Developing in CF5, Oracle 8.1.7, IIS 5) > > > -Original Message- > > From: Robertson-Ravo, Neil (RX) > > > > Sorry, is this for mySQL? if so, ignore all of the stuff > > regarding SP's! > > > > > > > > -Original Message- > > From: Jordan Thomas > > > > Yes, but if you want it to work then make sure to use either > > InnoDB or one of the other transaction safe tables. You will > > need to configure this manually. There is a good amound of > > docs on how to do this on www.mysql.com > > > > cheers > > > > Jordan > > -Original Message- > > From: Lofback, Chris > > > - No need for cftransaction on a single SQL query > > > > True, but I would always use a CFCATCH to trap any errors. > > > > > - cftansaction is good practice for a single stored > > procedure call in > > > case the stored procedure is doing multiple SQL queries > > > > I don't know if you read my previous post, but I don't think > > this is true. CFTRANSACTION cannot "talk" to the code inside > > a SP. You must write the transaction handling *within* the > > SP. So if the SP does 10 mods to the database, each mod > > within the SP must be checked for success/failure and then > > rolledback/committed by the SP code itself. CFTRANSACTION > > cannot be used to do this. > > > > Chris ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftransaction quick question
Ooops, replied to the wrong message. This was in reply to Bud's question about CFTransaction and MySQL. (http://www.houseoffusion.com/lists.cfm?link=m:4:25355:127593) Sorry. cheers Jordan -Original Message- From: Jordan Thomas [mailto:[EMAIL PROTECTED] Sent: Wednesday, 9 July 2003 3:45 PM To: CF-Talk Subject: RE: cftransaction quick question Yes, but if you want it to work then make sure to use either InnoDB or one of the other transaction safe tables. You will need to configure this manually. There is a good amound of docs on how to do this on www.mysql.com cheers Jordan -Original Message- From: Lofback, Chris [mailto:[EMAIL PROTECTED] Sent: Wednesday, 9 July 2003 3:37 PM To: CF-Talk Subject: RE: cftransaction quick question > - No need for cftransaction on a single SQL query True, but I would always use a CFCATCH to trap any errors. > - cftansaction is good practice for a single stored procedure call in > case the stored procedure is doing multiple SQL queries I don't know if you read my previous post, but I don't think this is true. CFTRANSACTION cannot "talk" to the code inside a SP. You must write the transaction handling *within* the SP. So if the SP does 10 mods to the database, each mod within the SP must be checked for success/failure and then rolledback/committed by the SP code itself. CFTRANSACTION cannot be used to do this. Chris ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftransaction quick question
> well then, from my experience with Oracle etc...you should > always need to use a transaction type method Sorry for labouring the point, but you say 'you should always need' This implies that the transactions is REQUIRED, whereas I do have legacy code (oh ... allright some of my lazy code as well :-) that does not have cftransaction.. and it works.. Chris, I am assuming that you are right when you say that CFTRANSACTION will have no effect over the workings of a stored procedure? Cheers for the info everyone... DC (Developing in CF5, Oracle 8.1.7, IIS 5) > Hi > > Soz guys woz in a meeting.. > > I was talking originally about Oracle NOT MySQL (although > did MySQL not get to handle transactions by version 4ish?) > Yup, I agree that cftransaction needs to have a cftry/cfcatch > inside it I wasn't wanting to comment on the effect > cftransaction had on a SP cos not used them much in my > lifetime other than to call them. DBA takes care of that > type of stuff on our Enterprise database > > I guess my thinking was, 'Is there any point to the > cftransaction in the following code?' > > > > > > some action sql > > > > > > > > > Guys, would you suggest wrapping your SELECT SQL in cftry's as well? > > On the note about making sure that the database supports > transactions, I would recommend coding for it all the time no > matter the database, as in my experience using the tag on a > database that does not support it does not cause any errors > and will bide well for any future upgrades > > DC (Developing in CF5, Oracle 8.1.7, IIS 5) > > > -Original Message- > > From: Robertson-Ravo, Neil (RX) > > > > Sorry, is this for mySQL? if so, ignore all of the stuff > > regarding SP's! > > > > > > > > -Original Message- > > From: Jordan Thomas > > > > Yes, but if you want it to work then make sure to use either > > InnoDB or one of the other transaction safe tables. You will > > need to configure this manually. There is a good amound of > > docs on how to do this on www.mysql.com > > > > cheers > > > > Jordan > > -Original Message- > > From: Lofback, Chris > > > - No need for cftransaction on a single SQL query > > > > True, but I would always use a CFCATCH to trap any errors. > > > > > - cftansaction is good practice for a single stored > > procedure call in > > > case the stored procedure is doing multiple SQL queries > > > > I don't know if you read my previous post, but I don't think > > this is true. CFTRANSACTION cannot "talk" to the code inside > > a SP. You must write the transaction handling *within* the > > SP. So if the SP does 10 mods to the database, each mod > > within the SP must be checked for success/failure and then > > rolledback/committed by the SP code itself. CFTRANSACTION > > cannot be used to do this. > > > > Chris ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftransaction quick question
well then, from my experience with Oracle etc...you should always need to use a transaction type method -Original Message- From: David Collie (itndac) [mailto:[EMAIL PROTECTED] Sent: 09 July 2003 15:11 To: CF-Talk Subject: RE: cftransaction quick question Hi Soz guys woz in a meeting.. I was talking originally about Oracle NOT MySQL (although did MySQL not get to handle transactions by version 4ish?) Yup, I agree that cftransaction needs to have a cftry/cfcatch inside it I wasn't wanting to comment on the effect cftransaction had on a SP cos not used them much in my lifetime other than to call them. DBA takes care of that type of stuff on our Enterprise database I guess my thinking was, 'Is there any point to the cftransaction in the following code?' some action sql Guys, would you suggest wrapping your SELECT SQL in cftry's as well? On the note about making sure that the database supports transactions, I would recommend coding for it all the time no matter the database, as in my experience using the tag on a database that does not support it does not cause any errors and will bide well for any future upgrades DC (Developing in CF5, Oracle 8.1.7, IIS 5) > -Original Message- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: 09 July 2003 14:49 > To: CF-Talk > Subject: RE: cftransaction quick question > > > Sorry, is this for mySQL? if so, ignore all of the stuff > regarding SP's! > > > > -Original Message- > From: Jordan Thomas [mailto:[EMAIL PROTECTED] > Sent: 09 July 2003 14:45 > To: CF-Talk > Subject: RE: cftransaction quick question > > > Yes, but if you want it to work then make sure to use either > InnoDB or one of the other transaction safe tables. You will > need to configure this manually. There is a good amound of > docs on how to do this on www.mysql.com > > cheers > > Jordan > > -Original Message- > From: Lofback, Chris [mailto:[EMAIL PROTECTED] > Sent: Wednesday, 9 July 2003 3:37 PM > To: CF-Talk > Subject: RE: cftransaction quick question > > > > - No need for cftransaction on a single SQL query > > True, but I would always use a CFCATCH to trap any errors. > > > - cftansaction is good practice for a single stored > procedure call in > > case the stored procedure is doing multiple SQL queries > > I don't know if you read my previous post, but I don't think > this is true. CFTRANSACTION cannot "talk" to the code inside > a SP. You must write the transaction handling *within* the > SP. So if the SP does 10 mods to the database, each mod > within the SP must be checked for success/failure and then > rolledback/committed by the SP code itself. CFTRANSACTION > cannot be used to do this. > > Chris > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftransaction quick question
Hi Soz guys woz in a meeting.. I was talking originally about Oracle NOT MySQL (although did MySQL not get to handle transactions by version 4ish?) Yup, I agree that cftransaction needs to have a cftry/cfcatch inside it I wasn't wanting to comment on the effect cftransaction had on a SP cos not used them much in my lifetime other than to call them. DBA takes care of that type of stuff on our Enterprise database I guess my thinking was, 'Is there any point to the cftransaction in the following code?' some action sql Guys, would you suggest wrapping your SELECT SQL in cftry's as well? On the note about making sure that the database supports transactions, I would recommend coding for it all the time no matter the database, as in my experience using the tag on a database that does not support it does not cause any errors and will bide well for any future upgrades DC (Developing in CF5, Oracle 8.1.7, IIS 5) > -Original Message- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: 09 July 2003 14:49 > To: CF-Talk > Subject: RE: cftransaction quick question > > > Sorry, is this for mySQL? if so, ignore all of the stuff > regarding SP's! > > > > -Original Message- > From: Jordan Thomas [mailto:[EMAIL PROTECTED] > Sent: 09 July 2003 14:45 > To: CF-Talk > Subject: RE: cftransaction quick question > > > Yes, but if you want it to work then make sure to use either > InnoDB or one of the other transaction safe tables. You will > need to configure this manually. There is a good amound of > docs on how to do this on www.mysql.com > > cheers > > Jordan > > -Original Message- > From: Lofback, Chris [mailto:[EMAIL PROTECTED] > Sent: Wednesday, 9 July 2003 3:37 PM > To: CF-Talk > Subject: RE: cftransaction quick question > > > > - No need for cftransaction on a single SQL query > > True, but I would always use a CFCATCH to trap any errors. > > > - cftansaction is good practice for a single stored > procedure call in > > case the stored procedure is doing multiple SQL queries > > I don't know if you read my previous post, but I don't think > this is true. CFTRANSACTION cannot "talk" to the code inside > a SP. You must write the transaction handling *within* the > SP. So if the SP does 10 mods to the database, each mod > within the SP must be checked for success/failure and then > rolledback/committed by the SP code itself. CFTRANSACTION > cannot be used to do this. > > Chris > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftransaction quick question
Sorry, is this for mySQL? if so, ignore all of the stuff regarding SP's! -Original Message- From: Jordan Thomas [mailto:[EMAIL PROTECTED] Sent: 09 July 2003 14:45 To: CF-Talk Subject: RE: cftransaction quick question Yes, but if you want it to work then make sure to use either InnoDB or one of the other transaction safe tables. You will need to configure this manually. There is a good amound of docs on how to do this on www.mysql.com cheers Jordan -Original Message- From: Lofback, Chris [mailto:[EMAIL PROTECTED] Sent: Wednesday, 9 July 2003 3:37 PM To: CF-Talk Subject: RE: cftransaction quick question > - No need for cftransaction on a single SQL query True, but I would always use a CFCATCH to trap any errors. > - cftansaction is good practice for a single stored procedure call in > case the stored procedure is doing multiple SQL queries I don't know if you read my previous post, but I don't think this is true. CFTRANSACTION cannot "talk" to the code inside a SP. You must write the transaction handling *within* the SP. So if the SP does 10 mods to the database, each mod within the SP must be checked for success/failure and then rolledback/committed by the SP code itself. CFTRANSACTION cannot be used to do this. Chris ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftransaction quick question
Yes, but if you want it to work then make sure to use either InnoDB or one of the other transaction safe tables. You will need to configure this manually. There is a good amound of docs on how to do this on www.mysql.com cheers Jordan -Original Message- From: Lofback, Chris [mailto:[EMAIL PROTECTED] Sent: Wednesday, 9 July 2003 3:37 PM To: CF-Talk Subject: RE: cftransaction quick question > - No need for cftransaction on a single SQL query True, but I would always use a CFCATCH to trap any errors. > - cftansaction is good practice for a single stored procedure call in > case the stored procedure is doing multiple SQL queries I don't know if you read my previous post, but I don't think this is true. CFTRANSACTION cannot "talk" to the code inside a SP. You must write the transaction handling *within* the SP. So if the SP does 10 mods to the database, each mod within the SP must be checked for success/failure and then rolledback/committed by the SP code itself. CFTRANSACTION cannot be used to do this. Chris ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftransaction quick question
To add : mailto:[EMAIL PROTECTED] Sent: 09 July 2003 14:37 To: CF-Talk Subject: RE: cftransaction quick question > - No need for cftransaction on a single SQL query True, but I would always use a CFCATCH to trap any errors. > - cftansaction is good practice for a single stored procedure call in > case the stored procedure is doing multiple SQL queries I don't know if you read my previous post, but I don't think this is true. CFTRANSACTION cannot "talk" to the code inside a SP. You must write the transaction handling *within* the SP. So if the SP does 10 mods to the database, each mod within the SP must be checked for success/failure and then rolledback/committed by the SP code itself. CFTRANSACTION cannot be used to do this. Chris ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftransaction quick question
-- No need for cftransaction on a single SQL query >> True, but to avoid ugly error I would suggest using cftry/cfcatch to catch db errors - cftansaction is good practice for a single stored procedure call in -- case the stored procedure is doing multiple SQL queries >> cftransaction should ALWAYS be used for db calls / procedures which perform updates/detes or inserts. its simple good practice and avoids errors. -Original Message- From: David Collie (itndac) [mailto:[EMAIL PROTECTED] Sent: 09 July 2003 14:26 To: CF-Talk Subject: RE: cftransaction quick question Oops, good point Neil... I was initially thinking that it would rather than So a little bit like this? - No need for cftransaction on a single SQL query - cftansaction is good practice for a single stored procedure call in case the stored procedure is doing multiple SQL queries DC > -Original Message- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: 09 July 2003 14:07 > To: CF-Talk > Subject: RE: cftransaction quick question > > > No true (well at least for Stored Procedures) as what if the > SP does 10 other things before the actual failure? i.e. if > you are simply doing one update - then no need for a rollback > / update etc BUT for an SP you do require at least a commit > or rollback. > > > > > -Original Message- > From: Lofback, Chris [mailto:[EMAIL PROTECTED] > Sent: 09 July 2003 14:04 > To: CF-Talk > Subject: RE: cftransaction quick question > > > > My thinking is that if the single query fails, there > wouldn't be any > > need for a rollback how does that sound? > > Sounds good to me. AFIK, a query either succeeds or > fails--there's no such thing as partial success in a SQL > statement. So if the query fails, there's no change in the > DB that would need to be rolled back. > > Chris > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftransaction quick question
> - No need for cftransaction on a single SQL query True, but I would always use a CFCATCH to trap any errors. > - cftansaction is good practice for a single stored procedure call in > case the stored procedure is doing multiple SQL queries I don't know if you read my previous post, but I don't think this is true. CFTRANSACTION cannot "talk" to the code inside a SP. You must write the transaction handling *within* the SP. So if the SP does 10 mods to the database, each mod within the SP must be checked for success/failure and then rolledback/committed by the SP code itself. CFTRANSACTION cannot be used to do this. Chris ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftransaction quick question
Oops, good point Neil... I was initially thinking that it would rather than So a little bit like this? - No need for cftransaction on a single SQL query - cftansaction is good practice for a single stored procedure call in case the stored procedure is doing multiple SQL queries DC > -Original Message- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: 09 July 2003 14:07 > To: CF-Talk > Subject: RE: cftransaction quick question > > > No true (well at least for Stored Procedures) as what if the > SP does 10 other things before the actual failure? i.e. if > you are simply doing one update - then no need for a rollback > / update etc BUT for an SP you do require at least a commit > or rollback. > > > > > -Original Message- > From: Lofback, Chris [mailto:[EMAIL PROTECTED] > Sent: 09 July 2003 14:04 > To: CF-Talk > Subject: RE: cftransaction quick question > > > > My thinking is that if the single query fails, there > wouldn't be any > > need for a rollback how does that sound? > > Sounds good to me. AFIK, a query either succeeds or > fails--there's no such thing as partial success in a SQL > statement. So if the query fails, there's no change in the > DB that would need to be rolled back. > > Chris > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftransaction quick question
Well, he's asking specifically about CFQUERY... And as for an SP, wouldn't the transaction have to be controlled inside the SP itself? I don't see how a CFTRANSACTION in your template could control the internal working of a SP. Chris > -Original Message- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: Wednesday, July 09, 2003 9:07 AM > To: CF-Talk > Subject: RE: cftransaction quick question > > > No true (well at least for Stored Procedures) as what if the > SP does 10 > other things before the actual failure? i.e. if you are > simply doing one > update - then no need for a rollback / update etc BUT for an SP you do > require at least a commit or rollback. > > > > > -Original Message- > From: Lofback, Chris [mailto:[EMAIL PROTECTED] > Sent: 09 July 2003 14:04 > To: CF-Talk > Subject: RE: cftransaction quick question > > > > My thinking is that if the single query fails, there wouldn't be any > > need for a rollback how does that sound? > > Sounds good to me. AFIK, a query either succeeds or > fails--there's no such > thing as partial success in a SQL statement. So if the query > fails, there's > no change in the DB that would need to be rolled back. > > Chris > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftransaction quick question
nice, thanks :-) > -Original Message- > From: Lofback, Chris [mailto:[EMAIL PROTECTED] > Sent: 09 July 2003 14:04 > To: CF-Talk > Subject: RE: cftransaction quick question > > > > My thinking is that if the single query fails, there > wouldn't be any > > need for a rollback how does that sound? > > Sounds good to me. AFIK, a query either succeeds or > fails--there's no such thing as partial success in a SQL > statement. So if the query fails, there's no change in the > DB that would need to be rolled back. > > Chris > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftransaction quick question
No true (well at least for Stored Procedures) as what if the SP does 10 other things before the actual failure? i.e. if you are simply doing one update - then no need for a rollback / update etc BUT for an SP you do require at least a commit or rollback. -Original Message- From: Lofback, Chris [mailto:[EMAIL PROTECTED] Sent: 09 July 2003 14:04 To: CF-Talk Subject: RE: cftransaction quick question > My thinking is that if the single query fails, there wouldn't be any > need for a rollback how does that sound? Sounds good to me. AFIK, a query either succeeds or fails--there's no such thing as partial success in a SQL statement. So if the query fails, there's no change in the DB that would need to be rolled back. Chris ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftransaction quick question
> My thinking is that if the single query fails, there wouldn't be any > need for a rollback how does that sound? Sounds good to me. AFIK, a query either succeeds or fails--there's no such thing as partial success in a SQL statement. So if the query fails, there's no change in the DB that would need to be rolled back. Chris ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
