Can't Access Stored Procedure in CFC

Get Started
1

What is wrong with this code?

I have this in my application.cfc, but my output page cannot find the stored procedure.

<cffunction name=“OnSessionStart” access=“public” returntype=“any” output=“true”>

<cfset monthIN = #LSDateFormat(now(), ‘MM’)# & ‘-1-’ & #LSDateFormat(now(), ‘YYYY’)#>
<cfstoredproc procedure=“spRSalesAgentAppAndIndCount” dataSource=“SalesNumbersLCD” returncode=“yes” debug=“yes”>

&lt;cfprocresult name="AppsReceived"&gt;
&lt;cfprocparam type="in" value="#monthIN#" cfsqltype="cf_sql_timestamp"&gt;
&lt;cfprocparam type="in" value="#now()#" cfsqltype="cf_sql_timestamp"&gt;&lt;/cfstoredproc&gt;

	&lt;cfreturn AppsReceived&gt;

&lt;/cffunction&gt;

Output page says that it cannot find my AppsReceived query result from the cfc.

What the heck?

Thanks in advance.

Matt

2

maybe because it is not saved for the session? I am new to CFCs…

My main objective is to only run this stored procedure once a day. Cannot use the cachewithin attribute since I am on MX7

Matt

3

figured it out, The OnSessionStart function will not return anything, so I put the result inside a session variable and then on my output page called the session.variable in the cfquery tag instead of the stored procedure result.

4

Glad you figured it out.

It’s good to get into the habit of VAR scoping all cffunction local variables. Lack of VAR scoping can cause thread problems with cfc’s stored in shared scopes.


<cffunction .....>
    <cfargument ....>

    <cfset [COLOR="Red"]VAR [/COLOR]AppsReceived = "" >
    <cfset [COLOR="Red"]VAR [/COLOR]monthIN = #LSDateFormat(now(), 'MM')# & '-1-' & #LSDateFormat(now(), 'YYYY')#>

    .... run stored procedure ...
    <cfset session.yourResultVariable = AppsReceived >
</cffunction>

Also, though the date string may work for you, it’s better to use date objects when possible.

ie
<cfprocparam type=“in” value=“#createDate(year(now()), month(now()), 1)#” cfsqltype=“cf_sql_timestamp”>

instead of

<!— note, you do need the pound # signs around LSDateFormat either —>
<cfset monthIN = #LSDateFormat(now(), ‘MM’)# & ‘-1-’ & #LSDateFormat(now(), ‘YYYY’)#>
<cfprocparam type=“in” value=“#monthIN#” cfsqltype=“cf_sql_timestamp”>
createDate(year(now()), month(now()), 1)