CFTRANSACTION with MS Access Database
Dear Fellows, I am trying to use CFTRANSACTION for a multiple insert query to the same datasource but obviously different tables. Now my need is that if any one of the INSERT query fails, all the other queries must be rolled-back. If all of them succeed, then the complete transaction should be committed. Also, I want to catch the DATABASE error if any occurs in the transaction using CFTRY for the smooth processing of my CFML page. Now I have tried something like the following code: cftry !---//Starting Transaction - cftransaction !---//Query##1 Insert cfquery name=insertDAP datasource=#data2# INSERT statement /cfquery !---//Query##2 Insert cfquery name=insertMW datasource=#data2# INSERT statement /cfquery /cftransaction cfcatch type=database !---//Actions to perform if DATABASE error occurs- cftransaction action=rollback / /cftry This code gives the error that the cftransaction tag with empty body must be nested inside another CFTRANSACTION block. If I do so, then I get an error for the CFTRY tag that it must have atleast one CFCATCH block. Any remedies where am I going wrong? Regards, Arsalan ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325024 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFTRANSACTION with MS Access Database
The CFTRANSACTION tag takes care of the top-level rollback all on it's own. The explicit rollback is only used if you have logic inside the CFTRANSACTION tag that needs to roll back because of some non-exception situation. So you'll get the behaviour you want if you remove the CFTRY..CFCATCH stuff and just use the main CFTRANSACTION block. cheers, barneyb On Mon, Jul 27, 2009 at 11:54 PM, Arsalan Tariq Keenarsalk...@hotmail.com wrote: Dear Fellows, I am trying to use CFTRANSACTION for a multiple insert query to the same datasource but obviously different tables. Now my need is that if any one of the INSERT query fails, all the other queries must be rolled-back. If all of them succeed, then the complete transaction should be committed. Also, I want to catch the DATABASE error if any occurs in the transaction using CFTRY for the smooth processing of my CFML page. Now I have tried something like the following code: cftry !---//Starting Transaction - cftransaction !---//Query##1 Insert cfquery name=insertDAP datasource=#data2# INSERT statement /cfquery !---//Query##2 Insert cfquery name=insertMW datasource=#data2# INSERT statement /cfquery /cftransaction cfcatch type=database !---//Actions to perform if DATABASE error occurs- cftransaction action=rollback / /cftry This code gives the error that the cftransaction tag with empty body must be nested inside another CFTRANSACTION block. If I do so, then I get an error for the CFTRY tag that it must have atleast one CFCATCH block. Any remedies where am I going wrong? Regards, Arsalan ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325025 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFTRANSACTION with MS Access Database
Thanks Barney will try that out :) I also found an old reply by you on a similar topic RE: cftransaction cftry order Barney Boisvert Thu, 29 May 2003 09:43:52 -0700 :) Regards, Arsalan -- From: Barney Boisvert bboisv...@gmail.com Sent: Tuesday, July 28, 2009 1:00 PM To: cf-talk cf-talk@houseoffusion.com Subject: Re: CFTRANSACTION with MS Access Database The CFTRANSACTION tag takes care of the top-level rollback all on it's own. The explicit rollback is only used if you have logic inside the CFTRANSACTION tag that needs to roll back because of some non-exception situation. So you'll get the behaviour you want if you remove the CFTRY..CFCATCH stuff and just use the main CFTRANSACTION block. cheers, barneyb On Mon, Jul 27, 2009 at 11:54 PM, Arsalan Tariq Keenarsalk...@hotmail.com wrote: Dear Fellows, I am trying to use CFTRANSACTION for a multiple insert query to the same datasource but obviously different tables. Now my need is that if any one of the INSERT query fails, all the other queries must be rolled-back. If all of them succeed, then the complete transaction should be committed. Also, I want to catch the DATABASE error if any occurs in the transaction using CFTRY for the smooth processing of my CFML page. Now I have tried something like the following code: cftry !---//Starting Transaction - cftransaction !---//Query##1 Insert cfquery name=insertDAP datasource=#data2# INSERT statement /cfquery !---//Query##2 Insert cfquery name=insertMW datasource=#data2# INSERT statement /cfquery /cftransaction cfcatch type=database !---//Actions to perform if DATABASE error occurs- cftransaction action=rollback / /cftry This code gives the error that the cftransaction tag with empty body must be nested inside another CFTRANSACTION block. If I do so, then I get an error for the CFTRY tag that it must have atleast one CFCATCH block. Any remedies where am I going wrong? Regards, Arsalan ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325026 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFTRANSACTION with MS Access Database
looks like /cfcatch is missing ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325027 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFTRANSACTION with MS Access Database
Guys cumbersome below is my code and still its not rolling back ... I have injected an error in the 2nd insert statement but the transaction doesn't rolls back... 1st insert takes place... 2nd one has error wat could be the problem ... dear experts??? !---//Starting Transaction - cftransaction cftry !---//QUERY1 Insert cfquery name=insertDAP datasource=#data2# INSERT STATEMENT 1 /cfquery !---//QUERY2 Insert cfquery name=insertMW datasource=#data2# INSERT STATEMENT 2 /cfquery !---//CFCATCH for Database errors -- cfcatch type=database cflog text=Error Code: #cfcatch.ErrorCode#, Error Message: #cfcatch.Message#, Detail: #cfcatch.Detail#, Extended Info: #cfcatch.ExtendedInfo#, Root Cause: , Type: #cfcatch.Type#, Native Error Code: #cfcatch.NativeErrorCode#, SQL State: #cfcatch.SQLState#, SQL: #cfcatch.SQL#, Querry Error: type=Error file=SI_ATP.log application=yes cftransaction action=rollback / /cfcatch /cftry /cftransaction -- From: Barney Boisvert bboisv...@gmail.com Sent: Tuesday, July 28, 2009 1:00 PM To: cf-talk cf-talk@houseoffusion.com Subject: Re: CFTRANSACTION with MS Access Database The CFTRANSACTION tag takes care of the top-level rollback all on it's own. The explicit rollback is only used if you have logic inside the CFTRANSACTION tag that needs to roll back because of some non-exception situation. So you'll get the behaviour you want if you remove the CFTRY..CFCATCH stuff and just use the main CFTRANSACTION block. cheers, barneyb On Mon, Jul 27, 2009 at 11:54 PM, Arsalan Tariq Keenarsalk...@hotmail.com wrote: Dear Fellows, I am trying to use CFTRANSACTION for a multiple insert query to the same datasource but obviously different tables. Now my need is that if any one of the INSERT query fails, all the other queries must be rolled-back. If all of them succeed, then the complete transaction should be committed. Also, I want to catch the DATABASE error if any occurs in the transaction using CFTRY for the smooth processing of my CFML page. Now I have tried something like the following code: cftry !---//Starting Transaction - cftransaction !---//Query##1 Insert cfquery name=insertDAP datasource=#data2# INSERT statement /cfquery !---//Query##2 Insert cfquery name=insertMW datasource=#data2# INSERT statement /cfquery /cftransaction cfcatch type=database !---//Actions to perform if DATABASE error occurs- cftransaction action=rollback / /cftry This code gives the error that the cftransaction tag with empty body must be nested inside another CFTRANSACTION block. If I do so, then I get an error for the CFTRY tag that it must have atleast one CFCATCH block. Any remedies where am I going wrong? Regards, Arsalan ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325031 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFTRANSACTION with MS Access Database
Guys cumbersome below is my code and still its not rolling back ... I have injected an error in the 2nd insert statement but the transaction doesn't rolls back... 1st insert takes place... 2nd one has error wat could be the problem ... dear experts??? !---//Starting Transaction - cftransaction cftry You're making this more complicated than it needs to be. As Barney previously stated, you don't need the CFTRY or the explicit rollback at all: cftransaction cfquery ... / cfquery ... / /cftransaction The transaction will automatically roll back the first query if the second fails. When you nest CFTRY in there, you're essentially turning CFTRANSACTION off. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more informat ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325034 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFTRANSACTION with MS Access Database
Try putting your CFTRY / CFCATCH around the outside of the CFTRANSACTION ... the error handler is not triggering your roll-back, I would guess. cftry !---//Starting Transaction - cftransaction !---//QUERY1 Insert cfquery name=insertDAP datasource=#data2# INSERT STATEMENT 1 /cfquery !---//QUERY2 Insert cfquery name=insertMW datasource=#data2# INSERT STATEMENT 2 /cfquery /cftransaction !---//CFCATCH for Database errors -- !--- note, no rollback here, just let the transaction fail above if there was an error --- cfcatch type=database cflog text=Error Code: #cfcatch.ErrorCode#, Error Message: #cfcatch.Message#, Detail: #cfcatch.Detail#, Extended Info: #cfcatch.ExtendedInfo#, Root Cause: , Type: #cfcatch.Type#, Native Error Code: #cfcatch.NativeErrorCode#, SQL State: #cfcatch.SQLState#, SQL: #cfcatch.SQL#, Querry Error: type=Error file=SI_ATP.log application=yes /cfcatch /cftry ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325037 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFTRANSACTION with MS Access Database
No guys this isn't working ... nor that what Barney and Dave advised... does anyone has a working example of such scenario? I would be very thankful if someone provides me with a somewhat similar working example and yes... I am using using CFTRY/CFCATCH because I need to log the errors and get a notification if a database exception occurs without breaking the flow of my page is there any alternate to CFCATCH/CFTRY for this purpose? -- From: Jason Fisher ja...@wanax.com Sent: Tuesday, July 28, 2009 10:50 PM To: cf-talk cf-talk@houseoffusion.com Subject: Re: CFTRANSACTION with MS Access Database Try putting your CFTRY / CFCATCH around the outside of the CFTRANSACTION ... the error handler is not triggering your roll-back, I would guess. cftry !---//Starting Transaction - cftransaction !---//QUERY1 Insert cfquery name=insertDAP datasource=#data2# INSERT STATEMENT 1 /cfquery !---//QUERY2 Insert cfquery name=insertMW datasource=#data2# INSERT STATEMENT 2 /cfquery /cftransaction !---//CFCATCH for Database errors -- !--- note, no rollback here, just let the transaction fail above if there was an error --- cfcatch type=database cflog text=Error Code: #cfcatch.ErrorCode#, Error Message: #cfcatch.Message#, Detail: #cfcatch.Detail#, Extended Info: #cfcatch.ExtendedInfo#, Root Cause: , Type: #cfcatch.Type#, Native Error Code: #cfcatch.NativeErrorCode#, SQL State: #cfcatch.SQLState#, SQL: #cfcatch.SQL#, Querry Error: type=Error file=SI_ATP.log application=yes /cfcatch /cftry ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325040 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFTRANSACTION with MS Access Database
try / catch is still the right way to capture errors, but if it's inside the transaction, then the TR won't fire. if this isn't working, though, then I gotta question whether Access supports transaction locking at all? It's been years since I used it, so I can't vouch one way or the other ... ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325042 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFTRANSACTION with MS Access Database
No guys this isn't working ... nor that what Barney and Dave advised... does anyone has a working example of such scenario? I would be very thankful if someone provides me with a somewhat similar working example and yes... I am using using CFTRY/CFCATCH because I need to log the errors and get a notification if a database exception occurs without breaking the flow of my page is there any alternate to CFCATCH/CFTRY for this purpose? You should be able to wrap the CFTRANSACTION itself in CFTRY/CFCATCH, or use CFERROR with the page; if the transaction fails, you should get an exception. That said, I've never had any problems with CFTRANSACTION around two queries using MS Access. It's actually covered in an exercise within the official Adobe course curriculum. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325049 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFTRANSACTION with MS Access Database
hey Dave... can you provide me with some literature or example code on this topic ... specially in regard to MS Access...? -- From: Dave Watts dwa...@figleaf.com Sent: Wednesday, July 29, 2009 12:34 AM To: cf-talk cf-talk@houseoffusion.com Subject: Re: CFTRANSACTION with MS Access Database No guys this isn't working ... nor that what Barney and Dave advised... does anyone has a working example of such scenario? I would be very thankful if someone provides me with a somewhat similar working example and yes... I am using using CFTRY/CFCATCH because I need to log the errors and get a notification if a database exception occurs without breaking the flow of my page is there any alternate to CFCATCH/CFTRY for this purpose? You should be able to wrap the CFTRANSACTION itself in CFTRY/CFCATCH, or use CFERROR with the page; if the transaction fails, you should get an exception. That said, I've never had any problems with CFTRANSACTION around two queries using MS Access. It's actually covered in an exercise within the official Adobe course curriculum. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325050 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFTRANSACTION with MS Access Database
Thanks a lot Dave, Barney and Jason ... it seems my code was perfectly fine .. it was a bug in Railo :) it has been removed in the server updates and now everything works fi9 Alhamdulillah ... :) Regards, Arsalan -- From: Arsalan Tariq Keen arsalk...@hotmail.com Sent: Wednesday, July 29, 2009 1:00 AM To: cf-talk cf-talk@houseoffusion.com Subject: Re: CFTRANSACTION with MS Access Database hey Dave... can you provide me with some literature or example code on this topic ... specially in regard to MS Access...? -- From: Dave Watts dwa...@figleaf.com Sent: Wednesday, July 29, 2009 12:34 AM To: cf-talk cf-talk@houseoffusion.com Subject: Re: CFTRANSACTION with MS Access Database No guys this isn't working ... nor that what Barney and Dave advised... does anyone has a working example of such scenario? I would be very thankful if someone provides me with a somewhat similar working example and yes... I am using using CFTRY/CFCATCH because I need to log the errors and get a notification if a database exception occurs without breaking the flow of my page is there any alternate to CFCATCH/CFTRY for this purpose? You should be able to wrap the CFTRANSACTION itself in CFTRY/CFCATCH, or use CFERROR with the page; if the transaction fails, you should get an exception. That said, I've never had any problems with CFTRANSACTION around two queries using MS Access. It's actually covered in an exercise within the official Adobe course curriculum. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325055 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
