Get Recordset from Stored Procedure on DB2

This tip provided by Experts Exchange.

Question:

This is code:

<CFSTOREDPROC PROCEDURE="proc1" DATASOURCE="PIKSINDLIVE" DEBUG>
    <CFPROCPARAM TYPE="IN" CFSQLTYPE=CF_SQL_INTEGER VALUE=10333 DBVARNAME=inp1>
    <CFPROCRESULT NAME = RS1>
</CFSTOREDPROC>

<CFOUTPUT>
Record Count: #RS1.RecordCount#
<br>
Columns: #RS1.ColumnList#
</CFOUTPUT>

this is result:

Record Count: 6350
Columns: 1,2

this is SP:

CREATE PROCEDURE DB2INST1.Proc1 (IN inp1 INT  )
   LANGUAGE SQL
P1: BEGIN
DECLARE cur1 CURSOR WITH RETURN TO CALLER FOR
   SELECT ID, LINE1 FROM ADDRESS;
OPEN cur1;
END P1   

How I can loop through records in the result set?

Comment:

have you tried:

<CFOUTPUT>
Record Count: #RS1.RecordCount#
<br>
Columns: #RS1.ColumnList#

<cfloop query=rs1>
Rrs1.ID# #rs1.line1#<br>
</cfloop>

</CFOUTPUT>

not familiar with db2 storedproc syntax, so not sure about that one.

incidentally, you can probably also call the storedprocedure like:

<cfquery name=rs1 ...etc...>
Proc1 10333
</cfquery>

cheers.

Comment:

Hi check it Out
<CFSTOREDPROC PROCEDURE="foo_proc"
   DATASOURCE="MY_SYBASE_TEST"    USERNAME="sa"
   PASSWORD=""    DBSERVER="scup"    DBNAME="pubs2"
   RETURNCODE="YES"    DEBUG>
<CFPROCRESULT NAME = RS1>
<CFPROCRESULT NAME = RS3 RESULTSET = 3>

<CFPROCPARAM TYPE="IN"
   CFSQLTYPE=CF_SQL_INTEGER
       VALUE="1"    DBVARNAME=@param1>
       
<CFPROCPARAM TYPE="OUT"    CFSQLTYPE=CF_SQL_DATE
   VARIABLE=FOO DBVARNAME=@param2>
</CFSTOREDPROC>
<CFOUTPUT>
The output param value: '#foo#'
<br>
</CFOUTPUT>
<h3>The Results Information</h3>
<CFOUTPUT QUERY = RS1>#NAME#,#DATE_COL#
<br>
</CFOUTPUT>
<P>
<CFOUTPUT>
<hr>
<P>Record Count: #RS1.RecordCount# >p>Columns: #RS1.ColumnList#
<hr>
</CFOUTPUT>
<CFOUTPUT QUERY=RS3>#col1#,#col2#,#col3#
<br>
</CFOUTPUT>
<P>
<CFOUTPUT>
<hr>
<P>Record Count: #RS3.RecordCount# <P>Columns: #RS3.ColumnList#
<hr>
The return code for the stored procedure is:
 '#CFSTOREDPROC.STATUSCODE#'<br>
</CFOUTPUT>

Harry

Comment:

Guys, I read documentation very carefully. If you will see my question, you mention that

#RS1.ColumnList#

return value "1,2"

I cannot write

<cfoutput>
#1# #2#<br>
</cfoutput>

Isn't it?

Comment:

Guys, I read documentation very carefully. If you will see my question, you mention that
> #RS1.ColumnList#
> return value "1,2"

oh yes, sorry - i missed that :-}

> I cannot write
> <cfoutput>
> #1# #2#<br>
> </cfoutput>

what happens when you do that? it would seem inuitive to go something like:

<cfoutput query=RS1>
#rs1.1# #rs1.2#<br>
</cfoutput>

which is essentially what you are doing... do you get some error? what does it tell you?

cheers.

Comment:

meverest :

which is essentially what you are doing... do you get some error? what does it tell you?

ERROR MESSAGE:

Invalid expression format. The usual cause is an error in the expression structure.

Comment:

why cant u use the column names manually .
I mean why do u need to get it like this (by using columnlist)

Jimmy

Comment:

invalid expression sounds more like a problem with the procedure than problem with cf script....

CREATE PROCEDURE DB2INST1.Proc1 (IN inp1 INT  )
  LANGUAGE SQL
P1: BEGIN
DECLARE cur1 CURSOR WITH RETURN TO CALLER FOR
  SELECT ID, LINE1 FROM ADDRESS;
OPEN cur1;
END P1   

now where is 'inp1' incidentally - it is not even used in the stored proc, so why bother even passing
it? is this stored procedure something you have built yourself, or have you copies it or modified from
something else?

forgive me - i am not familiar with db2 SPs, but it just doesn't seem right...

cheers.

Comment:

You can do #1# #2# use the evaluate function.

Written on 2/22/2001