Skip to main content
R
rhaddan
Inspiring
August 6, 2008
Answered

Problem with cfstoredproc

  • August 6, 2008
  • 1 reply
  • 941 views
I'm calling a stored procedure located on an IBM iSeries/DB2 database. The procedure runs fine, I receive notification of a successful call in the debug (see below). However, when I try to cfdump the variable "commentCaller", I receive the following error:

"Variable COMMENTCALLER is undefined"

The results in the debug are exactly what is expected. Why can't I access the contents of the variable?
This topic has been closed for replies.
Correct answer rhaddan
Sounds more like you are looking for the status code returned by your proc call rather than a value as an OUT parameter. If you are expecting to return "something" as a return code by setting returnCode = "yes", then that value would be returned in cfstoredproc.statusCode as such:

If you set returnCode = "Yes", this tag sets the variable prefix.statusCode, which holds the status code for a stored procedure. Status code values vary by DBMS. For the meaning of code values, see your DBMS documentation.

Phil
Thanks everyone for your input. This is only partially answered, so if anyone has more insight, feel free to add it.

My proc does have a returned value (a sequence number returned by the called function) that is different than the status code that is returned. I solved my immediate problem by adding variable parameters to each cfprocparam. I can then call the variable with the value returned.

However, I still get an error when I try to call the cfprocresult variable with cfdump.

1 reply

D
Daverms
Inspiring
August 7, 2008
Hi,

You should use the "cfprocresult" tag.

Try dumping your SP like this,

<cfstoredproc datasource="your_data_source" procedure="your_procedure_name">
<cfprocresult name="myQueryResultSet" />
<cfprocparam type = "IN" CFSQLType="cf_sql_varchar" value="#your_first_param#" dbvarname="@firstparam">
<!--- Your other parameters can be placed here --->
</cfstoredproc>

<cfdump var="#myQueryResultSet#">

HTH
R
rhaddanAuthor
Inspiring
August 7, 2008
Hi,

Thanks for the reply. I should have posted more code. I'm already doing what you suggested (code below). More testing leads me to believe that the issue may be to do with the returncode that the iSeries stored procedure requires as a passed parameter. If I try to call the program via SQL in cfquery, I receive an error that IN, OUT, and INOUT are not valid for the first parameter, which is the returncode.

The procedure executes with a status code of 0. I'm trying to find documentation on what that status code means.
P
paross1
Participating Frequently
August 9, 2008
Sounds more like you are looking for the status code returned by your proc call rather than a value as an OUT parameter. If you are expecting to return "something" as a return code by setting returnCode = "yes", then that value would be returned in cfstoredproc.statusCode as such:

If you set returnCode = "Yes", this tag sets the variable prefix.statusCode, which holds the status code for a stored procedure. Status code values vary by DBMS. For the meaning of code values, see your DBMS documentation.

Phil
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices