cftransaction
Say I have code that is accesses multiple datasources. CFTransaction cannot be used across multiple datasources. My question is can I make two CFTransaction blocks and on the first one set a CFTransaction SavePoint. Then if the second CFTransation errors can I roll back the first CFTransaction save point? Dummmy code: Cftransaction start Cftransaction set a save point1 Do some queries on datasource 1 Cftransaction end Cftransaction start Cftry Cftransaction set save point2 Do some queries on datasource 2 Cfcatch Cftransaction rollback save point 1 Cftransaction rollback save point 2 Cftransaction end ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353039 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cftransaction
Say I have code that is accesses multiple datasources. CFTransaction cannot be used across multiple datasources. My question is can I make two CFTransaction blocks and on the first one set a CFTransaction SavePoint. Then if the second CFTransation errors can I roll back the first CFTransaction save point? No, I don't think you can do that. CF doesn't support distributed transactions. While CF 9 does allow nested transactions, they don't seem to let you do what you're trying to do here. What you could do - not that I'd recommend this - is have two transactions run essentially in parallel, have a wait/sleep loop in the first, and when the second one completes you could have it set a flag that can be read by the first one and then it could either commit or rollback. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353040 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cftransaction isolation=repeatable_read
Bump... Casey - is this method thread safe? -Original Message- From: Brook Davies [mailto:cft...@logiforms.com] Sent: December-13-11 10:15 PM To: cf-talk Subject: RE: cftransaction isolation=repeatable_read So this would update and select the rows at the same time? Would I need to use transaction or would this guarantee if another request for the same query at the exact same time happened, it would not return or update the same rows? Brook -Original Message- From: Casey Dougall [mailto:ca...@uberwebsitesolutions.com] Sent: December-13-11 6:29 PM To: cf-talk Subject: Re: cftransaction isolation=repeatable_read On Tue, Dec 13, 2011 at 6:13 PM, Brook Davies cft...@logiforms.com wrote: I guess my question is, will this lead to performance problems? Is there a more elegant way to do this? Are you using MS SQL server? If so you only need one query which will still output rows like the first query with OUTPUT. cfquery name=getEvents update t1 set t1.enabled = 0 OUTPUT inserted.ID, inserted.cfc, inserted.runMethod, inserted.arguments, inserted.frequency, inserted.frequencytype from scheduledTasks t1 inner join scheduledTasks t2 on t1.ID = t2.ID where t2.enabled = 1 and t2.nextDate cfqueryparam cfsqltype=cf_sql_timestamp value=#createodbcdatetime(Now())# and t2.startDate cfqueryparam cfsqltype=cf_sql_timestamp value=#createodbcdatetime(Now())# and t2.endDate cfqueryparam cfsqltype=cf_sql_timestamp value=#createodbcdatetime(Now())# /cfquery ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349127 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cftransaction isolation=repeatable_read
So this would update and select the rows at the same time? Would I need to use transaction or would this guarantee if another request for the same query at the exact same time happened, it would not return or update the same rows? The INSERTED scope corresponds to the record you're updating, so yes, it's transaction-safe. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349128 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
cftransaction isolation=repeatable_read
I am using cftransaction isolation=repeatable_read to single thread access to scheduled tasks that live in a DB table. This application spawns multiple servers, and each server is set to poll the scheduled task table every 3 minutes. It grabs the tasks and executes them. Now, if this was on one server, I could simply use a named exclusive lock to single thread access to the tasks so that multiple servers don't pick up and execute the same task (they should only be run once). What I have ended up doing is something like the code below. Basically, within the repeatable_read transaction I also update each row that was picked up for processing. This has the effect of causing another server who reads the data at the same time (before the enabled flag is set to false) to fail. I've tested this by adding a sleep() command after the first read and running this from two instances. One request always fails, so that is working as I want it to. I guess my question is, will this lead to performance problems? Is there a more elegant way to do this? cftransaction isolation=repeatable_read cfquery name=getEvents datasource=#application.gs.datasource# Select ID,cfc,runMethod,arguments,frequency,frequencytype from scheduledTasks where enabled=1 and nextDate cfqueryparam cfsqltype=cf_sql_timestamp value=#createodbcdatetime(Now())# and startDate cfqueryparam cfsqltype=cf_sql_timestamp value=#createodbcdatetime(Now())# and endDate cfqueryparam cfsqltype=cf_sql_timestamp value=#createodbcdatetime(Now())# /cfquery cfoutput query=getEvents cftry cfquery datasource=#application.gs.datasource# update scheduledtasks set enabled=0 where id=cfqueryparam cfsqltype=cf_sql_integer value=#id# /cfquery cfcatch cfinvoke component=#application.core.Log# method=call logFile=scheduledEventLauncher log_error=Unable to update table, another server has a lock? #cfcatch.message# #cfcatch.detail# email=true cfreturn false /cfcatch /cftry /cfoutput /cftransaction Thanks for the input! Hopefully the above code does not get mangled.. Brook ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349090 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cftransaction isolation=repeatable_read
On Tue, Dec 13, 2011 at 6:13 PM, Brook Davies cft...@logiforms.com wrote: I guess my question is, will this lead to performance problems? Is there a more elegant way to do this? Are you using MS SQL server? If so you only need one query which will still output rows like the first query with OUTPUT. cfquery name=getEvents update t1 set t1.enabled = 0 OUTPUT inserted.ID, inserted.cfc, inserted.runMethod, inserted.arguments, inserted.frequency, inserted.frequencytype from scheduledTasks t1 inner join scheduledTasks t2 on t1.ID = t2.ID where t2.enabled = 1 and t2.nextDate cfqueryparam cfsqltype=cf_sql_timestamp value=#createodbcdatetime(Now())# and t2.startDate cfqueryparam cfsqltype=cf_sql_timestamp value=#createodbcdatetime(Now())# and t2.endDate cfqueryparam cfsqltype=cf_sql_timestamp value=#createodbcdatetime(Now())# /cfquery ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349091 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cftransaction isolation=repeatable_read
So this would update and select the rows at the same time? Would I need to use transaction or would this guarantee if another request for the same query at the exact same time happened, it would not return or update the same rows? Brook -Original Message- From: Casey Dougall [mailto:ca...@uberwebsitesolutions.com] Sent: December-13-11 6:29 PM To: cf-talk Subject: Re: cftransaction isolation=repeatable_read On Tue, Dec 13, 2011 at 6:13 PM, Brook Davies cft...@logiforms.com wrote: I guess my question is, will this lead to performance problems? Is there a more elegant way to do this? Are you using MS SQL server? If so you only need one query which will still output rows like the first query with OUTPUT. cfquery name=getEvents update t1 set t1.enabled = 0 OUTPUT inserted.ID, inserted.cfc, inserted.runMethod, inserted.arguments, inserted.frequency, inserted.frequencytype from scheduledTasks t1 inner join scheduledTasks t2 on t1.ID = t2.ID where t2.enabled = 1 and t2.nextDate cfqueryparam cfsqltype=cf_sql_timestamp value=#createodbcdatetime(Now())# and t2.startDate cfqueryparam cfsqltype=cf_sql_timestamp value=#createodbcdatetime(Now())# and t2.endDate cfqueryparam cfsqltype=cf_sql_timestamp value=#createodbcdatetime(Now())# /cfquery ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349092 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFtransaction questions
that really shouldn't cause you to only be able to run 1 request at a time. what actually happens if you run the page twice at the same time ? On Tue, Nov 1, 2011 at 12:39 AM, Matt Blatchley m...@bridgeleaf.com wrote: Win2003 Standard I removed the cflock and I'm currently using cftransaction isolation=READ_COMMITTED ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348399 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFtransaction questions
I thought that would be the case as well after researching a bit further, but I haven't been able to get the two to finish at the same time. Could just be a timing thing. The code that is executed once the XML response is returned is quite lengthy, performing a number of checks and rolling up the daily data into monthly values and re-inserting those as well. I'm going to go back through and double check to make sure I don't have anything nested, or if there is anything else I can find that might be causing this. Thank you both for the direction, looks like I've got a bit more to work through. Matt ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348400 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFtransaction questions
well don;t forget that CF now has debugging again, so you could use cfeclipse to step through and see what is happening. I wouldn't expect 2 concurrent requests to run exactly the same, as presumably each would be working on different data surely ? On Tue, Nov 1, 2011 at 11:22 AM, Matt Blatchley m...@bridgeleaf.com wrote: I thought that would be the case as well after researching a bit further, but I haven't been able to get the two to finish at the same time. Could just be a timing thing. The code that is executed once the XML response is returned is quite lengthy, performing a number of checks and rolling up the daily data into monthly values and re-inserting those as well. I'm going to go back through and double check to make sure I don't have anything nested, or if there is anything else I can find that might be causing this. Thank you both for the direction, looks like I've got a bit more to work through. Matt ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348401 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFtransaction questions
Very true. Thanks Russ. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348402 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFtransaction questions
On Tue, Nov 1, 2011 at 1:13 AM, Matt Blatchley wrote: Microsoft SQL Server 2008 (SP1) Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) Database table stores the Metrics by daily values so it's constantly being hit by the imported data and the user interface. That is what snapshot isolation is designed for: http://msdn.microsoft.com/en-us/library/tcbchxcb%28v=vs.80%29.aspx Use with updlock and as short lock acquisition timeout to grab items from a queue table and the snapshot isolation will make sure the concurrent updates to the metrics table won't affect eachother. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348403 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFtransaction questions
That is what snapshot isolation is designed for: http://msdn.microsoft.com/en-us/library/tcbchxcb%28v=vs.80%29.aspx Great information, thank you Jochem. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348404 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
CFtransaction questions
Greeting all, I'm running into an issue and was wondering if someone could give me some sort of feedback if this can be accomplished. Background: The project I'm working on involves running a CFC that calls the Google AdWords API for a number of specific accounts and then dumps the data into a table. In total we make about 200 calls to the API throughout the course of the night to gather this data for reporting purposes. This part works great when the API is running quickly (right now I have it timeout after 5 minutes) To do this I created a Queue and a Queue manager. The Queue manager runs twice a day to insert or re-insert items into the queue that need to be updated (reports, API calls, alerts etc). The queue itself is executed by a single scheduled task that runs every 5 minutes and so far that runs smoothly, but it takes about 10-12 hours to finish everything in the queue. And I need to somehow cut that time in half. So my question is this: Is it possible to have multiple queues running on different machines and dumping the data into the same table? Using cftransaction, the second queue attempts to run but has to wait until the main Queue finishes before the secondary queue starts. I've read into the Isolation attribute in the cftransaction tag and the READ_COMMITTED but still not getting far. Would I have to use TEMP tables or is there some other way of accomplishing this? I'm currently using CF9 on the main machine and CF7 on the other. Thanks, ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348368 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFtransaction questions
How about using cfthread and processing multiple items with each request, that will at least half the time it takes. Regards Russ Michaels From my mobile On 31 Oct 2011 19:57, Matt Blatchley m...@bridgeleaf.com wrote: Greeting all, I'm running into an issue and was wondering if someone could give me some sort of feedback if this can be accomplished. Background: The project I'm working on involves running a CFC that calls the Google AdWords API for a number of specific accounts and then dumps the data into a table. In total we make about 200 calls to the API throughout the course of the night to gather this data for reporting purposes. This part works great when the API is running quickly (right now I have it timeout after 5 minutes) To do this I created a Queue and a Queue manager. The Queue manager runs twice a day to insert or re-insert items into the queue that need to be updated (reports, API calls, alerts etc). The queue itself is executed by a single scheduled task that runs every 5 minutes and so far that runs smoothly, but it takes about 10-12 hours to finish everything in the queue. And I need to somehow cut that time in half. So my question is this: Is it possible to have multiple queues running on different machines and dumping the data into the same table? Using cftransaction, the second queue attempts to run but has to wait until the main Queue finishes before the secondary queue starts. I've read into the Isolation attribute in the cftransaction tag and the READ_COMMITTED but still not getting far. Would I have to use TEMP tables or is there some other way of accomplishing this? I'm currently using CF9 on the main machine and CF7 on the other. Thanks, ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348384 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFtransaction questions
On Mon, Oct 31, 2011 at 8:56 PM, Matt Blatchley wrote: So my question is this: Is it possible to have multiple queues running on different machines and dumping the data into the same table? Yes. Using cftransaction, the second queue attempts to run but has to wait until the main Queue finishes before the secondary queue starts. That is probably caused by the exact SQL used in combination with the locking strategy your database is using. You have to either make sure your database doesn't use predicate locking, or that you don't run multi row statements by separating the table that holds the actual queue from the one where you lock your records in a transaction More details on database schema's, vendors and versions will result in more details in the answer :) Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348391 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFtransaction questions
I'm not sure how much flexibility I have on this version because it's a hosted solution in a shared environment: Microsoft SQL Server 2008 (SP1) Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) Database table stores the Metrics by daily values so it's constantly being hit by the imported data and the user interface. The other thing is both CF versions are Standard, not Enterprise, so I'm limited in the use of CFThread, but I did try it anyway :) Thanks Matt On Mon, Oct 31, 2011 at 5:45 PM, Jochem van Dieten joch...@gmail.com wrote: On Mon, Oct 31, 2011 at 8:56 PM, Matt Blatchley wrote: So my question is this: Is it possible to have multiple queues running on different machines and dumping the data into the same table? Yes. Using cftransaction, the second queue attempts to run but has to wait until the main Queue finishes before the secondary queue starts. That is probably caused by the exact SQL used in combination with the locking strategy your database is using. You have to either make sure your database doesn't use predicate locking, or that you don't run multi row statements by separating the table that holds the actual queue from the one where you lock your records in a transaction More details on database schema's, vendors and versions will result in more details in the answer :) Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348392 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFtransaction questions
what type of cftransaction are you using, do u have cflocking as well, if so what type. are you really using Windows NT ? i'm pretty sure the minimum requirements for SQL Server 2008 is windows 2003 server. Russ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348393 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFtransaction questions
Win2003 Standard I removed the cflock and I'm currently using cftransaction isolation=READ_COMMITTED ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348395 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Regarding cftransaction action=commit
Hello all. Question regarding cftransaction and commit action. cftransaction cftry cfloop file cfquery insert cfquery update /cfloop cfcatchgenerate error message/cfcatch /cftry cfif error message is null or override commit=1 cftransaction commit cfelse cftransaction rollback button to allow a forced commit (which should, because of the try catch, only commit those rows which are successful queries). /cftransaction The question: I'm in the cftransaction commit but it's not committing... ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:343269 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Regarding cftransaction action=commit
You may just need to close the tag cftransaction commit / you are also missing a /cfif On Fri, Mar 25, 2011 at 3:25 PM, ike Savino msav...@optiontrax.com wrote: Hello all. Question regarding cftransaction and commit action. cftransaction cftry cfloop file cfquery insert cfquery update /cfloop cfcatchgenerate error message/cfcatch /cftry cfif error message is null or override commit=1 cftransaction commit cfelse cftransaction rollback button to allow a forced commit (which should, because of the try catch, only commit those rows which are successful queries). /cftransaction The question: I'm in the cftransaction commit but it's not committing... ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:343277 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
cftransaction and multiple databases
Hello, ran into this one today. I have a CFTransaction surrounding some queries that insert data. I am using a CFC function to get shipping total: cfprocparam dbvarname=@ShippingTotal value=#CartObj.GetCartShipping()# cfsqltype=cf_sql_numeric That function needs to query a different database then the other queries in the CFTransaction to help calculate shipping. I get this error: Datasource FOO verification failed. The root cause was that: java.sql.SQLException: Datasource names for all the database tags within the cftransaction tag must be the same. How can I get around this error? The function is just doing a select... there is nothing even to roll back even if the other queries fail. I have a feeling that I will not be able to do this and I will have to take a different approach, but wanted to ask you guys anyway. Thanks, Chad ~| 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:332201 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cftransaction and multiple databases
If the databases are on the same server, you can try accessing the other database from your main data source by specifying the full path to the table: SELECT * FROM databaseName.owner.tableName On SQL Server, owner is often dbo. Or you can use the shortcut databaseName..tableName ~Brad Original Message Subject: cftransaction and multiple databases From: Chad Gray cg...@careyweb.com Date: Wed, March 24, 2010 3:05 pm To: cf-talk cf-talk@houseoffusion.com Hello, ran into this one today. I have a CFTransaction surrounding some queries that insert data. ~| 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:332207 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cftransaction and multiple databases
On Wed, Mar 24, 2010 at 9:05 PM, Chad Gray wrote: Datasource FOO verification failed. The root cause was that: java.sql.SQLException: Datasource names for all the database tags within the cftransaction tag must be the same. How can I get around this error? The function is just doing a select... there is nothing even to roll back even if the other queries fail. Run your select from a different thread using cfthread and join it back to the main thread. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| 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:332208 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cftransaction and multiple databases
I figured out a work around. I made a view of the table. So now all my tables inside of the CFTransaction are in the same database. Chad -Original Message- From: Chad Gray [mailto:cg...@careyweb.com] Sent: Wednesday, March 24, 2010 4:05 PM To: cf-talk Subject: cftransaction and multiple databases Hello, ran into this one today. I have a CFTransaction surrounding some queries that insert data. I am using a CFC function to get shipping total: cfprocparam dbvarname=@ShippingTotal value=#CartObj.GetCartShipping()# cfsqltype=cf_sql_numeric That function needs to query a different database then the other queries in the CFTransaction to help calculate shipping. I get this error: Datasource FOO verification failed. The root cause was that: java.sql.SQLException: Datasource names for all the database tags within the cftransaction tag must be the same. How can I get around this error? The function is just doing a select... there is nothing even to roll back even if the other queries fail. I have a feeling that I will not be able to do this and I will have to take a different approach, but wanted to ask you guys anyway. Thanks, Chad ~| 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:332210 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cftransaction
Thanks everyone - looks like the sproc is indeed committing on the backend. I do miss writing my own queries... ~| 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:329154 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
cftransaction
Hi all, I understand the gist of cftransaction, but I've tried using it with a conditional rollback on an action page in the below example and it doesn't seem to work. saveDataToDatabase() submits data to a stored procedure, which always returns a string: empty string if the data was saved, or a message stating what the problem was if not. cfset allReturnMessages = cftransaction cfloop cfset returnMessage = someCFC.saveDataToDatabase() cfset allReturnMessages = allReturnMessages returnMessage br /cfloop cfif len(trim(allReturnMessages)) cftransaction action=rollback / /cfif /cftransaction I'm not including cftry in this example, just to illustrate that I'm not looking for a database or other type of CF error per se - rather, I'm looking for any return message from the stored proc that isn't an empty string, such as sorry - everything is otherwise kosher, but this stored proc is coded not to save the data if field x is above value y. That's not a database error as far as CF is concerned, so cftry wouldn't catch it. But in testing this code, I've found that the rollback doesn't take place, even when the cfif statement is true. Am I not using cftransaction correctly? ~| 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:329092 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
I'm pretty sure the rollback is not occurring because your cfstoredproc call inside your saveDataToDatabase() function is running in it's own transaction. I think you'd need to include a rollback; command inside your stored procedure if you have a failure. Dave -Original Message- From: Christophe Maso [mailto:zum...@hotmail.com] Sent: Friday, December 11, 2009 10:12 AM To: cf-talk Subject: cftransaction Hi all, I understand the gist of cftransaction, but I've tried using it with a conditional rollback on an action page in the below example and it doesn't seem to work. saveDataToDatabase() submits data to a stored procedure, which always returns a string: empty string if the data was saved, or a message stating what the problem was if not. cfset allReturnMessages = cftransaction cfloop cfset returnMessage = someCFC.saveDataToDatabase() cfset allReturnMessages = allReturnMessages returnMessage br /cfloop cfif len(trim(allReturnMessages)) cftransaction action=rollback / /cfif /cftransaction I'm not including cftry in this example, just to illustrate that I'm not looking for a database or other type of CF error per se - rather, I'm looking for any return message from the stored proc that isn't an empty string, such as sorry - everything is otherwise kosher, but this stored proc is coded not to save the data if field x is above value y. That's not a database error as far as CF is concerned, so cftry wouldn't catch it. But in testing this code, I've found that the rollback doesn't take place, even when the cfif statement is true. Am I not using cftransaction correctly? ~| 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:329096 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
The way the code is written, the cfif will never be true because it will always have br in so the length will never be below 4. Hi all, I understand the gist of cftransaction, but I've tried using it with a conditional rollback on an action page in the below example and it doesn't seem to work. saveDataToDatabase() submits data to a stored procedure, which always returns a string: empty string if the data was saved, or a message stating what the problem was if not. cfset allReturnMessages = cftransaction cfloop cfset returnMessage = someCFC.saveDataToDatabase() cfset allReturnMessages = allReturnMessages returnMessage br /cfloop cfif len(trim(allReturnMessages)) cftransaction action=rollback / /cfif /cftransaction I'm not including cftry in this example, just to illustrate that I'm not looking for a database or other type of CF error per se - rather, I'm looking for any return message from the stored proc that isn't an empty string, such as sorry - everything is otherwise kosher, but this stored proc is coded not to save the data if field x is above value y. That's not a database error as far as CF is concerned, so cftry wouldn't catch it. But in testing this code, I've found that the rollback doesn't take place, even when the cfif statement is true. Am I not using cftransaction correctly? ~| 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:329097 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
Nevermind, I read the cfif backwards. I was thinking it was looking for an empty string. In that case, the cfif will always be true. This doesn't explain the rollback issue, but once the rollback is fixed, it will always rollback. Do you have any commits in your stored procedure? If so, the rollback will be after the commit and won't matter? I beleive a GO statement will also commit and the rollback will be too late. The way the code is written, the cfif will never be true because it will always have br in so the length will never be below 4. Hi all, I understand the gist of cftransaction, but I've tried using it with a conditional rollback on an action page in the below example and it doesn't seem to work. saveDataToDatabase() submits data to a stored procedure, which always returns a string: empty string if the data was saved, or a message stating what the problem was if not. cfset allReturnMessages = cftransaction cfloop cfset returnMessage = someCFC.saveDataToDatabase() cfset allReturnMessages = allReturnMessages returnMessage br /cfloop cfif len(trim(allReturnMessages)) cftransaction action=rollback / /cfif /cftransaction I'm not including cftry in this example, just to illustrate that I'm not looking for a database or other type of CF error per se - rather, I'm looking for any return message from the stored proc that isn't an empty string, such as sorry - everything is otherwise kosher, but this stored proc is coded not to save the data if field x is above value y. That's not a database error as far as CF is concerned, so cftry wouldn't catch it. But in testing this code, I've found that the rollback doesn't take place, even when the cfif statement is true. Am I not using cftransaction correctly? ~| 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:329099 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
Good catch - you're right; I would normally include a cfif to append the return string and the br to allReturnMessages only if the return string isn't empty. The stored proc was written by someone else, so I'm not 100% sure what's happening with it, which is part of the problem. I suppose what I should be asking is - is it even possible to do what I'm trying to do? Which is, loop over a variable number of records to be saved or updated, and after the loop has finished, test to see if ANY of them have gotten a positive-length string back from the proc, and if so, rol lback ALL of them? ~| 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:329102 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
The way the CFTransaction tag works is CF is putting the TRANSACTION statements in the T-SQL it sends to the server. So the rollback tag will rollback any transactions that haven't been committed. You could have 100's of queries running with Inserts/deletes/updates and everything will rollback. It's the same if you wrote a T-SQL and started it with BEGIN TRANSACTION and ended it with ROLLBACK TRANSACTION. This is great way to test stuff, because nothing gets committed, but all of the steps occur. Good catch - you're right; I would normally include a cfif to append the return string and the br to allReturnMessages only if the return string isn't empty. The stored proc was written by someone else, so I'm not 100% sure what's happening with it, which is part of the problem. I suppose what I should be asking is - is it even possible to do what I'm trying to do? Which is, loop over a variable number of records to be saved or updated, and after the loop has finished, test to see if ANY of them have gotten a positive-length string back from the proc, and if so, rol lback ALL of them? ~| 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:329109 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
I suppose what I should be asking is - is it even possible to do what I'm trying to do? Yes. Unless, as someone else mentioned, the procedure you are calling is using its own transaction. Is it? -Leigh ~| 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:329110 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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
Re: cftransaction scope
Thanks for your comments. So long as I do not mess up my transactions because of cfcs. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:315676 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
cftransaction scope
Wondering if we can wrap cfc methods (with db queries) in a outer cftransaction?? Any comments ??? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:315431 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 scope
Yes, you can, so long as they all hit the same datasource. Wondering if we can wrap cfc methods (with db queries) in a outer cftransaction?? Any comments ??? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:315432 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 scope
Yes, you can, so long as they all hit the same datasource. Wondering if we can wrap cfc methods (with db queries) in a outer cftransaction?? Any comments ??? I've heard that they've added the feature enhancement in CF9 that allows you to nest cftransactions now. So if you have already declared a transation, and then it hits another one in the middle, it will just ignore the inner transaction in favor of the one that's already executing. Instead of throwing the error it throws currently. Not quite the same thing, but it's an issue that crops up with functions. -- s. isaac dealey ^ new epoch isn't it time for a change? ph: 781.769.0723 http://onTap.riaforge.org/blog ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:315443 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
cftransaction and user/pass in DSN
I cannot find this documented anywhere although I see mention of it on blogs and forums. A developer here uses the macromedia driver for stored proc support. In her application she also uses cftransaction. I have asked that she supply the database username and password in either her cftransaction tags or in the dsn since I have read that cftransaction bypasses the username and password stored there. Although the transaction appears to commit and rollback, errors occur repeatedly in her logs: application.log:Warning,jrpp-14881,04/09/08,18:20:37,,[Macromedia][Oracle JDBC Driver]OS Authentication was requested, but is not supported by this Oracle Server. Do any of you know of other implications caused by not supplying username and pass in the DSN as far as cftransaction goes? I am thinking it could increase response time in her application if it does not. Also can anyone point me to any official documentation that states this is the case since all I can find is on blogs and message boards? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303420 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
functions in a cftransaction
I was wondering if I can wrap a couple of functions(with queries) in a cftransaction. Would this work? I mean if a function query fails, will the transaction still roll back? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301666 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: functions in a cftransaction
Yes, this will definitely work. Just make sure of the standard stuff, such as use only 1 datasource and it will only roll back database transactions (not file or variable changes, etc.). Oh, and watch out for nested transactions. -- nathan strutz http://www.dopefly.com/ On Thu, Mar 20, 2008 at 12:10 PM, Brian Dumbledore [EMAIL PROTECTED] wrote: I was wondering if I can wrap a couple of functions(with queries) in a cftransaction. Would this work? I mean if a function query fails, will the transaction still roll back? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301675 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: functions in a cftransaction
Thanks a bunch for your reply.. Yes, this will definitely work. Just make sure of the standard stuff, such as use only 1 datasource and it will only roll back database transactions (not file or variable changes, etc.). Oh, and watch out for nested transactions. -- nathan strutz http://www.dopefly.com/ On Thu, Mar 20, 2008 at 12:10 PM, Brian Dumbledore [EMAIL PROTECTED] wrote: I was wondering if I can wrap a couple of functions(with queries) in a cftransaction. Would this work? I mean if a function query fails, will the transaction still roll back? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301677 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Correct locking / cftransaction
Matthew Chambers wrote: I've just checked and the DB has been create with MyISAM. Am I in trouble? Not with regard to this specific problem. Just use cftransaction and the MySQL last_inserted_id() function. In general though, don't expect any other benefits from transactions as long as you use MySQL/MyISAM. That you can rely on it in this instance to give you back the right ID should not be seen as an indication that MyISAM is suitable for any purpose at all, let alone the reliable storage of your data. Out of interest: I've just been reading the CF livedocs and it says; serializable: places an exclusive lock on every data table in use for the duration of the transaction. Causes access to the database to be single-threaded and is therefore not recommended for normal database access. The effects of a serializable transaction are defined in terms of operations that can not happen. Any side-effects, such as the locking of tables, are implementation defined. I believe the CF manual should not make claims about things that are outside the control of CF and I will file a bug against the documentation this weekend. So would you still say that SERIALIZABLE is the way to go? It is not required for your application. And if you want to really use transactions you need at the very least a new table handler (InnoDB) and perhaps even a new database first. And if your new table handler / database uses some form of multiversioning it is quite likely that a serializable transaction does not exclusively lock tables. I've been trying to encourage the guys to upgrade to SQLsever this will be a good argument!!! I would be very careful making that argument. If you happen to run into somebody who understands the transaction locking models of MySQL/InnoDB and MS SQL Server she will turn that argument around on you. Jochem ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274175 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Correct locking / cftransaction
Hi guys, I'm trying to find some up to date (MX7) info on how to correctly use CFTRANSACTION when writing to a DB (MySQL). All I am doing is one CFQUERY for the INSERT statement and then a second CFQUERY for the last_insert_id(). I think MySQL v5 allows you to have more than one SQL statement per CFQUERY however I'm using v4. Cheers ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274058 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Correct locking / cftransaction
If msql4 supports transactions then you have no problems. That's what transactions is all about, multiple queries that rely on one or another failing, to summarise it up. On 3/29/07, Matthew Chambers [EMAIL PROTECTED] wrote: Hi guys, I'm trying to find some up to date (MX7) info on how to correctly use CFTRANSACTION when writing to a DB (MySQL). All I am doing is one CFQUERY for the INSERT statement and then a second CFQUERY for the last_insert_id(). I think MySQL v5 allows you to have more than one SQL statement per CFQUERY however I'm using v4. Cheers ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274060 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Correct locking / cftransaction
Ok, but what CFTRANSACTION type do I use? Cheers Matthew If msql4 supports transactions then you have no problems. That's what transactions is all about, multiple queries that rely on one or another failing, to summarise it up. On 3/29/07, Matthew Chambers [EMAIL PROTECTED] wrote: ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274153 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Correct locking / cftransaction
If you are simply inserting a new record, then doing a select max type operation to determine the id, you shouldn't need to specify the isolation level, as the default will work...e.g. cftransaction cfquery name=whatever datasource=mydsn INSERT INTO(.) /cfquery cfquery name=getMyWhateverID datasource=mydsn SELECT MAX(field) AS intNewID FROM someTable /cfquery /cftransaction -Ken On 3/29/07, Matthew Chambers [EMAIL PROTECTED] wrote: Ok, but what CFTRANSACTION type do I use? Cheers Matthew If msql4 supports transactions then you have no problems. That's what transactions is all about, multiple queries that rely on one or another failing, to summarise it up. On 3/29/07, Matthew Chambers [EMAIL PROTECTED] wrote: ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274160 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Correct locking / cftransaction
If this DB defaults to read-committed, that's not always going to yield consistent results. Serializable is the only way to be sure. Of course, with MySQL, this all only applies if the table type is InnoDB, since MyISAM doesn't support transactions at all. On 3/30/07, Ken Wexel [EMAIL PROTECTED] wrote: If you are simply inserting a new record, then doing a select max type operation to determine the id, you shouldn't need to specify the isolation level, as the default will work...e.g. cftransaction cfquery name=whatever datasource=mydsn INSERT INTO(.) /cfquery cfquery name=getMyWhateverID datasource=mydsn SELECT MAX(field) AS intNewID FROM someTable /cfquery /cftransaction -Ken On 3/29/07, Matthew Chambers [EMAIL PROTECTED] wrote: Ok, but what CFTRANSACTION type do I use? Cheers Matthew If msql4 supports transactions then you have no problems. That's what transactions is all about, multiple queries that rely on one or another failing, to summarise it up. On 3/29/07, Matthew Chambers [EMAIL PROTECTED] wrote: ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274162 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Correct locking / cftransaction
ahhh..true...i shouldn't be responding to posts while working. Thread from January about the same concepts... http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:44021 On 3/29/07, James Holmes [EMAIL PROTECTED] wrote: If this DB defaults to read-committed, that's not always going to yield consistent results. Serializable is the only way to be sure. Of course, with MySQL, this all only applies if the table type is InnoDB, since MyISAM doesn't support transactions at all. On 3/30/07, Ken Wexel [EMAIL PROTECTED] wrote: If you are simply inserting a new record, then doing a select max type operation to determine the id, you shouldn't need to specify the isolation level, as the default will work...e.g. cftransaction cfquery name=whatever datasource=mydsn INSERT INTO(.) /cfquery cfquery name=getMyWhateverID datasource=mydsn SELECT MAX(field) AS intNewID FROM someTable /cfquery /cftransaction -Ken On 3/29/07, Matthew Chambers [EMAIL PROTECTED] wrote: Ok, but what CFTRANSACTION type do I use? Cheers Matthew If msql4 supports transactions then you have no problems. That's what transactions is all about, multiple queries that rely on one or another failing, to summarise it up. On 3/29/07, Matthew Chambers [EMAIL PROTECTED] wrote: ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274165 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Correct locking / cftransaction
Hi James, I've just checked and the DB has been create with MyISAM. Am I in trouble? Out of interest: I've just been reading the CF livedocs and it says; serializable: places an exclusive lock on every data table in use for the duration of the transaction. Causes access to the database to be single-threaded and is therefore not recommended for normal database access. So would you still say that SERIALIZABLE is the way to go? I've been trying to encourage the guys to upgrade to SQLsever this will be a good argument!!! By the way: if we did upgrade to SQLserver would I still need to use SERIALIZABLE? Cheers Matthew If this DB defaults to read-committed, that's not always going to yield consistent results. Serializable is the only way to be sure. Of course, with MySQL, this all only applies if the table type is InnoDB, since MyISAM doesn't support transactions at all. On 3/30/07, Ken Wexel [EMAIL PROTECTED] wrote: ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274167 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 ( mysql load data infile)--CFHTTP??
Thanks for the suggestion. For one of my sites I'm going to need to do something along these lines, because the LOAD DATA function is disabled by the host in the shared hosting environment. I need to import/update the data (a CSV file that gets FTPed to the server) into a MySQL database a few times a day. It will be around 2000 lines of data with six columns Thankfully, I can control that the CSV file is in good condition and won't (knock on wood) throw any errors. I'm also looking into CFHTTP. Any other ideas about what would be the most efficient and the least disruptive method? Many thanks, Megan Cytron This might not be the most efficient way but it's easy. This could be a bit quicker by running the loop inside the values statement but then you need to to check for the last row of your .cvs file and remove the comma. I only run this on 50 records or so, never really cared to look further into it. You should also have values in in every insert statement, this doesn't look for nulls either. Quick and dirty... cffile action=read file=C:\LOCATION TO FILE\Import.csv variable=fileData cfloop index=record list=#fileData# delimiters=#Chr(10)##Chr(13)# cfquery name=qryInsertImport datasource=#dsn# INSERT INTO TheTable (THE 5 COLUMNS TO BE INSERTED) VALUES ( cfqueryparam value=#listgetat(record,1,chr(44))# cfsqltype=cf_sql_numeric, cfqueryparam value=#listgetat(record,2,chr(44))# cfsqltype=cf_sql_varchar, cfqueryparam value=#listgetat(record,3,chr(44))# cfsqltype=cf_sql_varchar, cfqueryparam value=#listgetat(record,4,chr(44))# cfsqltype=cf_sql_varchar, cfqueryparam value=#listgetat(record,5,chr(44))# cfsqltype=cf_sql_varchar, ) /cfquery /cfloop ~| Create Web Applications With ColdFusion MX7 Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:272538 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 ( mysql load data infile)--CFHTTP??
On 3/13/07, megan c wrote: Thanks for the suggestion. For one of my sites I'm going to need to do something along It is EASY with cfhttp (if you've got well formed data), but I have never load tested it. or (not nearly as easy) If you've got createObject power you can use HSSQLDB, which seems pretty robust for CSV stuff- but CFHTTP is probably fastest from 0 to Query. The one advantage to rolling your own, is you can do it all in one statement (if it's really well formed, mind), with the old INSERT INTO table (col1,col2,col3) VALUES ( (row,one,values), (row,two,values), (row,three,values) ) Or something like that. Instead of 2000 inserts, you can do one- but it'll be a monster. Just another option or two to keep in mind, or whatnot. Happy hunting! ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:272617 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 ( mysql load data infile)--CFHTTP??
It is EASY with cfhttp (if you've got well formed data), but I have never load tested it. or (not nearly as easy) If you've got createObject power you can use HSSQLDB, which seems pretty robust for CSV stuff- but CFHTTP is probably fastest from 0 to Query. The one advantage to rolling your own, is you can do it all in one statement (if it's really well formed, mind), with the old INSERT INTO table (col1,col2,col3) VALUES ( (row,one,values), (row,two,values), (row,three,values) ) Or something like that. Instead of 2000 inserts, you can do one- but it'll be a monster. Thank you so much--this worked like a charm (once I realized that there was a tab at the end of the lines mucking things up, that is). ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:272627 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 ( mysql load data infile)--CFHTTP??
On 3/13/07, megan c wrote: Thank you so much--this worked like a charm (once I realized that there was a tab at the end of the lines mucking things up, that is). Glad I could help! ~| ColdFusion MX7 and Flex 2 Build sales marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:272634 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 ( mysql load data infile)
Does load data infile query of mysql doesnt work in cftransaction? I'm curious... did you ever figure this out? I'm also trying to determine the simplest/most efficient way of loading the data from a .csv file into a mysql DB using CF. ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:272458 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 ( mysql load data infile)
On 3/12/07, megan cytron [EMAIL PROTECTED] wrote: Does load data infile query of mysql doesnt work in cftransaction? I'm curious... did you ever figure this out? I'm also trying to determine the simplest/most efficient way of loading the data from a .csv file into a mysql DB using CF. This might not be the most efficient way but it's easy. This could be a bit quicker by running the loop inside the values statement but then you need to to check for the last row of your .cvs file and remove the comma. I only run this on 50 records or so, never really cared to look further into it. You should also have values in in every insert statement, this doesn't look for nulls either. Quick and dirty... cffile action=read file=C:\LOCATION TO FILE\Import.csv variable=fileData cfloop index=record list=#fileData# delimiters=#Chr(10)##Chr(13)# cfquery name=qryInsertImport datasource=#dsn# INSERT INTO TheTable (THE 5 COLUMNS TO BE INSERTED) VALUES ( cfqueryparam value=#listgetat(record,1,chr(44))# cfsqltype=cf_sql_numeric, cfqueryparam value=#listgetat(record,2,chr(44))# cfsqltype=cf_sql_varchar, cfqueryparam value=#listgetat(record,3,chr(44))# cfsqltype=cf_sql_varchar, cfqueryparam value=#listgetat(record,4,chr(44))# cfsqltype=cf_sql_varchar, cfqueryparam value=#listgetat(record,5,chr(44))# cfsqltype=cf_sql_varchar, ) /cfquery /cfloop ~| Create Web Applications With ColdFusion MX7 Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:272467 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 Question
Tom Kitta wrote: You should only use cftransaction tag to make sequence of SQL statements into one atomic unit. You should never have anything inside your cftransaction block that is not SQL related especially if its going to take a long time (i.e. more than few ms). You don't want to lock your SQL server DBs because some operation such as FTP is taking place. Depends. I can first do the database update, then try the FTP and if the FTP fails roll back the database update. I can not do it the other way around because I can not undo an FTP put in a dropbox. Also, not all databases require locking to provide transactional integrity. Databases that use some sort of multiversioning instead of locking generally suffer far less consequences from a long running transaction. Jochem ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:271484 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 Question
I stand by my previous claim - transactions are used by DBs to set locking level. Locking is not good for performance. Locking leads to blocking and that can slow down your DB and thus your application down to a crawl. If this extra code in the transaction is happening once in a while than it is OK (within limits, like say 10ms). We are talking here about tasks that will take a long time more than say few ms, like 50ms or something like that. Calling functions from cftransaction tag that execute a query is treated as SQL, calling a function to do an FTP operation is a definite NO. There is even a classic example in some of the Adobe CF notes - don't place user interaction code in cftransaction tag - I don't even go as far as such extremes, TK - Original Message - From: Andrew Scott [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Sunday, March 04, 2007 5:54 PM Subject: RE: cftransaction Question Tom, The statement about, You should only use cftransaction tag to make sequence of SQL statements into one atomic unit. Is Correct. Your Statement about, You should never have anything inside your cftransaction block that is not SQL related especially if its going to take a long time (i.e. more than few ms). Is not Correct, in the instance of objects and dao's it is the only way to achieve a transaction for your queries, for example. cftransaction cfset copyRecord = object.Load(id=100) / cfset newObject = Object.CreateRecord() / cfset newObject.setItem(copyRecord.getItem()) / cfset fileObject.SaveImage(form.Somefilename) / cfset newObject.Save(false) / /cftransaction Now to role this back for files, you will need to wrap the entire thing in a cftry block, so that you can delete or whatever you need to do with the maintenance of the file. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: Tom Kitta [mailto:[EMAIL PROTECTED] Sent: Monday, 5 March 2007 10:20 AM To: CF-Talk Subject: Re: cftransaction Question You should only use cftransaction tag to make sequence of SQL statements into one atomic unit. You should never have anything inside your cftransaction block that is not SQL related especially if its going to take a long time (i.e. more than few ms). You don't want to lock your SQL server DBs because some operation such as FTP is taking place. cftransaction will rollback if there is an error in the SQL executed within it. You can also use the rollback option to rollback on some user error. The rollback here refers only to SQL. To answer your question, no cftransaction will not delete any files etc. for you, it is only for SQL. TK - Original Message - From: Deepak Gupta [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Sunday, March 04, 2007 1:31 PM Subject: cftransaction Question Hi Everyone I am using cftransaction around few things 1 Reading a file to my local from ftp site 2 query to insert 3 query to loop and do some operations 4 zip files Now according cftransaction if there is any error in these functionalities, it should rollback to the initial stage right? But its not working in my case. It picks the file from ftp to local, then there is an error in query, it stops there but doesn't rollback ( i mean it doesn't remove the file read on local ). It should remove the file read through FTP rite? Code Used: cftransaction cftry 1- FTP read 2-Query ( Error one) cftransaction action=commit / cfcatch type=tran cftransaction action=rollback / cfset request.lstErrorMessages = ListAppend(request.lstErrorMessages,cfcatch.message) /cfcatch cfcatch type=any cftransaction action=rollback / cfset request.lstErrorMessages = ListAppend(request.lstErrorMessages,cfcatch.message) /cfcatch /cftry /cftransaction Can anyone help[ me with this one. what is wrong with this approach? DG ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:271486 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 Question
Well anyone who puts user interaction between trnsactions should be shot. But to day that a transaction should only ever run for 10ms, and if it goes out to 50ms is not a good call either. I have developed applications that have been very complex in the calculations, and rely heavily on day being stored i numerous tables, and can go out as far as 200ms. Now this is not that much of a perfomance hit in this case, because that is as optimised as that code is ever going to get. I agree with what you saying, but disagree that you should never do it my example showed ORM processing that had more logic to build the queries and this is by your definition a no no. On 3/5/07, Tom Kitta [EMAIL PROTECTED] wrote: I stand by my previous claim - transactions are used by DBs to set locking level. Locking is not good for performance. Locking leads to blocking and that can slow down your DB and thus your application down to a crawl. If this extra code in the transaction is happening once in a while than it is OK (within limits, like say 10ms). We are talking here about tasks that will take a long time more than say few ms, like 50ms or something like that. Calling functions from cftransaction tag that execute a query is treated as SQL, calling a function to do an FTP operation is a definite NO. There is even a classic example in some of the Adobe CF notes - don't place user interaction code in cftransaction tag - I don't even go as far as such extremes, TK - Original Message - From: Andrew Scott [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Sunday, March 04, 2007 5:54 PM Subject: RE: cftransaction Question Tom, The statement about, You should only use cftransaction tag to make sequence of SQL statements into one atomic unit. Is Correct. Your Statement about, You should never have anything inside your cftransaction block that is not SQL related especially if its going to take a long time (i.e. more than few ms). Is not Correct, in the instance of objects and dao's it is the only way to achieve a transaction for your queries, for example. cftransaction cfset copyRecord = object.Load(id=100) / cfset newObject = Object.CreateRecord() / cfset newObject.setItem(copyRecord.getItem()) / cfset fileObject.SaveImage(form.Somefilename) / cfset newObject.Save(false) / /cftransaction Now to role this back for files, you will need to wrap the entire thing in a cftry block, so that you can delete or whatever you need to do with the maintenance of the file. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: Tom Kitta [mailto:[EMAIL PROTECTED] Sent: Monday, 5 March 2007 10:20 AM To: CF-Talk Subject: Re: cftransaction Question You should only use cftransaction tag to make sequence of SQL statements into one atomic unit. You should never have anything inside your cftransaction block that is not SQL related especially if its going to take a long time (i.e. more than few ms). You don't want to lock your SQL server DBs because some operation such as FTP is taking place. cftransaction will rollback if there is an error in the SQL executed within it. You can also use the rollback option to rollback on some user error. The rollback here refers only to SQL. To answer your question, no cftransaction will not delete any files etc. for you, it is only for SQL. TK - Original Message - From: Deepak Gupta [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Sunday, March 04, 2007 1:31 PM Subject: cftransaction Question Hi Everyone I am using cftransaction around few things 1 Reading a file to my local from ftp site 2 query to insert 3 query to loop and do some operations 4 zip files Now according cftransaction if there is any error in these functionalities, it should rollback to the initial stage right? But its not working in my case. It picks the file from ftp to local, then there is an error in query, it stops there but doesn't rollback ( i mean it doesn't remove the file read on local ). It should remove the file read through FTP rite? Code Used: cftransaction cftry 1- FTP read 2-Query ( Error one) cftransaction action=commit / cfcatch type=tran cftransaction action=rollback / cfset request.lstErrorMessages = ListAppend(request.lstErrorMessages,cfcatch.message) /cfcatch cfcatch type=any cftransaction action=rollback / cfset request.lstErrorMessages = ListAppend(request.lstErrorMessages,cfcatch.message) /cfcatch /cftry /cftransaction Can anyone help[ me with this one. what is wrong with this approach? DG ~| ColdFusion MX7 and Flex 2 Build sales marketing dashboard RIAâs
Re: cftransaction Question
Tom Kitta wrote: I stand by my previous claim - transactions are used by DBs to set locking level. Transactions are used to group statements into atomic units and control the visibility of changes made by concurrent transactions. In some databases locks are used to implement transactions, others use a technique called multiversioning (and I wouldn't be surprised if other options existed somewhere). Locking is not good for performance. Locking leads to blocking and that can slow down your DB and thus your application down to a crawl. Carefully designed transactions do not necessarily lead to either locking or blocking. But you have to know exactly what you are doing and how the database you are working with implements transactions internally. And that is very database specific knowledge. For a small example: take a standard MS SQL Server, open a transaction over one connection, update a tuple in a table, open another connection and select the count(*) from that table. The count query will not complete until the update has either been committed or rolled back. Now do the same sequence against MySQL/InnoDB, PostgreSQL, Oracle or any other database that implements multiversioning and you will see that the count query completes before the update has been committed or rolled back. (MS SQL Server 2005 finally has a form of multiversioning implemented, but you need to enable that first.) There is even a classic example in some of the Adobe CF notes - don't place user interaction code in cftransaction tag - I don't even go as far as such extremes, How would you even do that in CF? Jochem ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 Flex 2. Free Trial http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:271492 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 Question
Andrew Scott wrote: Well anyone who puts user interaction between trnsactions should be shot. But to day that a transaction should only ever run for 10ms, and if it goes out to 50ms is not a good call either. I have developed applications that have been very complex in the calculations, and rely heavily on day being stored i numerous tables, and can go out as far as 200ms. Now this is not that much of a perfomance hit in this case, because that is as optimised as that code is ever going to get. I have some heavy processing tasks that run transactions that take minutes and cross 20+ tables. As long as the database is not MS SQL Server 2000 there really is no noticeable performance impact. If the database is MS SQL Server 2000 the site goes down after about 40 seconds because the server starts escalating locks on some tables that are used by pretty much every page. Jochem ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 Flex 2. Free Trial http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:271496 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 Question
I think we are into this big 'depends' here conversation - yes if your FTP is needed in the middle of the processing and it runs once in a while the transaction is one way to go. However, it would create some issues if it is to be run like 10 times a second on a server. As for the DB - I guess I am too much into that SQL Server thing - the dark side is clouding my vision :) TK Depends. I can first do the database update, then try the FTP and if the FTP fails roll back the database update. I can not do it the other way around because I can not undo an FTP put in a dropbox. Also, not all databases require locking to provide transactional integrity. Databases that use some sort of multiversioning instead of locking generally suffer far less consequences from a long running transaction. Jochem ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:271542 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 Question
yes if your FTP is needed in the middle of the processing and it runs once in a while the transaction is one way to go. However, it would create some issues if it is to be run like 10 times a second on a server. However, do not count on the CFTRANSACTION to roll back any file transfer in case of failure. You can put any action you like inside your CFTRANSACTON, but ONLY actions on your database can be rolled back. ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:271585 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 Question
I guess I should mark all of my responses to DB related questions with refers mostly to SQL Server 2000. So Jochem when you see my DB post think SQL Server 2000 - I am yet to work more with SQL 2005. TK - Original Message - From: Jochem van Dieten [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Monday, March 05, 2007 1:49 AM Subject: Re: cftransaction Question Andrew Scott wrote: Well anyone who puts user interaction between trnsactions should be shot. But to day that a transaction should only ever run for 10ms, and if it goes out to 50ms is not a good call either. I have developed applications that have been very complex in the calculations, and rely heavily on day being stored i numerous tables, and can go out as far as 200ms. Now this is not that much of a perfomance hit in this case, because that is as optimised as that code is ever going to get. I have some heavy processing tasks that run transactions that take minutes and cross 20+ tables. As long as the database is not MS SQL Server 2000 there really is no noticeable performance impact. If the database is MS SQL Server 2000 the site goes down after about 40 seconds because the server starts escalating locks on some tables that are used by pretty much every page. Jochem ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:271673 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 Question
Yes I know :) I even used this as a joke with fellow developers at my company - cftransaction that rolls back *everything* TK - Original Message - From: Claude_Schnéegans [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Monday, March 05, 2007 11:33 AM Subject: Re: cftransaction Question yes if your FTP is needed in the middle of the processing and it runs once in a while the transaction is one way to go. However, it would create some issues if it is to be run like 10 times a second on a server. However, do not count on the CFTRANSACTION to roll back any file transfer in case of failure. You can put any action you like inside your CFTRANSACTON, but ONLY actions on your database can be rolled back. ~| ColdFusion MX7 and Flex 2 Build sales marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:271675 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
cftransaction Question
Hi Everyone I am using cftransaction around few things 1 Reading a file to my local from ftp site 2 query to insert 3 query to loop and do some operations 4 zip files Now according cftransaction if there is any error in these functionalities, it should rollback to the initial stage right? But its not working in my case. It picks the file from ftp to local, then there is an error in query, it stops there but doesn't rollback ( i mean it doesn't remove the file read on local ). It should remove the file read through FTP rite? Code Used: cftransaction cftry 1- FTP read 2-Query ( Error one) cftransaction action=commit / cfcatch type=tran cftransaction action=rollback / cfset request.lstErrorMessages = ListAppend(request.lstErrorMessages,cfcatch.message) /cfcatch cfcatch type=any cftransaction action=rollback / cfset request.lstErrorMessages = ListAppend(request.lstErrorMessages,cfcatch.message) /cfcatch /cftry /cftransaction Can anyone help[ me with this one. what is wrong with this approach? DG ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 Flex 2. Free Trial http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:271457 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 Question
You should only use cftransaction tag to make sequence of SQL statements into one atomic unit. You should never have anything inside your cftransaction block that is not SQL related especially if its going to take a long time (i.e. more than few ms). You don't want to lock your SQL server DBs because some operation such as FTP is taking place. cftransaction will rollback if there is an error in the SQL executed within it. You can also use the rollback option to rollback on some user error. The rollback here refers only to SQL. To answer your question, no cftransaction will not delete any files etc. for you, it is only for SQL. TK - Original Message - From: Deepak Gupta [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Sunday, March 04, 2007 1:31 PM Subject: cftransaction Question Hi Everyone I am using cftransaction around few things 1 Reading a file to my local from ftp site 2 query to insert 3 query to loop and do some operations 4 zip files Now according cftransaction if there is any error in these functionalities, it should rollback to the initial stage right? But its not working in my case. It picks the file from ftp to local, then there is an error in query, it stops there but doesn't rollback ( i mean it doesn't remove the file read on local ). It should remove the file read through FTP rite? Code Used: cftransaction cftry 1- FTP read 2-Query ( Error one) cftransaction action=commit / cfcatch type=tran cftransaction action=rollback / cfset request.lstErrorMessages = ListAppend(request.lstErrorMessages,cfcatch.message) /cfcatch cfcatch type=any cftransaction action=rollback / cfset request.lstErrorMessages = ListAppend(request.lstErrorMessages,cfcatch.message) /cfcatch /cftry /cftransaction Can anyone help[ me with this one. what is wrong with this approach? DG ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:271460 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 Question
Tom, The statement about, You should only use cftransaction tag to make sequence of SQL statements into one atomic unit. Is Correct. Your Statement about, You should never have anything inside your cftransaction block that is not SQL related especially if its going to take a long time (i.e. more than few ms). Is not Correct, in the instance of objects and dao's it is the only way to achieve a transaction for your queries, for example. cftransaction cfset copyRecord = object.Load(id=100) / cfset newObject = Object.CreateRecord() / cfset newObject.setItem(copyRecord.getItem()) / cfset fileObject.SaveImage(form.Somefilename) / cfset newObject.Save(false) / /cftransaction Now to role this back for files, you will need to wrap the entire thing in a cftry block, so that you can delete or whatever you need to do with the maintenance of the file. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: Tom Kitta [mailto:[EMAIL PROTECTED] Sent: Monday, 5 March 2007 10:20 AM To: CF-Talk Subject: Re: cftransaction Question You should only use cftransaction tag to make sequence of SQL statements into one atomic unit. You should never have anything inside your cftransaction block that is not SQL related especially if its going to take a long time (i.e. more than few ms). You don't want to lock your SQL server DBs because some operation such as FTP is taking place. cftransaction will rollback if there is an error in the SQL executed within it. You can also use the rollback option to rollback on some user error. The rollback here refers only to SQL. To answer your question, no cftransaction will not delete any files etc. for you, it is only for SQL. TK - Original Message - From: Deepak Gupta [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Sunday, March 04, 2007 1:31 PM Subject: cftransaction Question Hi Everyone I am using cftransaction around few things 1 Reading a file to my local from ftp site 2 query to insert 3 query to loop and do some operations 4 zip files Now according cftransaction if there is any error in these functionalities, it should rollback to the initial stage right? But its not working in my case. It picks the file from ftp to local, then there is an error in query, it stops there but doesn't rollback ( i mean it doesn't remove the file read on local ). It should remove the file read through FTP rite? Code Used: cftransaction cftry 1- FTP read 2-Query ( Error one) cftransaction action=commit / cfcatch type=tran cftransaction action=rollback / cfset request.lstErrorMessages = ListAppend(request.lstErrorMessages,cfcatch.message) /cfcatch cfcatch type=any cftransaction action=rollback / cfset request.lstErrorMessages = ListAppend(request.lstErrorMessages,cfcatch.message) /cfcatch /cftry /cftransaction Can anyone help[ me with this one. what is wrong with this approach? DG ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:271467 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 Question
Scott Suppose I have 2 queries in cftransaction 1- inserts into database 2 reads from database Then cftransaction action=commit is it only after the commit i will be able to see the values in the database? my 1st insert is load data infile somehow i cant see my results in database during the process and it is able to get results out of second query that means there is something in DB, but why can't i see it? when i do load data infile in a cftransaction i cant see results in DB Do you have any suggestion DG Tom, The statement about, You should only use cftransaction tag to make sequence of SQL statements into one atomic unit. Is Correct. Your Statement about, You should never have anything inside your cftransaction block that is not SQL related especially if its going to take a long time (i.e. more than few ms). Is not Correct, in the instance of objects and dao's it is the only way to achieve a transaction for your queries, for example. cftransaction cfset copyRecord = object.Load(id=100) / cfset newObject = Object.CreateRecord() / cfset newObject.setItem(copyRecord.getItem()) / cfset fileObject.SaveImage(form.Somefilename) / cfset newObject.Save(false) / /cftransaction Now to role this back for files, you will need to wrap the entire thing in a cftry block, so that you can delete or whatever you need to do with the maintenance of the file. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: Tom Kitta [mailto:[EMAIL PROTECTED] Sent: Monday, 5 March 2007 10:20 AM To: CF-Talk Subject: Re: cftransaction Question You should only use cftransaction tag to make sequence of SQL statements into one atomic unit. You should never have anything inside your cftransaction block that is not SQL related especially if its going to take a long time (i.e. more than few ms). You don't want to lock your SQL server DBs because some operation such as FTP is taking place. cftransaction will rollback if there is an error in the SQL executed within it. You can also use the rollback option to rollback on some user error. The rollback here refers only to SQL. To answer your question, no cftransaction will not delete any files etc. for you, it is only for SQL. TK - Original Message - From: Deepak Gupta [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Sunday, March 04, 2007 1:31 PM Subject: cftransaction Question ~| Create Web Applications With ColdFusion MX7 Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:271471 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
cftransaction ( mysql load data infile)
Does load data infile query of mysql doesnt work in cftransaction? ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:271472 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 Question
Scott is not here at the moment, but maybe I can answer the question for you. Yes until the final cftransaction you will not see the data in the database, but having said that. cftransaction /cftransaction In your case the sql query to read doesn't need to be in the cftransaction, you can place a cftry around the block and make it fail past the sql read if the transaction fails for any reason. HTH Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: Deepak Gupta [mailto:[EMAIL PROTECTED] Sent: Monday, 5 March 2007 12:48 PM To: CF-Talk Subject: Re: cftransaction Question Scott Suppose I have 2 queries in cftransaction 1- inserts into database 2 reads from database Then cftransaction action=commit is it only after the commit i will be able to see the values in the database? my 1st insert is load data infile somehow i cant see my results in database during the process and it is able to get results out of second query that means there is something in DB, but why can't i see it? when i do load data infile in a cftransaction i cant see results in DB Do you have any suggestion DG Tom, The statement about, You should only use cftransaction tag to make sequence of SQL statements into one atomic unit. Is Correct. Your Statement about, You should never have anything inside your cftransaction block that is not SQL related especially if its going to take a long time (i.e. more than few ms). Is not Correct, in the instance of objects and dao's it is the only way to achieve a transaction for your queries, for example. cftransaction cfset copyRecord = object.Load(id=100) / cfset newObject = Object.CreateRecord() / cfset newObject.setItem(copyRecord.getItem()) / cfset fileObject.SaveImage(form.Somefilename) / cfset newObject.Save(false) / /cftransaction Now to role this back for files, you will need to wrap the entire thing in a cftry block, so that you can delete or whatever you need to do with the maintenance of the file. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: Tom Kitta [mailto:[EMAIL PROTECTED] Sent: Monday, 5 March 2007 10:20 AM To: CF-Talk Subject: Re: cftransaction Question You should only use cftransaction tag to make sequence of SQL statements into one atomic unit. You should never have anything inside your cftransaction block that is not SQL related especially if its going to take a long time (i.e. more than few ms). You don't want to lock your SQL server DBs because some operation such as FTP is taking place. cftransaction will rollback if there is an error in the SQL executed within it. You can also use the rollback option to rollback on some user error. The rollback here refers only to SQL. To answer your question, no cftransaction will not delete any files etc. for you, it is only for SQL. TK - Original Message - From: Deepak Gupta [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Sunday, March 04, 2007 1:31 PM Subject: cftransaction Question ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:271474 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
cftransaction and roll your own transactions
We're investigating performance issues in a db app that is throwing lock contention errors. One of the things we are looking into is using the UPDLOCK optimizer hint (this is for SQL Server 2000). However, we have a situation where a number of queries are run inside a cftransaction tag. We can't move the logic outside, generate the sql, then execute it in our own transaction because the subsequent queries rely on data returned from previous queries in the transaction. I know I could rewrite this all into a stored procedure, but budgetarily, this isn't an option right now. So, my question is this: can you use your own transaction hints inside of queries that are contained inside a cftransaction? Or will the cftransaction just set the lock level and disregard the hints contained in the sql in the various cfqueries? Fwiw, I'm running 7.0.2 on Windows 2003. Thanks, Judah ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 Flex 2. Free Trial http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270985 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
cftransaction and two datasources
I have a question. I have a application where on one template I have several inserts into one datasource are wrapped inside a cftransaction. At the end of the loop, but outside the cftransaction, I have an update of a record in a second datasource, marking it as viewed. My question, is can I have the update query inside my transaction even though it is a separate DSN? ~| ColdFusion MX7 and Flex 2 Build sales marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270390 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 and two datasources
I have a question. I have a application where on one template I have several inserts into one datasource are wrapped inside a cftransaction. At the end of the loop, but outside the cftransaction, I have an update of a record in a second datasource, marking it as viewed. My question, is can I have the update query inside my transaction even though it is a separate DSN? You can, but it won't be treated as part of that transaction. That is, it'll run, but it won't depend on other queries to succeed, or cause other queries to be rolled back if it fails. If you want to have transactions across multiple datasources that point to the same database server, that can be pretty easy to set up, as long as one of the datasources uses an account with rights to the database used by the other datasource. If they point to separate database servers, you need a distributed transaction coordinator of some sort, which is usually not easy to set up. 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! ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270392 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 and two datasources
While I have not tested this just now, I am pretty sure that it will not run... It throws an error telling you that you cannot have different DSN queries in the same transaction. I am on MX 7, maybe it is new? I only knew this happens because I have been doing a lot of data transfer stuff lately. Of course, I could be thinking of something else, as I said, I didn't just test this. -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 21, 2007 4:14 PM To: CF-Talk Subject: RE: cftransaction and two datasources I have a question. I have a application where on one template I have several inserts into one datasource are wrapped inside a cftransaction. At the end of the loop, but outside the cftransaction, I have an update of a record in a second datasource, marking it as viewed. My question, is can I have the update query inside my transaction even though it is a separate DSN? You can, but it won't be treated as part of that transaction. That is, it'll run, but it won't depend on other queries to succeed, or cause other queries to be rolled back if it fails. If you want to have transactions across multiple datasources that point to the same database server, that can be pretty easy to set up, as long as one of the datasources uses an account with rights to the database used by the other datasource. If they point to separate database servers, you need a distributed transaction coordinator of some sort, which is usually not easy to set up. 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! ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 Flex 2. Free Trial http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270395 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cftry inside of cftransaction ??
Will Tomlinson wrote: Can you wrap a query with cftry inside a cftransaction? Yes. It won't do anything for you unless you put an explicit commit in the catch (in which case the behaviour is 'implementation defined' so check your DB documentation) so I would recommend against it. Jochem ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259585 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cftry inside of cftransaction ??
hi i'm still newbie to xml thingy, do u think i can use cftransaction and cftry into my code below? cfflush interval=10 !DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd; html xmlns=http://www.w3.org/1999/xhtml; head meta http-equiv=Content-Type content=text/html; charset=iso-8859-1 / titleXML to Database/title /head body !---parse XML via http--- cfhttp url=http://localhost:8500/templatemonsters-WEBAPI/01NOV-02NOV.xml; method=GET resolveurl=Yes throwOnError=Yes/ cfset Templates = XmlParse(CFHTTP.FileContent) !--- The root element of this XML Object --- !--- is the templates element in the XML code--- CFSET templatesRoot = Templates.XmlRoot !--- Let's grab the company element's name attribute CFSET templatesCompany = templatesRoot.XmlAttributes[company] --- !--- How many immediate children of company are there? --- CFSET templatesTotal = ArrayLen(templatesRoot.XmlChildren) !---cfoutputp#templatesCompany#/pp#templatesTotal#/p/cfoutput--- CFOUTPUT !--- Loop through each of the location elements --- CFLOOP FROM=1 TO=#templatesTotal# INDEX=i !--- Grab values from the XML document --- CFSET thisID = templatesRoot.XmlChildren[i] !---CFSET id = thisID.XmlAttributes[id]--- cfif thisID[state].xmlText neq 2 and (thisID[template_type][type_id].xmlText eq 1 or thisID[template_type][type_id].xmlText eq 2 or thisID[template_type][type_id].xmlText eq 3 or thisID[template_type][type_id].xmlText eq 9 or thisID[template_type][type_id].xmlText eq 10) h3#thisID[state]#/h3 !---xml to database - begin--- ptt emstrong#thisID[id].xmlText#/strong/embr / Price#thisID[price].xmlText# (Unique#thisID[exc_price].xmlText#)br / Type #thisID[template_type][type_id].xmlText#br / cfif not #structIsEmpty(thisID[screenshots_list])# cfset findDIR = getDirectoryFromPath(thisID[screenshots_list][screenshot][1][uri].xmlText) Directory #ReplaceList(findDIR,http://,images.templatemonster.com/screenshots/,/;, )#br / cfloop from=1 to=#ArrayLen(thisID.screenshots_list.screenshot)# index=the_screenshot_list cfset screenshotToFind = getFileFromPath(thisID[screenshots_list][screenshot][the_screenshot_list][uri].xmlText) cfif screenshotToFind contains #thisID[id].xmlText#-m.jpg or screenshotToFind contains #thisID[id].xmlText#-b.jpg #getFileFromPath( thisID[screenshots_list][screenshot][the_screenshot_list][uri].xmlText )#, /cfif /cfloop cfelse Null /cfif /tt/p !---xml to database - end--- /cfif /CFLOOP /CFOUTPUT /body /html Figured someone would know and answer quicker than me testing this. Can you wrap a query with cftry inside a cftransaction? Thanks, Will I do this all the time in my CFCs... cftransaction cftry cfquery !--- Query here --- /cfquery cfquery !--- Another query here --- /cfquery cfcatch type=database cftransaction action=rollback / cfreturn false / /cfcatch !--- If no database errors caught, commit transaction --- cftransaction action=commit / /cftransaction ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259726 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
cftry inside of cftransaction ??
Figured someone would know and answer quicker than me testing this. Can you wrap a query with cftry inside a cftransaction? Thanks, Will ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259408 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cftry inside of cftransaction ??
Figured someone would know and answer quicker than me testing this. Can you wrap a query with cftry inside a cftransaction? Thanks, Will I do this all the time in my CFCs... cftransaction cftry cfquery !--- Query here --- /cfquery cfquery !--- Another query here --- /cfquery cfcatch type=database cftransaction action=rollback / cfreturn false / /cfcatch !--- If no database errors caught, commit transaction --- cftransaction action=commit / /cftransaction ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259422 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cftry inside of cftransaction ??
We do it like this: cftransaction action=begin isolation=serializable cftry !--- [mjh] Query here: --- cfquery ... /cfquery cftransaction action=commit / cfcatch cftransaction action=rollback / cfdump var=#cfcatch# cfabort /cfcatch /cftry /cftransaction Will Tomlinson wrote: Figured someone would know and answer quicker than me testing this. Can you wrap a query with cftry inside a cftransaction? Thanks, Will ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259429 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cftry inside of cftransaction ??
We do it like this: cftransaction action=begin isolation=serializable cftry !--- [mjh] Query here: --- cfquery ... /cfquery cftransaction action=commit / cfcatch cftransaction action=rollback / cfdump var=#cfcatch# cfabort /cfcatch /cftry /cftransaction There's no need to use CFTRY within CFTRANSACTION like this. If any query fails within the transaction, all other queries will be rolled back. If no query fails, the transaction will be committed. 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! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259573 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
cftransaction stored procs
Hi, I was wondering if anyone had any experience executing cfstoredproc calls within cftransaction's. The livedocs for cftransaction focus on cfquery operations. Though, using a MS Sql Server 2000 database, cftransaction seems to fully support rollback of cfstoredproc calls. I know it would be ideal to combine my sql work into a single stored proc and let the SQL server handle transactions. But I'm working with pre-built DAO cfc's that I'd rather not hack into if I can handle transactions efficiently in CF in the business layer. Anyone know of any performance or compatibility issues with doing this? ___ David Konopka Systems Programmer Wharton Computing and Information Technology E-mail: [EMAIL PROTECTED] ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251391 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 stored procs
I was wondering if anyone had any experience executing cfstoredproc calls within cftransaction's. The livedocs for cftransaction focus on cfquery operations. Though, using a MS Sql Server 2000 database, cftransaction seems to fully support rollback of cfstoredproc calls. I know it would be ideal to combine my sql work into a single stored proc and let the SQL server handle transactions. But I'm working with pre-built DAO cfc's that I'd rather not hack into if I can handle transactions efficiently in CF in the business layer. Anyone know of any performance or compatibility issues with doing this? You should have no problems doing this. I haven't. 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! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251397 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
cfTRANSACTION - success or failed
Is there any way to carry a True/False value from the result of queries between cftransaction tags? Thanks. D ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241043 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: cfTRANSACTION - success or failed
Meaning a success/fail value for the transaction as a whole? The only time CFTRANSACTION will implicitly roll back is when an exception is raised. So if the CFTRANSACTION block exits normally (assuming you're not doing a manual rollback), then you know the transaction was successful. Conversely, if an exception is raised, that means the transaction was rolled back. cheers, barneyb On 5/20/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Is there any way to carry a True/False value from the result of queries between cftransaction tags? Thanks. D -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 100 invites. ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241044 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: cftransaction
Tony wrote: does that retrieve the newest UUID Primary Key generated? right now, i have a dateAdded field that i sort by, to get the newest one. That's not real reliable. What happens if two records are added in the same second? :) Yes, it returns the last identity inserted, and inside a transaction, I think it's reliable. I recalll the old days of having a separate field that I would throw a UUID into then query it out to get the autonumber. *shudder* I haven't done that in yearS! Rick ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236862 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: cftransaction
Yep, indeed. They should also not be using @@identity :-) -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: 04 April 2006 03:51 To: CF-Talk Subject: cftransaction I'm looking through someone elses code and I came across this... cftransaction cfquery name=qInsertData datasource=#APPLICATION.dsn# INSERT INTO t_doctors (#ColNames#) VALUES (#preserveSingleQuotes(ColValues)#) /cfquery /cftransaction !--- pull the pkey back out --- cftransaction cfquery name=get_new_pkey datasource=#APPLICATION.dsn# SELECT @@identity AS newpkey /cfquery /cftransaction having the queries in separate transactions doesn't do anything at all, does it? Wouldn't the whole thing need to be wrapped in cftransaction for it to be effective/ Rick ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236864 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: cftransaction
Keep in mind that most other databases (besides SQL Server) don't allow you to send two queries with at once like this. It's very insecure, because of sql injection possibilities. But since you guys are all talking about MS SQL, this works fine. :) -Original Message- From: Pete Ruckelshaus [mailto:[EMAIL PROTECTED] Sent: Monday, April 03, 2006 9:03 PM You are correct. Moreover, a better solution would be something like this (assuming SQL Server): cftransaction cfquery name=qInsertData datasource=#APPLICATION.dsn# INSERT INTO t_doctors (#ColNames#) VALUES (#preserveSingleQuotes(ColValues)#); SELECT SCOPE_IDENTITY() AS newpkey; /cfquery /cftransaction Pete On 4/3/06, Rick Root [EMAIL PROTECTED] wrote: I'm looking through someone elses code and I came across this... cftransaction cfquery name=qInsertData datasource=#APPLICATION.dsn# INSERT INTO t_doctors (#ColNames#) VALUES (#preserveSingleQuotes(ColValues)#) /cfquery /cftransaction !--- pull the pkey back out --- cftransaction cfquery name=get_new_pkey datasource=#APPLICATION.dsn# SELECT @@identity AS newpkey /cfquery /cftransaction having the queries in separate transactions doesn't do anything at all, does it? Wouldn't the whole thing need to be wrapped in cftransaction for it to be effective/ This transmission may contain information that is privileged, confidential and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you. A1. ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236876 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: cftransaction
In MS SQL I've seen two ways to handle returning the last inserted primary key: INSERT INTO t_doctors (#ColNames#) VALUES (#preserveSingleQuotes(ColValues)#); SELECT SCOPE_IDENTITY() AS newpkey; This will return the last inserted primary key for the current scope (session), so there is no concern of race conditions. INSERT INTO t_doctors (#ColNames#) VALUES (#preserveSingleQuotes(ColValues)#); SELECT @@identity AS newpkey; While this will return the last inserted primary key across scopes (sessions). Using @@identity will leave you open to potential problems with race conditions. This is from information I've read in the BOL as well as been informed from our DBA. I can say that when using SCOPE_IDENTITY() I've never found any errors under load, but if anyone else has any input I'd love to hear it. Rich Kroll Application Developer ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236877 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
