Using cftransaction in a cfc

[Ofeargall]

It looks like from my searching the forums that the cftransaction tag is being used mainly for multiple INSERT statements. I need to employ the tag to get the last_insert_id command to work properly but I don't know the proper procedure.

I want to keep my query in the CFC but all the tutorial stuff I've seen is on the page.

Where would I put the transaction tags in my CFC code to get the invoke on my page to work properly?

[Dan_Bracuk]

The short answer is that you put the cftransaction tags around the two queries.

[apocalipsis19]

Ofeargall,

Dan is right. I find myself very often using the cftransaction tags at work. The most commonly scenario where I use it is when I have an ID that must be incremented when i hit the "New" button.

I bet you are using two queries, one to pull the "last_insert_id" and the other query that completes your data transaction (whatever that transaction may be). Wrap both queries around the <cftransaction> tags:

<cftransaction>

<cfquery name="myQuery1" dbtype="query">
---YOUR SQL STATEMENET---
</cfquery>

<cfquery name="myQuery2" dbtype="query">
---YOUR SQL STATEMENET---
</cfquery>

</cftransaction>

That should do it. I am just giving you some hints so you can work this around. Good luck!

[Ofeargall]

Thank you both for your input. I appreciate you taking the time.

If I understand you correctly my Function should look like the following code block. But if it does then which method do I call on my invoke on the page? Does that make sense or am I just muddying the waters with an abundance of ignorance?

By the way, the code is for inserting a new article by the writer into table 1 then inserting any supporting PDF documents into table 2. So, I need the ID (primary key) from the article table to associate with the document for later pages.

<cffunction name="x" access="public" returntype="query">
<cftransaction>
<cfquery datasource blah blah blah>
SQL INSERT STATEMENT HERE
</cfquery>

<cfquery name="qNewID" datasource="x">
SELECT_LAST_INSERT() AS NewID
</cfquery>
<cfreturn qNewID>
</cftransaction>
</cffunction>

I'm posting this code in the off chance I actually figured it out.

Thank you forum users for you help.

[Dan_Bracuk]

Regarding what method do you invoke, how many choices do you have?

[apocalipsis19]

Ok,

I mentioned before that there are two queries inside the cftransaction tags. The cftransaction tags does nothing more but grouping queries into one unit. In my program I have a CFC component that contains all of the functions that we use accross the system. I will talk to you about an specific example. I have an insert function.

This insert function has two queries, one of them is where I get the last ID I inserted and the other one is my regular insert routine. I have both queries wrapped around the <cftransaction> tags. Everytime I call that Insert function both queries are executed.

I also think you are missing a piece here. If you have a CFC component then you need to explicitly let your page know you are using that component. In my situation my CFC resides in the session scope, so I call its methods by typing: "session.SISDBAccess.methods" where session is the session, SISDBAccess is a user defined property of my component and methods is the method I am calling.

In your code your "cfreturn" tag should be out of the cftransaction tags. I hope this helps, I also suggest that if this does not solve your problem you take the time to provide as many details as possible about your scenario.

[Ofeargall]

I wanted to jump back and post the operational code now that I have it working. Thank you Dan and apocalipsis19 for your time and direction. I'll need to learn more about moving this type of functionality into the sessions scope. That sounds like a great trick if not the proper way to do this.