Using cftransaction in a cfc
in [Advanced Techniques]
|
Prev: problem with xmlParse()
Next: webservice does not work
From: Ofeargall on 15 Apr 2008 23:02 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? <cffunction name="insertArticle" access="public" returntype="query" hint="For Adding a New Article to the DB"> <cfargument name="formData" type="struct" required="yes"> <cfquery datasource="#application.datasource#" username="#application.username#" password="#application.password#"> INSERT INTO dds_articles (articles_categoryid, articles_datecreated, articles_datemodified, articles_headline, articles_subcatid, articles_subhead, articles_text, articles_documents) VALUES (<cfqueryparam value="#formData.articles_categoryid#" cfsqltype="cf_sql_integer">, <cfqueryparam value="#formData.articles_datecreated#" cfsqltype="cf_sql_date">, <cfqueryparam value="#formData.articles_datemodified#" cfsqltype="cf_sql_date">, <cfqueryparam value="#formData.articles_headline#" cfsqltype="cf_sql_varchar">, <cfqueryparam value="#formData.articles_subcatid#" cfsqltype="cf_sql_integer">, <cfqueryparam value="#formData.articles_subhead#" cfsqltype="cf_sql_varchar">, <cfqueryparam value="#formData.articles_text#" cfsqltype="cf_sql_longvarchar">, <cfqueryparam value="#formData.articles_documents#" cfsqltype="cf_sql_integer">) </cfquery> <cffunction name="getLastID" access="public" returntype="query" hint="Retrns ID from most recent INSERT"> <cfquery name="qNewID" datasource="#application.datasource#" username="#application.username#" password="#application.password#"> SELECT_LAST_INSERT() AS NewID </cfquery><cfreturn qNewID> </cffunction>
From: GArlington on 16 Apr 2008 06:53 On Apr 16, 4:02 am, "Ofeargall" <webforumsu...(a)macromedia.com> wrote: > 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? > > <cffunction name="insertArticle" access="public" returntype="query" hint="For > Adding a New Article to the DB"> > <cfargument name="formData" type="struct" required="yes"> > <cfquery datasource="#application.datasource#" > username="#application.username#" password="#application.password#"> > INSERT INTO dds_articles > (articles_categoryid, articles_datecreated, > articles_datemodified, articles_headline, articles_subcatid, articles_subhead, > articles_text, articles_documents) > VALUES > (<cfqueryparam value="#formData.articles_categoryid#" > cfsqltype="cf_sql_integer">, <cfqueryparam > value="#formData.articles_datecreated#" cfsqltype="cf_sql_date">, <cfqueryparam > value="#formData.articles_datemodified#" cfsqltype="cf_sql_date">, > <cfqueryparam value="#formData.articles_headline#" cfsqltype="cf_sql_varchar">, > <cfqueryparam value="#formData.articles_subcatid#" cfsqltype="cf_sql_integer">, > <cfqueryparam value="#formData.articles_subhead#" cfsqltype="cf_sql_varchar">, > <cfqueryparam value="#formData.articles_text#" cfsqltype="cf_sql_longvarchar">, > <cfqueryparam value="#formData.articles_documents#" cfsqltype="cf_sql_integer">) > </cfquery> > > <cffunction name="getLastID" access="public" returntype="query" > hint="Retrns ID from most recent INSERT"> > <cfquery name="qNewID" datasource="#application.datasource#" > username="#application.username#" password="#application.password#"> > SELECT_LAST_INSERT() AS NewID > </cfquery><cfreturn qNewID> > </cffunction> Wrap all (both in your case) queries in <cftransaction>... (YourQueriesHoweverManyYouNeedToSync)...</cftransaction>
From: Dan Bracuk on 16 Apr 2008 08:28 The short answer is that you put the cftransaction tags around the two queries.
From: apocalipsis19 on 16 Apr 2008 10:15 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!
From: Ofeargall on 16 Apr 2008 10:37
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. |