Get Recordset from Stored Procedure on DB2

This tip provided by Experts Exchange.

Question:

This is code:

Record Count: #RS1.RecordCount#

Columns: #RS1.ColumnList#

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:

Record Count: #RS1.RecordCount#

Columns: #RS1.ColumnList#

Rrs1.ID# #rs1.line1#

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

incidentally, you can probably also call the storedprocedure like:

Proc1 10333

cheers.

Comment:

Hi check it Out
   DATASOURCE=”MY_SYBASE_TEST”   USERNAME=”sa”
   PASSWORD=””   DBSERVER=”scup”    DBNAME=”pubs2″
   RETURNCODE=”YES”    DEBUG>

   CFSQLTYPE=CF_SQL_INTEGER
       VALUE=”1″   DBVARNAME=@param1>
       
   VARIABLE=FOO DBVARNAME=@param2>


The output param value: ‘#foo#’

The Results Information

#NAME#,#DATE_COL#

Record Count: #RS1.RecordCount# >p>Columns: #RS1.ColumnList#

#col1#,#col2#,#col3#

Record Count: #RS3.RecordCount#

Columns: #RS3.ColumnList#

The return code for the stored procedure is:
 ‘#CFSTOREDPROC.STATUSCODE#’

Harry

Comment:

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

#RS1.ColumnList#

return value “1,2”

I cannot write

#1# #2#

Isn’t it?

Comment:

Guys,I read documentation very carefully. If you will see my question, you mentionthat
> #RS1.ColumnList#
> return value “1,2”

oh yes, sorry – i missed that :-}

> I cannot write
>
> #1# #2#

>

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

#rs1.1# #rs1.2#

which is essentially what you are doing… do you get some error? what does ittell you?

cheers.

Comment:

meverest :

which is essentially what you are doing… do you get some error? what does ittell you?

ERROR MESSAGE:

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

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 theprocedure 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, sowhy bother even passing
it? is this stored procedure something you have built yourself, or have youcopies 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