Hi Adrian,
Thank you for your suggestions! Below are the results:
***********
The log still records "example_status is undefined". Here's the cfquery:
<cffunction name="getExample_status" output="true" access="public"
returntype="query">
<cfargument name="status" hint="Argument for status."> <cfset var
example_status = "" > <cfquery name="example_status"
datasource="#application.dsn#" username="
test" password="test">
<!---
UPDATE test_view
SET status =
UPPER(TRIM(<cfqueryparam value="#arguments.status#"
cfsqltype="cf_sql_varchar">)) --->
SELECT * FROM test_view
WHERE test_id IN (
SELECT test_ID
FROM test_VIEW
WHERE UPPER(TRIM(STATUS)) =
UPPER(TRIM(
<cfqueryparam value="#arguments.status#"
cfsqltype="cf_sql_varchar">))
AND EXPIRE_DATE <= SYSDATE)
</cfquery>
<cfdump var="#example_status#" expand="yes"> <cfabort> <cfreturn
example_status> </cffunction>
I ran the query and got the same result as before: 0 records with empty status
values, just as if I had never run the update, with no error.
When I change back to the UPDATE statement, I copy the cfdump to before the
query, and it shows [empty string]. The log records the same error statement,
and of course no records are updated (as can be expected if the query variable
is undefined). Strange that example_status is recognized just before the query
but not just after.
Any ideas?
Thanks, Teresa
**************
>You're running an UPDATE and don't return a query.
>
>To see the problem:
>
>- change output to true
>- add <cfdump var="#example_status#"><cfabort> under the cfquery block
>- run the page
>
>You should get a example_status is undefined error.
>
>Then:
>
>- comment out your UPDATE code
>- add SELECT * FROM test_VIEW in it's place
>- run the page again
>
>You should now get a dump of the query.
>
>Adrian
>
>-----Original Message-----
>From: Robinson, Teresa L. BUMED Contractor
>[mailto:[EMAIL PROTECTED]
>Sent: 22 June 2007 18:53
>To: CF-Newbie
>Subject: Query works in SQL*Plus, not in CF
>
>
>Hello all,
>
>I'm using CF MX7 and DW CS3, on WinXP with Oracle 10g.
>
>I have a SQL query in a CFC that works beautifully in SQL*Plus but not
>in CF. I use F12 to run the page in the server that comes with CF.
>
>Here's the function in the CFC:
>
><cffunction name="getExample_status" output="false" access="public"
>returntype="query">
><cfargument name="status" hint="Argument for status.">
><cfset var example_status = "" >
><cfquery name="example_status" datasource="#application.dsn#" username="
>test" password="test">
>UPDATE test_view
> SET status =
> UPPER(TRIM(<cfqueryparam value="#arguments.status#"
>cfsqltype="cf_sql_varchar">))
> WHERE test_id IN (
> SELECT test_ID
> FROM test_VIEW
> WHERE UPPER(TRIM(STATUS)) =
> UPPER(TRIM(
> <cfqueryparam value="#arguments.status#"
>cfsqltype="cf_sql_varchar">))
> AND EXPIRE_DATE <= SYSDATE)
></cfquery>
><cfreturn example_status>
></cffunction>
>
>Here's the code in the CFM:
>
><cfinvoke
> component="Dev.components.example_queries"
> method="getExample_status"
> returnvariable="example_s">
><!--- CFC Query --->
><cfinvokeargument name="status" value="ACT">
></cfinvoke>
>
>The recordcount comes back -1. I don't know how to get more detailed
>debugging information than what you can set in the CF Administrator or
>by using the Server Debug button on the Documents bar. I'd really like
>to step into the code, but I can't find out how to do that or even if
>it's possible.
>
>This is the entry in the log file:
>
>"Error","web-13","06/22/07","13:22:34","HRKS","Event name: onRequest"
>"Error","web-13","06/22/07","13:22:34","HRKS","Message: Event Handler
>Exception."
>"Error","web-13","06/22/07","13:22:34","HRKS","Root Cause Message:
>Variable EXAMPLE_STATUS is undefined."
>
>Since the log file names the query in the CFC, does that mean I've coded
>something wrong there? I've re-written it three times and this is still
>happening.
>
>Any advice is much appreciated, and thanks in advance.
>Teresa
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ
Archive:
http://www.houseoffusion.com/groups/CF-Newbie/message.cfm/messageid:2871
Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
