Re: cftransaction

[Dave Watts]

 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

[Brook Davies]

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

[Dave Watts]

 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

[Casey Dougall]

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

[Brook Davies]

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

[Russ Michaels]

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

[Matt Blatchley]

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

[Russ Michaels]

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

[Matt Blatchley]

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

[Jochem van Dieten]

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

[Matt Blatchley]

 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

[Russ Michaels]

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

[Jochem van Dieten]

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

[Matt Blatchley]

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

[Russ Michaels]

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

[Matt Blatchley]

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

[brad]

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

[Jochem van Dieten]

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

[Chad Gray]

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

[Christophe Maso]

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

[Dave Phillips]

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

[Donnie Carvajal]

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

[Donnie Carvajal]

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

[Christophe Maso]

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

[Donnie Carvajal]

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

[Leigh]

 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

[Barney Boisvert]

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

[Arsalan Tariq Keen]

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

[Brian McCairn]

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

[Arsalan Tariq Keen]

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

[Dave Watts]

 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

[Jason Fisher]

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

[Arsalan Tariq Keen]

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

[Jason Fisher]

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

[Dave Watts]

 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

[Arsalan Tariq Keen]

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

[Arsalan Tariq Keen]

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

[Brian Dumbledore]

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

[Jason Fisher]

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

[s. isaac dealey]

 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??

[megan cytron]

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??

[Dinner]

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??

[megan cytron]

 
 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??

[Dinner]

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)

[megan cytron]

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)

[Casey Dougall]

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

[Jochem van Dieten]

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

[Tom Kitta]

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

[Andrew Scott]

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

[Jochem van Dieten]

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

[Jochem van Dieten]

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

[Tom Kitta]

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

[Claude_Schn�egans]

 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

[Tom Kitta]

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

[Tom Kitta]

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

[Tom Kitta]

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

[Andrew Scott]

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

[Deepak Gupta]

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

[Andrew Scott]

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

[Dave Watts]

 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

[Ben Nadel]

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

[Dave Watts]

 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

[Barney Boisvert]

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

[Rick Root]

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

[Robertson-Ravo, Neil (RX)]

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

[Munson, Jacob]

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

[Rich Kroll]

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

[Robertson-Ravo, Neil (RX)]

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

[S . Isaac Dealey]

 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

[S . Isaac Dealey]

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

[S . Isaac Dealey]

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

[Joelle Tegwen]

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

[Dave Watts]

 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

[Munson, Jacob]

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

[Munson, Jacob]

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

[Dave Watts]

 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

[Munson, Jacob]

 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

[Dave Watts]

 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

[Aaron Rouse]

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

[Jochem van Dieten]

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

[Munson, Jacob]

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

[Mike Kear]

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

[Munson, Jacob]

 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

[Mike Kear]

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

[Munson, Jacob]

 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

[Rich Kroll]

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

[S . Isaac Dealey]

 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

[S . Isaac Dealey]

 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

[Jochem van Dieten]

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

[S . Isaac Dealey]

 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

[Munson, Jacob]

 
 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

[S . Isaac Dealey]

 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

[Pete Ruckelshaus]

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

[Tony]

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?

[Dave Watts]

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?

[Dave Watts]

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

[Dave Watts]

 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

[Dave Watts]

 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

[Paul Vernon]

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

[Pascal Peters]

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