I personally can't see any problems with using
<cftransaction isolation="SERIALIZABLE">
apart from slowing things by cueing the threads. Besides, it's the
default setting if you don't specify the isolation mode (as of CFMX)
the number of queries to run shouldn't matter that much at all since
they come under the umbrella of the transaction
I also can't see a problem using @@identity to get the autonumber and
then inserting that as a foreign key - provided they are under the same
transaction.
in my ASP days, they had to be done under the same db connection (ADO)
because if you used different connection objects (or opened and closed
for each conn.execute(SQL) run since the conn objects were pooled) there
could be (up to) a 3 second delay before the insert happens and
therefore the @@identity would return the wrong number. caught me and
others out heaps before I clicked on how it all worked (but then again I
wasn't programming against MTS with using transactions as such)
I think (feel free for someone else to jump in here) it's to do with the
stack calls and that's why CFTRANSACTION covers it
______xxxx______ (query within called method)
____xxxxxxxx____ (called method)
__xxxxxxxxxxxx__ (outside method + trans)
another thing - I've moved away from TRY/CATCH within the trans and the
commit/rollback actions. if one query fails (for whatever reason) the
trans rolls back automatically. T/C is a bit surpurflous in this case
and returning true/false can let the calling code know if it worked or
not (but no error message)
here's an example of a transaction calling a couple of methods. there's
lots here but it might help.
if anyone has more specific/advanced knowledge about CFTRANSACTION,
please feel free to chime in. Half of this stuff I've mentioned is
educated guesses or research using very thin information.
hope it helps
cheers
barry.b
<!---===== edit existing requisition (container only) =====--->
<cffunction name="Update" returntype="numeric" access="public"
output="No"
displayname="update requisition (container)"
hint="transaction over the updating of master and details or a
requisition, returns requisition edit number or 0 if error">
<cfset var success = TRUE>
<cfset var reqNum = 0><!-- to store and return the updated
requisition number --->
<cfset isInit = checkInit()><!--- see if component correctly
initialised first --->
<!---
*** start transaction ***
UpdateMaster()
UpdateDetails()
*** end transaction ***
--->
<cftransaction action="BEGIN" isolation="SERIALIZABLE">
<cftry>
<cfset success = UpdateMaster()><!--- update
master properties, as set in calling page (uses this.REQ_NUM) --->
<cfset success = UpdateDetails()><!--- same for
the details (delete details then unpack arr of structs, inserting each
one (uses this.REQ_NUM) --->
<cfif success EQ TRUE>
<cftransaction action="COMMIT" />
</cfif>
<cfcatch type="Any">
<cfset success = FALSE>
<cftransaction action="ROLLBACK" />
<cfrethrow><!--- send the error message
back to the calling page --->
</cfcatch>
</cftry>
</cftransaction>
<cfif success>
<cfset reqNum = this.REQ_NUM>
</cfif>
<cfreturn reqNum />
</cffunction>
and here's an example of cftransaction over a very long process. I
wouldn't write it like this today but it does work
<cffunction name="Authorise" returntype="boolean" access="public">
<cfargument name="req_num" type="numeric" required="Yes" />
<cfargument name="auth_note" type="string" required="Yes" />
<cfargument name="status_flg" type="string" required="Yes" />
<cfargument name="auth_code" type="string" required="Yes" />
<cfset var success = "FALSE" />
<!--- authorisation logic:
if status_flg = "R" // req is rejected
// update status and leave
UPDATE reqhead
SET status_flg = "R",
auth_code = "#ARGUMENTS.auth_code#",
auth_note = "#ARGUMENTS.auth_note#"
WHERE req_num = "#ARGUMENTS.req_num#"
else
(1) get Next_order_num
(2a) get delivery details
(2b) get vendor for this requisition (used everywhere)
(3) update reqhead with delivery details, form data and
PKey
(4) get data from reqhead and insert into purchhead
(5) get some of the data from reqdetl and insert into
purchdetl
(6) put the rest of the data from reqdetl and insert
into po_audit
(7) increase Next_order_num
end if
--->
<cfif ARGUMENTS.status_flg EQ "R"><!--- req is rejected, update
status and leave --->
<cfquery name="uAuthorise" datasource="#this.dsnInfo.dsnName#"
password="#this.dsnInfo.dsnPassword#"
username="#this.dsnInfo.dsnUsername#">
UPDATE reqhead
SET status_flg = <cfqueryparam cfsqltype="CF_SQL_CHAR"
value="R">,
authorise_code = <cfqueryparam
cfsqltype="CF_SQL_CHAR" value="#ARGUMENTS.auth_code#">,
authorise_note = <cfqueryparam
cfsqltype="CF_SQL_CHAR" value="#ARGUMENTS.auth_note#">
WHERE req_num = <cfqueryparam cfsqltype="CF_SQL_INTEGER"
value="#ARGUMENTS.req_num#">
</cfquery>
<cfset success = "TRUE" />
<cfelse>
<cftransaction isolation="SERIALIZABLE">
<!--- (1) get Next_order_num: qNext_po_num.order_num --->
<cfquery name="qNext_po_num"
datasource="#this.dsnInfo.dsnName#"
password="#this.dsnInfo.dsnPassword#"
username="#this.dsnInfo.dsnUsername#">
SELECT next_po_num as order_num FROM puparms
WHERE cmpy_code = <cfqueryparam cfsqltype="CF_SQL_CHAR"
value="#this.cmpy_code#">
</cfquery>
<!--- (2a) get delivery details --->
<cfquery name="qDelivery"
datasource="#this.dsnInfo.dsnName#"
password="#this.dsnInfo.dsnPassword#"
username="#this.dsnInfo.dsnUsername#">
SELECT
ADDR1_TEXT, ADDR2_TEXT, CITY_TEXT,
STATE_CODE || POST_CODE AS state_post,
'' as del_country, DESC_TEXT
FROM
warehouse
WHERE
WARE_CODE
IN
(
SELECT WARE_CODE
FROM reqhead
WHERE req_num = <cfqueryparam
cfsqltype="CF_SQL_INTEGER" value="#ARGUMENTS.req_num#">
AND cmpy_code = <cfqueryparam
cfsqltype="CF_SQL_CHAR" value="#this.cmpy_code#">
)
</cfquery>
<!--- (2b) get vendor for this requisition (used everywhere:
qVendor.VEND_CODE) --->
<cfquery name="qVendor"
datasource="#this.dsnInfo.dsnName#"
password="#this.dsnInfo.dsnPassword#"
username="#this.dsnInfo.dsnUsername#">
SELECT VEND_CODE from reqhead
WHERE req_num = <cfqueryparam
cfsqltype="CF_SQL_INTEGER" value="#ARGUMENTS.req_num#">
AND cmpy_code = <cfqueryparam
cfsqltype="CF_SQL_CHAR" value="#this.cmpy_code#">
</cfquery>
<!--- (3) update reqhead with delivery details, form data and PKey
--->
<cfquery name="uReqhead"
datasource="#this.dsnInfo.dsnName#"
password="#this.dsnInfo.dsnPassword#"
username="#this.dsnInfo.dsnUsername#">
UPDATE reqhead
SET status_flg = <cfqueryparam
cfsqltype="CF_SQL_CHAR" value="C">,
authorise_code = <cfqueryparam
cfsqltype="CF_SQL_CHAR" value="#ARGUMENTS.auth_code#">,
authorise_note = <cfqueryparam
cfsqltype="CF_SQL_CHAR" value="#ARGUMENTS.auth_note#">,
order_num = <cfqueryparam
cfsqltype="CF_SQL_INTEGER" value="#qNext_po_num.order_num#">,
DEL_ADDR1_TEXT = <cfqueryparam
cfsqltype="CF_SQL_CHAR" value="#qDelivery.ADDR1_TEXT#">,
DEL_ADDR2_TEXT = <cfqueryparam
cfsqltype="CF_SQL_CHAR" value="#qDelivery.ADDR2_TEXT#">,
DEL_ADDR3_TEXT = <cfqueryparam
cfsqltype="CF_SQL_CHAR" value="#qDelivery.CITY_TEXT#">,
DEL_ADDR4_TEXT = <cfqueryparam
cfsqltype="CF_SQL_CHAR" value="#qDelivery.state_post#">,
DEL_COUNTRY_TEXT = <cfqueryparam
cfsqltype="CF_SQL_CHAR" value="#qDelivery.del_country#">,
DEL_NAME_TEXT = <cfqueryparam
cfsqltype="CF_SQL_CHAR" value="#qDelivery.DESC_TEXT#">
WHERE req_num = <cfqueryparam
cfsqltype="CF_SQL_INTEGER" value="#ARGUMENTS.req_num#">
AND cmpy_code = <cfqueryparam
cfsqltype="CF_SQL_CHAR" value="#this.cmpy_code#">
</cfquery>
<!--- (4) get data from reqhead and insert into purchhead (uses
Next_order_num) this uses a "insert into select ..." see the informix
tutorial book pg 4-10 --->
<cfquery name="iPurchhead"
datasource="#this.dsnInfo.dsnName#"
password="#this.dsnInfo.dsnPassword#"
username="#this.dsnInfo.dsnUsername#">
INSERT INTO purchhead(
AUTHORISE_CODE, CMPY_CODE, COM1_TEXT,
COM2_TEXT, DEL_ADDR1_TEXT, DEL_ADDR2_TEXT, DEL_ADDR3_TEXT,
DEL_ADDR4_TEXT, DEL_COUNTRY_TEXT, DEL_NAME_TEXT, DUE_DATE, ENTER_CODE,
ENTRY_DATE, ORDER_NUM,
PERIOD_NUM, PRINTED_FLAG, PUR1_FLG,
PUR2_FLG, PUR3_FLG, PUR4_TEXT, PUR5_TEXT, PUR6_TEXT, SALESPERSON_TEXT,
STATUS_IND,
TAX_CODE,
TERM_CODE,
TYPE_IND, VEND_CODE, WARE_CODE, YEAR_NUM
)
SELECT
AUTHORISE_CODE, CMPY_CODE, COM1_TEXT,
COM2_TEXT, DEL_ADDR1_TEXT, DEL_ADDR2_TEXT, DEL_ADDR3_TEXT,
DEL_ADDR4_TEXT, DEL_COUNTRY_TEXT, DEL_NAME_TEXT, DUE_DATE, ENTRY_CODE,
ENTRY_DATE, ORDER_NUM,
#month(now())#, "N", PUR1_FLG, PUR2_FLG,
PUR3_FLG, PUR4_TEXT, PUR5_TEXT, PUR6_TEXT, SALESPERSON_TEXT, "O",
(SELECT TAX_CODE FROM VENDOR WHERE
VEND_CODE = <cfqueryparam cfsqltype="CF_SQL_CHAR"
value="#qVendor.VEND_CODE#"> AND cmpy_code = <cfqueryparam
cfsqltype="CF_SQL_CHAR" value="#this.cmpy_code#">),
(SELECT TERM_CODE FROM VENDOR WHERE
VEND_CODE = <cfqueryparam cfsqltype="CF_SQL_CHAR"
value="#qVendor.VEND_CODE#"> AND cmpy_code = <cfqueryparam
cfsqltype="CF_SQL_CHAR" value="#this.cmpy_code#">),
"3", VEND_CODE, WARE_CODE, #year(now())#
FROM
REQHEAD
WHERE
order_num = <cfqueryparam
cfsqltype="CF_SQL_INTEGER" value="#qNext_po_num.order_num#">
AND cmpy_code = <cfqueryparam
cfsqltype="CF_SQL_CHAR" value="#this.cmpy_code#">
</cfquery>
<!--- (5) get some of the data from reqdetl and insert into purchdetl
--->
<cfquery name="iPurchdetl"
datasource="#this.dsnInfo.dsnName#"
password="#this.dsnInfo.dsnPassword#"
username="#this.dsnInfo.dsnUsername#">
INSERT INTO purchdetl (
ACCT_CODE, CMPY_CODE, DESC_TEXT,
LINE_NUM, OEM_TEXT,
ORDER_NUM,
REF_TEXT, SEQ_NUM, TYPE_IND,
VEND_CODE
)
SELECT
ACCT_CODE, CMPY_CODE, DESC_TEXT,
LINE_NUM, OEM_TEXT,
#qNext_po_num.order_num#,
REF_TEXT, 1, "G",
"#qVendor.VEND_CODE#"
FROM
reqdetl
WHERE
req_num = <cfqueryparam
cfsqltype="CF_SQL_INTEGER" value="#ARGUMENTS.req_num#">
AND
cmpy_code = <cfqueryparam
cfsqltype="CF_SQL_CHAR" value="#this.cmpy_code#">
</cfquery>
<!--- (6) put the rest of the data from reqdetl and insert into po_audit
--->
<cfquery name="iPoaudit"
datasource="#this.dsnInfo.dsnName#"
password="#this.dsnInfo.dsnPassword#"
username="#this.dsnInfo.dsnUsername#">
INSERT INTO poaudit(
CMPY_CODE, DESC_TEXT, ENTRY_CODE,
ENTRY_DATE,
EXT_COST_AMT, EXT_TAX_AMT, LINE_NUM,
LINE_TOTAL_AMT,
NOW_AUTH_FLAG, ORDER_QTY,
ORIG_AUTH_FLAG, PERIOD_NUM,
POSTED_FLAG, PO_NUM, RECEIVED_QTY,
SEQ_NUM,
TRAN_CODE, TRAN_DATE, TRAN_NUM,
UNIT_COST_AMT,
UNIT_TAX_AMT, VEND_CODE, VOUCHER_QTY,
YEAR_NUM
)
SELECT
CMPY_CODE, DESC_TEXT,
"#ARGUMENTS.auth_code#", #NOW()#,
unit_amt * order_qty, 0, LINE_NUM,
unit_amt * order_qty,
"N", order_qty, "N", #month(now())#,
"N", #qNext_po_num.order_num#, 0 , 1,
"AA", #now()#, 1, unit_amt,
0, "#qVendor.VEND_CODE#", 0,
#year(now())#
FROM
reqdetl
WHERE
req_num = <cfqueryparam
cfsqltype="CF_SQL_INTEGER" value="#ARGUMENTS.req_num#">
AND
cmpy_code = <cfqueryparam
cfsqltype="CF_SQL_CHAR" value="#this.cmpy_code#">
</cfquery>
<!--- (7) increase Next_order_num --->
<cfquery name="uNext_po_num"
datasource="#this.dsnInfo.dsnName#"
password="#this.dsnInfo.dsnPassword#"
username="#this.dsnInfo.dsnUsername#">
UPDATE puparms SET next_po_num = next_po_num + 1
WHERE cmpy_code = <cfqueryparam cfsqltype="CF_SQL_CHAR"
value="#this.cmpy_code#">
</cfquery>
</cfif>
<cfset success = "TRUE" />
</cftransaction>
<cfreturn success />
</cffunction>
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
Aussie Macromedia Developers: http://lists.daemon.com.au/
