Ok, I have go it to work, but I would like to know why.
This code works:
<cfset newCompCode = dataCompCode(FORM.COMPCODE)>
<cfset oldCompCode = dataCompCode(FORM.COMPCODE_OLD)>
<cfquery datasource="WEBCNTRL" result="stats">
UPDATE INTRANET.TRAK_CMP
SET LOC_CODE = '#FORM.LOCCODE#',
CMP_CD = '#newCompCode#',
SCAN_DATE = #CreateODBCDateTime(FORM.Date)#
WHERE USER_ID = '#FORM.USER#'
AND ENTRY_DATE =
#CreateODBCDateTime(FORM.ENTRY_DATE)#
AND UNIT_NO = '#FORM.UNITNO#'
AND CMP_CD = '#oldCompCode#'
</cfquery>
This code does not.
<cfquery datasource="WEBCNTRL" result="stats">
UPDATE INTRANET.TRAK_CMP
SET LOC_CODE = '#FORM.LOCCODE#',
CMP_CD = '#dataCompCode(FORM.COMPCODE)#',
SCAN_DATE = #CreateODBCDateTime(FORM.Date)#
WHERE USER_ID = '#FORM.USER#'
AND ENTRY_DATE =
#CreateODBCDateTime(FORM.ENTRY_DATE)#
AND UNIT_NO = '#FORM.UNITNO#'
AND CMP_CD =
'#dataCompCode(FORM.COMPCODE_OLD)#'
</cfquery>
As you can see in the second code I call a UDF inside the SQL statement
in the former I call the function outside setting local variables used
in the following SQL statement.
Here is the UDF code:
<cffunction name="dataCompCode" returntype="string" output="false">
<cfargument name="rawCode" type="string" required="yes">
<!--- IF Component Code is ISBT --->
<cfif len(trim(arguments.rawCode)) EQ 10 AND
ucase(left(trim(arguments.rawCode),3)) EQ "=<E">
<!--- Return ISBT Component Code --->
<cfquery datasource="#Client.WTDSN#"
name="component_lookup">
SELECT
L_CMT_CMPCD
FROM
EBIS.LAB_CMT_DB_REC
WHERE
L_CMT_CMPCOD8 = <cfqueryparam
value="#mid(trim(arguments.rawCode),3,5)#" cfsqltype="cf_sql_varchar">
AND
L_CMT_DIVIDE8 = <cfqueryparam
value="#right(trim(arguments.rawCode),2)#" cfsqltype="cf_sql_varchar">
</cfquery>
<cfreturn component_lookup.L_CMT_CMPCD>
<cfelse>
<!--- Return CODABAR Compnent Code --->
<cfreturn arguments.rawCode>
</cfif>
</cffunction>
I am presuming it is an issue with the UDF making a <cfquery...> call.
But why should that be a problem? Is this documented anywhere?
Confidentiality Notice: This message including any
attachments is for the sole use of the intended
recipient(s) and may contain confidential and privileged
information. Any unauthorized review, use, disclosure or
distribution is prohibited. If you are not the
intended recipient, please contact the sender and
delete any copies of this message.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 &
Flex 2
Free Trial
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:281058
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
