CF8 -> CF9 Migration : CfProcResult Issue

[parkerst]

Hi Guys,

We recently migrated our cf8 application to a new server running cf9 and this piece of code is causing an absolute nightmare! I just don't understand what is happening (Obviously I've modified it a bit to protect sensitive information)

It exists within a cfc as part of the model glue framework (which is bad enough as it is).

I have checked our datasource setup to the old environment, its identical. Nor is there any reserve words...it's almost like the cfprocResult simply isn't setting the value.

The error we are getting is that results is a String and not actually a query.

<cfset var results = "" />

<cfif arguments.status EQ 'status1'>

    <cfstoredproc procedure="proc1" datasource="#_getConfig().getDsn()#">

        <cfif SESSION.role EQ "type1">

            <cfprocparam cfsqltype="cf_sql_integer" variable="var1" value="#SESSION.someVariable#">

        <cfelse>

            <cfprocparam cfsqltype="cf_sql_integer" variable="var2" value="#arguments.otherVariable#">

        </cfif>

        <cfprocresult name="results">

    </cfstoredproc>

<cfelseif arguments.status EQ 'status2'>

    <cfstoredproc procedure="proc2" datasource="#_getConfig().getDsn()#">

        <cfif SESSION.role EQ "type1">

            <cfprocparam cfsqltype="cf_sql_integer" variable="var1" value="#SESSION.somevariable#">

        <cfelse>

            <cfprocparam cfsqltype="cf_sql_integer" variable="var2" value="#arguments.otherVariable#">

        </cfif>

        <cfprocparam cfsqltype="cf_sql_varchar" variable="status" value="#arguments.status#" maxlength="100">

        <cfprocresult name="results">

    </cfstoredproc>

<cfelse>

    <cfstoredproc procedure="proc3" datasource="#_getConfig().getDsn()#">

        <cfif SESSION.role EQ "type1">

            <cfprocparam cfsqltype="cf_sql_integer" variable="var1" value="#SESSION.someVariable#">

        <cfelse>

            <cfprocparam cfsqltype="cf_sql_integer" variable="var2" value="#arguments.otherVariable#">

        </cfif>

        <cfprocparam cfsqltype="cf_sql_varchar" variable="status" value="#arguments.status#" maxlength="100">

        <cfprocresult name="results">

    </cfstoredproc>

</cfif>

<cfif SESSION.role EQ "type1">

    <cfquery name="results" dbtype="query">

        SELECT * FROM results WHERE column = '#SESSION.userID#' OR column IS NULL

    </cfquery>

</cfif>

Have I missed anything here? I'm not sure why this even happens. (Excuse the legacy code)

[parkerst]

The error we are getting is that results is a String and not actually a query.

Sorry I should add that the error occurs in this piece of code

<cfif SESSION.role EQ "type1">

    <cfquery name="results" dbtype="query">

        SELECT * FROM results WHERE column = '#SESSION.userID#' OR column IS NULL

    </cfquery>

</cfif>

when it gets to this point, results is still a string...

Should also add that I have tried explicitly setting the resultSet variable to no avail.

Additionally:

The proc itself has no explicit return out, only CREATE AS BEGIN SELECT, however, this worked fine in the CF8 Environment

Message was edited by: parkerst

[Dave Watts]

The error we are getting is that results is a String and not actually a query.

Sorry I should add that the error occurs in this piece of code

<cfif SESSION.role EQ "type1">

    <cfquery name="results" dbtype="query">

        SELECT * FROM results WHERE column = '#SESSION.userID#' OR column IS NULL

    </cfquery>

</cfif>

when it gets to this point, results is still a string...

My guess would be that you're using "results" as the name of the query

you're creating by selecting from "results".

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on

GSA Schedule, and provides the highest caliber vendor-authorized

instruction at our training centers, online, or onsite.

[parkerst]

I considered this however, if I dump out results before the query of queries, it comes out as [empty string]

odly enough, it works on my local environment too! Same cf version, same java version, same Database server version, identical database structure and data....but I'm missing some key component here which I don't understand.

[Dave Watts]

I considered this however, if I dump out results before the query of queries, it comes out as

odly enough, it works on my local environment too! Same cf version, same java version, same Database server version, identical

database structure and data....but I'm missing some key component here which I don't understand.

In that case, I'd find out which branch of that decision tree your

code is following in each case, and see if that makes a difference.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on

GSA Schedule, and provides the highest caliber vendor-authorized

instruction at our training centers, online, or onsite.

[parkerst]

Tried this also, by logging out which of the condition blocks are actually called and both local and production are providing the same result, in any case, results should always be a query because all 3 of those procs return a query.

[Adam Cameron.]

What's the actual value of "results"?  You don't mention this at any point (that I can see). That might give you an idea.  Or is it still an empty string (ie: what you're initialising it as in your VAR statement)?  Might you need to specify which result set you wish to go in that variable?

It seems very odd to me that this code works in one environment and not in another, with "identical" setups.  I think you need to do some investigation as to what's not idential.

For one thing, the DB is not likely to be the same one, I should think?

Have you done a profile on the DB to see what is being executed and with what params, and then replicated the call with some DB client to see what is being returned?

--

Adam

[parkerst]

As stated earlier, the actual value of results is an empty string as per the initalize. All the procedures return a value and work fine in cf8. If they didn't return, they wouldn't work in cf8. The code is identical.

This leads me to believe that results isn't being set or is returning null in the <cfprocresult.

As for the database themselves, they are literally identical too. We backed up one, restored it on the other. Important information is that 'user options' are identical on these db's as is the 'ANSI WARNINGS enabled' option under database properties.

The datasources are identically set up too, with the IP being different in the new environment. we have overlayed screenshots to ensure this.

Now, one of the blanket solutions offered online has been to set ANSI WARNINGS OFF on request, however, thats a pretty drastic solution and could result in a lot of re-compiling... (This fixes the problem temporarily anyway)

Message was edited by: parkerst

[parkerst · Accepted solution]

The issue is resolved by installing ColdFusion 9.0.1.x

The bug is #81153 as referenced here http://kb2.adobe.com/cps/847/cpsid_84726.html