Nesting cftransaction

Forum|Forum|19 years ago
March 3, 2007
5 replies
773 views

i have a coldfusion component that does order processing. i also have a separate dao crud component for each of my db tables. when processing an order i need to make a number of changes to the database tables. the create method within these cruds has a sql insert statement and then a select max statement wrapped within a cftransaction so i can return the newly generated custid/orderid/etc.

Now i need the sql statements of the create methods to be wrapped in a transaction and i also need the actions in my order processing component to be wrapped in a transaction in case of any problems. but since coldfusion does not allow nested transactions, what would the best way to accomplish this?

Is it possible to have a select statement within an insert statement in t-sql? That could be the solution, but i haven't looked into it.

Thanks!

This topic has been closed for replies.

Correct answer Newsgroup_User

> Is it possible to have a select statement within an insert statement in t-sql?
> That could be the solution, but i haven't looked into it.

I recommend looking into it. You're on the right track with this notion.

Either that or turn both processes into procedures, which would probably be
the better way to go. Let the DB handle the DB-oriented logic.

--
Adam

A

Anonymous

Thanks for the input Adam, thought i just wasn't getting it. I think it'll be best to go the stored procedures route as you suggest. Lots of benefits that cant go ignored.

N

Newsgroup_User

Inspiring

> You should have the transaction tag at just one place, in the cfm page that invokes the CFCs.

That's not much good for situations in which the create() methods needs to
be called in isolation. It's the job of the DAO to effect the DB
transactions, not the calling code.

One COULD add a "transaction layer" to all this - which is kind of what
you're suggesting - in which the methods actually called by CFM pages wrap
calls to the ACTUAL DB-accessing methods in a transaction (where
appropriate), and the DB-acccessing methods are not directly callable other
than that (package-level access, I guess?).

However that is just catering to the shortcomings of CF, and if one was
going to have that sort of tiering, I'd shift the tranactionality and the
SQL out of CF completely, and into the DB.
--
Adam

N

Newsgroup_UserCorrect answer

Inspiring

> Is it possible to have a select statement within an insert statement in t-sql?
> That could be the solution, but i haven't looked into it.

I recommend looking into it. You're on the right track with this notion.

Either that or turn both processes into procedures, which would probably be
the better way to go. Let the DB handle the DB-oriented logic.

--
Adam

BKBK

Community Expert

You should have the transaction tag at just one place, in the cfm page that invokes the CFCs.

BKBK

Community Expert

Y ou have a page transaction.cfm that does something like

A

Anonymous

I am not sure how that gets around the problem. The transactions are still nested. The create method of the DAO has a transaction around it. Here is my code:

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded