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
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
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
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
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
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
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
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
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
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
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: 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
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
RE: cftransaction
If possible you should use SCOPE_IDENTITY() for the reasons stated - as noted direct from BOL. -Original Message- From: Rich Kroll [mailto:[EMAIL PROTECTED] Sent: 04 April 2006 15:31 To: CF-Talk Subject: 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:236878 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. Minor clarification: when using scope_identity() with a single cfquery tag, the cftransaction tags aren't necessary to make this reliable. Scope_identity() returns specifically the identity last inserted in the current batch statement. Which means that it can't be used (or at least not reliably) if you use two separate cfquery tag pairs, however, if there's only one cfquery tag pair with both the insert and select statements in it, then that obviates the need for the cftransaction (and I believe it will also improve performance in comparison to using the cftransaction around either one or two queries). I don't think I've actually tried using scope_identity() with two cfqueries, so I don't know if it would produce an error or just produce bad or unreliable data. I suspect it would produce an error. s. isaac dealey 434.293.6201 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236879 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
The 2nd query won't allow anyone to perform a sql injection attack against the database, although it's possible that using #ColNames# or #preserveSingleQuotes(ColValues)# in this example might. 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 s. isaac dealey 434.293.6201 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236882 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
Hey Rich, how are things at Site? I think sessions is the wrong label for this (although I could be wrong) :) -- I believe scope_identity() works on the batch, whereas an open session for example in Query Analyzer can execute multiple batches. I think CF Server in particular only creates one session at a time for each DSN and then executes all queries within that same session and each cfquery tag pair is a new batch. 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 s. isaac dealey 434.293.6201 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236881 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
How would you to this in (say) MySQL? Munson, Jacob wrote: 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. :) 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 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236885 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
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? No, not a thing. The point of variables like @@IDENTITY (and functions like SCOPE_IDENTITY()) are to allow you to retrieve the newly created primary key without running multiple queries and having to worry about transactional logic, anyway. Wouldn't the whole thing need to be wrapped in cftransaction for it to be effective? In this particular case, you wouldn't really need the CFTRANSACTION tags at all. Ideally, you'd just put both SQL statements together in a single batch, or use a stored procedure or a trigger to get your primary key. But if you couldn't do that, and therefore did need a CFTRANSACTION, it would have to wrap both queries to make them into a single transaction. 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! ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236897 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
I'm not sure about MySQL, but in Postgres you can select a new identity key /before/ you do an insert. The key is unique to you, and can never be used by anybody else (just like getting a new key when you do an insert). Then you can use that key for your insert and any subsequent queries. Much safer than Microsft's approach, where you have to scramble to safely get the key from a recent insert. -Original Message- From: Joelle Tegwen [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 04, 2006 9:11 AM How would you to this in (say) MySQL? Munson, Jacob wrote: 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. :) 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 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:236907 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
Right. The problem is not that the developer is going to do a SQL injection, it's that Hackers on a public facing site will. DB2, Oracle and others don't allow two queries in one statement to stop things like what Ben Forta demonstrates in this blog post: http://tinyurl.com/ozq8x That said, SQL Inject attacks /can/ be prevented by doing proper data cleaning for all queries that use values generated by outsiders (URL params, forms, etc.) I'm just saying that Oracle, DB2 and the others prefer to prevent things at the database level, rather than putting the security burden on the developer. -Original Message- From: S.Isaac Dealey [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 04, 2006 8:56 AM The 2nd query won't allow anyone to perform a sql injection attack against the database, although it's possible that using #ColNames# or #preserveSingleQuotes(ColValues)# in this example might. 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 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:236909 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
That said, SQL Inject attacks /can/ be prevented by doing proper data cleaning for all queries that use values generated by outsiders (URL params, forms, etc.) I'm just saying that Oracle, DB2 and the others prefer to prevent things at the database level, rather than putting the security burden on the developer. Whether you can run multiple queries within a single SQL batch is generally determined by the JDBC drivers (or other database clients) being used, not by the database. It's my understanding that Oracle and DB2 both can accept SQL batches. Generally, Oracle is just as vulnerable to SQL injection attacks as is SQL Server (although for various reasons, the outcomes may often be less bad with Oracle). 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! ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236912 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
Whether you can run multiple queries within a single SQL batch is generally determined by the JDBC drivers (or other database clients) being used, not by the database. It's my understanding that Oracle and DB2 both can accept SQL batches. Generally, Oracle is just as vulnerable to SQL injection attacks as is SQL Server (although for various reasons, the outcomes may often be less bad with Oracle). I was told by someone that develops on Oracle that multiple queries are not supported, but I suppose he could have been wrong. As far as DB2, we tried to do multiple queries in one statement once on our local DB2 instance and it didn't work, but I suppose it could have been a driver issue like you said. --- [INFO] -- Access Manager: 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. A2 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236914 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
I was told by someone that develops on Oracle that multiple queries are not supported, but I suppose he could have been wrong. You can run an SQL batch through SQL*Plus and see for yourself. That's just another Oracle client. And I'm pretty sure that you can run SQL batches against DB2 with some client, too. Otherwise, it would be difficult to script database creation, etc. 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! ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236921 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
I believe he was wrong, unless I am just misunderstanding multiple queries because I do multiple ones all the time just not with a CFQUERY and due to the JDBC drivers from what I can tell. On 4/4/06, Munson, Jacob [EMAIL PROTECTED] wrote: I was told by someone that develops on Oracle that multiple queries are not supported, but I suppose he could have been wrong. As far as DB2, we tried to do multiple queries in one statement once on our local DB2 instance and it didn't work, but I suppose it could have been a driver issue like you said. ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236923 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
Dave Watts wrote: That said, SQL Inject attacks /can/ be prevented by doing proper data cleaning for all queries that use values generated by outsiders (URL params, forms, etc.) I'm just saying that Oracle, DB2 and the others prefer to prevent things at the database level, rather than putting the security burden on the developer. Whether you can run multiple queries within a single SQL batch is generally determined by the JDBC drivers (or other database clients) being used, not by the database. The JDBC standard prohibits multiple queries in one prepared statement and most drivers stick to that. If you don't use prepared statements, most drivers allow almost everything. Jochem ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236924 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
Well, I think we're talking apples and oranges. This is what we tried in DB2: in SQL Server we tried to use OpenQuery to send more than one statement at once to DB2, and it just silently ignored the second statement. However, we /could/ do two separate OpenQuery statements in the same SQL script. This works: Select * from openquery (db2prodss, ' Select * from ZPFCSAD.TVCHFACC where voucher in (''0012'',''0014'') ') Select * from openquery (db2prodss, ' Delete ZPFCSAD.TVCHFACC ') This doesn't: Select * from openquery (db2prodss, ' Select * from ZPFCSAD.TVCHFACC where voucher in (''0012'',''0014''); Delete ZPFCSAD.TVCHFACC; ') I may have some syntax errors there, I just threw it in as a quick example. -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 04, 2006 11:29 AM I was told by someone that develops on Oracle that multiple queries are not supported, but I suppose he could have been wrong. You can run an SQL batch through SQL*Plus and see for yourself. That's just another Oracle client. And I'm pretty sure that you can run SQL batches against DB2 with some client, too. Otherwise, it would be difficult to script database creation, etc. 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:236925 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
If you're going to put two SQL statements in the same CFQUERY tag, you also have to suppress the returned messages from the database, or the second SQL Statement will throw an error. SET NOCOUNT ON will supress the returned messages. so your CFQUERY looks something like this: cfquery name=myquery datasource=#request.dsn# SET NOCOUNT ON INSERT INTO tablename (field1,field2) VALUES ( cfqueryparam value=#trim(form.field1)# cfsqltype=cf_sql_varchar/, cfqueryparam value=#trim(form.field2)# cfsqltype=cf_sql_varchar/) SELECT SCOPE_IDENTITY() AS newpkey SET NOCOUNT OFF /cfquery Another valid way to get the identity in SQLServer is SELECT IDENT_CURRENT('tablename ') which returns the latest identity in the table name in question. That also prevents getting the identity of the wrong table in the event of the query being used as part of a trigger which affects multiple tables. Cheers Mike Kear Windsor, NSW, Australia Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month On 4/5/06, S. Isaac Dealey [EMAIL PROTECTED] wrote: 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. Minor clarification: when using scope_identity() with a single cfquery tag, the cftransaction tags aren't necessary to make this reliable. Scope_identity() returns specifically the identity last inserted in the current batch statement. Which means that it can't be used (or at least not reliably) if you use two separate cfquery tag pairs, however, if there's only one cfquery tag pair with both the insert and select statements in it, then that obviates the need for the cftransaction (and I believe it will also improve performance in comparison to using the cftransaction around either one or two queries). I don't think I've actually tried using scope_identity() with two cfqueries, so I don't know if it would produce an error or just produce bad or unreliable data. I suspect it would produce an error. s. isaac dealey 434.293.6201 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236952 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
SELECT IDENT_CURRENT('tablename ') which returns the latest identity in the table name in question. That also prevents getting the identity of the wrong table in the event of the query being used as part of a trigger which affects multiple tables. Unless someone has created a new record after your insert, then you'll get their identity instead. SCOPE_IDENTITY, on the other hand, returns the last identity value generated for any table in the current session and the current scope. So with one, you might get someone else's identity, and with the other you might get the wrong table. Can we ever win? :) I guess you could do both methods and compare the results, just to be sure. -- [INFO] -- Access Manager: 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. A2 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236953 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
Actually what I do more frequently nowdays is use createUUID() to generate a primary key of my own and insert that instead of using identity fields. All of these issues go away then. The only reason I use identity fields nowdays is if I think it's likely I might have to be typing queries out in the query editor using the primary key on a regular basis. Cheers Mike Kear Windsor, NSW, Australia Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month On 4/5/06, Munson, Jacob [EMAIL PROTECTED] wrote: SELECT IDENT_CURRENT('tablename ') which returns the latest identity in the table name in question. That also prevents getting the identity of the wrong table in the event of the query being used as part of a trigger which affects multiple tables. Unless someone has created a new record after your insert, then you'll get their identity instead. SCOPE_IDENTITY, on the other hand, returns the last identity value generated for any table in the current session and the current scope. So with one, you might get someone else's identity, and with the other you might get the wrong table. Can we ever win? :) I guess you could do both methods and compare the results, just to be sure. -- ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236956 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
Actually what I do more frequently nowdays is use createUUID() to generate a primary key of my own and insert that instead of using identity fields. All of these issues go away then. That's a good idea, except that UUIDs take more space and are slower to index when compared to integers. However, that's only a problem if you are dealing with large amounts of data. [INFO] -- Access Manager: 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. A2 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236957 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
Doing well, lots of fun new things on the horizon! How've you been? Btw, it's good to see you on here. Rich Kroll Application Developer -Original Message- From: S. Isaac Dealey [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 04, 2006 10:56 AM To: CF-Talk Subject: RE: cftransaction Hey Rich, how are things at Site? ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236959 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
Actually what I do more frequently nowdays is use createUUID() to generate a primary key of my own and insert that instead of using identity fields. All of these issues go away then. That's a good idea, except that UUIDs take more space and are slower to index when compared to integers. However, that's only a problem if you are dealing with large amounts of data. It only starts to become an issue with tens of millions of records, at which point you have bigger issues than indexing a 32 character string instead of an integer. It's also a vanishing issue as hardware and database evolution diminish it over time. Some of the other issues related to large data sets, most notably usability issues are not diminished over time by hardware or human evolution. s. isaac dealey 434.293.6201 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236963 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
Actually what I do more frequently nowdays is use createUUID() to generate a primary key of my own and insert that instead of using identity fields. All of these issues go away then. The only reason I use identity fields nowdays is if I think it's likely I might have to be typing queries out in the query editor using the primary key on a regular basis. I sometimes use epoch dates for these (it's a roughly 10 digit number), although it's important to ensure that the epoch-date is created from UTC / GMT dates to eliminate the possibility of DST modifications raising the likelyhood of duplication (though you need to exclusive-lock the creation of the epoch-date and use java to pause the thread for 1 second anyway). But then this for me isn't because I plan to use the value in Query Analyzer or SQL+ but rather I choose this approach when I expect to use the value in a URL which will be sent to people via email. So ultimately my real reason for doing this is because we (programmers) are waiting for the standard for email to join the rest of us in the current century and eliminate the 70-whatever character per line limitation. Just as an 8-character limitation for file names was once eliminated because it failed to meet human needs, the standard for email now fails to meet human needs, as evidenced by all our cavorting about to accomodate it. The same is true of search engine safe URLs -- we cavorted about an awful lot because search engines were slow to meet our needs, although this is no longer an issue because the search engine providers have been faster to accomodate us than those responsible for the email standard. s. isaac dealey 434.293.6201 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236964 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
S.Isaac Dealey wrote: That's a good idea, except that UUIDs take more space and are slower to index when compared to integers. However, that's only a problem if you are dealing with large amounts of data. It only starts to become an issue with tens of millions of records, at which point you have bigger issues than indexing a 32 character string instead of an integer. Offially a UUID is a 128-bit unsigned integer (RFC 4122). If your database is smart it will only take 16 bytes of storage. Jochem ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236967 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
SELECT IDENT_CURRENT('tablename ') which returns the latest identity in the table name in question. That also prevents getting the identity of the wrong table in the event of the query being used as part of a trigger which affects multiple tables. Jacob Munson wrote: Unless someone has created a new record after your insert, then you'll get their identity instead. SCOPE_IDENTITY, on the other hand, returns the last identity value generated for any table in the current session and the current scope. So with one, you might get someone else's identity, and with the other you might get the wrong table. Can we ever win? :) Nope that's not true. scope_identity() works on the last table specified in the current batch. You can test this by creating a table with an identity column and a trigger which inserts data into a 2nd table also with an identity column. Make sure there is a discrepancy between the two tables by padding the 2nd table with some data, so that the trigger will insert a different value. When you then insert a value into the table on which the trigger was created and return the value of scope_identity() within the same statement batch, you'll get the value of the table you inserted into (while the triggered insert goes merilly on past you). However, thank you for the reminder that the ident_current() function is not thread-safe, and as such would require a serializable cftransaction (or equivalent) to make it thread-safe, which is not true of scope_identity(). But with the serializable transaction (CF or DB initiated) it could work reliably as I understand it, it just wouldn't be the most efficient method. s. isaac dealey 434.293.6201 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236971 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
Jacob Munson wrote: Unless someone has created a new record after your insert, then you'll get their identity instead. SCOPE_IDENTITY, on the other hand, returns the last identity value generated for any table in the current session and the current scope. So with one, you might get someone else's identity, and with the other you might get the wrong table. Nope that's not true. scope_identity() works on the last table specified in the current batch. I actually copied this line from BOL: SCOPE_IDENTITY, returns the last identity value generated for any table in the current session and the current scope. So it doesn't matter what table you inserted into, it will get the most recent identity field you created for the scope and session. However, I think that's saying the same thing you are. The danger is if you forget and do more than one insert, and then grab scope_identity, which is a dumb programmer mistake not a database problem. [INFO] -- Access Manager: 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. A2 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236974 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
Jacob Munson wrote: Unless someone has created a new record after your insert, then you'll get their identity instead. SCOPE_IDENTITY, on the other hand, returns the last identity value generated for any table in the current session and the current scope. So with one, you might get someone else's identity, and with the other you might get the wrong table. Nope that's not true. scope_identity() works on the last table specified in the current batch. I actually copied this line from BOL: SCOPE_IDENTITY, returns the last identity value generated for any table in the current session and the current scope. So it doesn't matter what table you inserted into, it will get the most recent identity field you created for the scope and session. However, I think that's saying the same thing you are. The danger is if you forget and do more than one insert, and then grab scope_identity, which is a dumb programmer mistake not a database problem. I thought I remembered it using the word batch in the docs, but I must be mistaken... in any event, the scope is the batch. :P As evidenced by the test case... (Actually it's not technically the batch because if it's used within a stored procedure, then the scope is the procedure, but I digress)... but yeah, if you perform two inserts you have to be careful about where you place the scope_identity() in your batch, which does make it a coding issue. s. isaac dealey 434.293.6201 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236985 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
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/ Rick ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236856 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
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. tony On 4/3/06, Pete Ruckelshaus [EMAIL PROTECTED] wrote: 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/ Rick ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236858 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... it wasnt safe?
I'm going through old emails I hadn't read, sorry it's taken so long to respond. There are rules for passwords, and no spaces be one of mine. I figure if Microsoft can tell me that Backup 2/15/2005.zip is an illegal file name, then I can tell someone that my dog has$$fleas is an illegal password. I wouldn't hold that up as a great justification, myself. I would recommend that you allow spaces in passwords (although you might require that the first and last characters be something other than spaces) if for no other reason than to encourage the use of pass phrases. 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! ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213895 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... it wasnt safe?
I'm going through old emails I hadn't read, sorry it's taken so long to respond. I always, always, always set the first column in my table to primary key, identity, and often (but NOT always) a clustered index. I think you're usually better off reserving the clustered index for something other than an identity column for a couple of reasons. First, assigning the clustered index to an identity column means you might have hotspots if you have lots of near-concurrent INSERTs. Second, you get much more bang for your index buck if you have any other fields within the table that you commonly search. 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! ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213898 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 behavior
CFTRANSACTION action=BEGIN CFTRY many CFQUERY tags CFTRANSACTION action=COMMIT / CFCATCH CFTRANSACTION action=ROLLBACK / /CFCATCH /CFTRY /CFTRANSACTION It's worth pointing out that this isn't really necessary, if all you're doing is running a bunch of CFQUERY tags using the same datasource, username and password. If any of those queries throws an error, the transaction will automatically rollback all the other queries. 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! ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:199779 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 behavior
If I comment out the CFTRANSACTION action=COMMIT tag and the query completes without errors, the data is still written to the database. Shouldn't I have to explicitly use COMMIT in order for the data to be saved? At first glance it seems that the only time I'd want to ROLLBACK is in the event of an error, but if this is the expected behavior I'm not sure why I'd ever need to use CFTRANSACTION action=COMMIT. Anyone have any ideas? The ACTION=COMMIT and ACTION=ROLLBACK are there primarily to allow you to do partial commits and rollbacks within a single transaction. In your case, all you need to do is wrap your queries within a single CFTRANSACTION tag, and ensure you're using the appropriate isolation level. 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! ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:199780 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 behavior
Using your pseudo code, I do the following... Never had a problem with it and I'm not dependent on varables being set etc... CFTRANSACTION action=BEGIN CFTRY many CFQUERY tags CFTRANSACTION action=COMMIT / CFCATCH CFTRANSACTION action=ROLLBACK / /CFCATCH /CFTRY /CFTRANSACTION Paul ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:199671 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 behavior
It's expected behavior. The /cftransaction from CFTRANSACTION action=BEGIN will automatically commit if no error occurred or rollback if an error occurred. Pascal -Original Message- From: Cliff Meyers [mailto:[EMAIL PROTECTED] Sent: 22 March 2005 17:23 To: CF-Talk Subject: cftransaction behavior I've noticed some behavior from CFTRANSACTION that I wouldn't quite expect. Here is some pseudo code: CFSET transactionSuccess=false CFTRANSACTION action=BEGIN CFTRY many CFQUERY tags CFSET transactionSuccess=true CFCATCH CFTRANSACTION action=ROLLBACK /CFCATCH /CFTRY CFIF transactionSuccess CFTRANSACTION action=COMMIT /CFIF /CFTRANSACTION If I comment out the CFTRANSACTION action=COMMIT tag and the query completes without errors, the data is still written to the database. Shouldn't I have to explicitly use COMMIT in order for the data to be saved? At first glance it seems that the only time I'd want to ROLLBACK is in the event of an error, but if this is the expected behavior I'm not sure why I'd ever need to use CFTRANSACTION action=COMMIT. Anyone have any ideas? I'm using CFMX 6.1 on Solaris 9 with Oracle 9i Release 2. -Cliff ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:199672 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