CF to MySQL stored proc question

[Guest]

looking at multiple examples of ColdFusion to MySql procedure calls and I think code example 1 should return a value in the OUT param. I get [empty string] displayed.

Code example 2 which changes the ColdFusion code by adding

<cfprocresult
name="searchResults">

and returning

<cfdump var="#searchResults#" label="Search Results">

works fine. I have about 10 examples including from Adobe coded as example 1. Please would somebody explain :-)

TIA,

Mic

[MichaelSJudd]

I noticed in the stored procedure you declared the variable as INT, but defined the cfprocparam return as numeric. You may want to try as cfsqltype="cf_sql_integer" (or change the variable declaration in the stored procedure) to see if that helps.

[Guest]

Tried ....

<cfprocparam
cfsqltype="cf_sql_integer"
type="out"
variable="var_out">

makes no difference 😞 - this should work, right? Not sure why the

<cfprocresult
name="searchResults"> works and the OUT var does not. I am presuming that cfprocresult is an array. I went back to basics - CF and a web page as I could not get the OUT int to return within Flex and wanted to simplify things. Problem with the array is that one has to ask Flex to dig within it to retrieve the int, and this will cause me to lose a point 🙂

[MichaelSJudd]

Well, when I use the Stored Procedures, I simply access the cfprocresult's RecordCount to see if anything was returned. The specifics (from example 2 that you had) is:

<cfif searchResults.RecordCount GT 0>
do the "yup he's a real guy"
<cfelse>
do your failure routine
</cfif>

You should get an error message, for instance, if you tried to do a cfoutput on that variable, that you are trying to access a complex data type (or something like that). So I would try to access the RecordCount as 0 or not 0 if you are simply looking to see if it found SOMETHING.

- Mike

[Guest]

Hi Mike, this is what is confusing me - from
http://www.adobe.com/devnet/coldfusion/articles/stored_procs.html - have attached code ......

There is no <cfprocresult> just the OUT parm. They are doing

SET arg_BookID = LAST_INSERT_ID(); to set the OUT parm as the SQL is an Insert, but my Select should bind the user_id into the Out parm - does in Oracle, Sybase and DB2 ...maybe MySQL was written by aliens :-)