Link to home
Create AccountLog in
Avatar of Camillia
CamilliaFlag for United States of America

asked on

Getting a "resultset" back from cursor

I use a cursor and concat lines together. I need to get a result set back...

I can get the values back by using OUT parameters but need one line of code returned. In my sql sever, i have : select @DName, @textNote and that gets me the resultset.

I used "select into" but not sure if it's correct. When i run the SP, i only see "return value =0".

This is what i have and tried:
Create Procedure Testing
(
    Selection varchar(1),
    Dcode varchar(10),
    UserLogin Varchar(10),
    AlertText varchar(4000) --,
  --  OUT DebtorName varchar(35),
   -- OUT textValue varchar(4000)
)
BEGIN
 DECLARE textNote VARCHAR(4000) ;
 DECLARE chunk VARCHAR(60);
 Declare  DName  VARCHAR(30);
 declare test varchar(30);
 
DECLARE c1 CURSOR WITH RETURN  For 
  SELECT ....
DECLARE CONTINUE HANDLER FOR NOT_FOUND
 
  set EOF_COND = -300;
      Open c1;
     SET textNote = ' ';
       fetch c1 into DName, chunk;
    while (EOF_COND =0 ) do
        SET textNote = textNote || ' ' || chunk ;
        fetch c1 into test, chunk;
       END While;
    Fetch c1 into DName,textNote;
--*** here i'm using select into *********
    select chunk into textNote from somerandomtable fetch first 1 rows only;
 
  -- Set DebtorName=DName;
 -- Set textValue = textNote;
 Close c1;

Open in new window

Avatar of momi_sabag
momi_sabag
Flag of United States of America image

so i don't understand you
want to get a resultset back or a single value ?
if you want to get back a result set you need to define the stored procedure to return a result set and then inside the stored procedure you declare a cursor and open it, and that cursor is returned to the calling code
a different method is to create a temporary table and insert the result set into that temporary table and then the code that called your procedure can access that temporary table and retrieve the result
the second approach is more simple and it's more like sql server
Avatar of Camillia

ASKER

1. I have a cursor that holds 2 columns and it has one row. Will post my SP below.  Just like doing:
   select col1,col2: ONLY one row and 2 columns

"that cursor is returned to the calling code"...*** I dont think i know how to return the cursor.

2.  thought about creating a physical table. Not sure how to do a temp table in AS400.
***but please take a look at the SP below. If I could return a cursor, that would be great.
Create Procedure testing
 
(
    Selection varchar(1),
    Dcode varchar(10),
    UserLogin Varchar(10),
    AlertText varchar(4000)-- ,
    --OUT DebtorName varchar(35),
    --OUT textValue varchar(4000)
 
)
  RESULT SETS 1
  LANGUAGE SQL
  Not Deterministic
BEGIN
 DECLARE textNote VARCHAR(4000) ;
 DECLARE chunk VARCHAR(60);
 Declare  DName  VARCHAR(30);
 declare test varchar(30);
DECLARE EOF_COND INTEGER Default 0;
DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
 
 DECLARE c1 CURSOR WITH RETURN  For 
 
  SELECT .....
 
DECLARE CONTINUE HANDLER FOR NOT_FOUND
  set EOF_COND = -300;
 
If Selection ='S' Then
      Open c1;
     SET textNote = ' ';
       fetch c1 into DName, chunk;
    while (EOF_COND =0 ) do
         SET textNote = textNote || ' ' || chunk ;
            fetch c1 into test, chunk;
    END While;
   --Fetch c1 into DName,textNote;
   -- select textNote into test from SomeTable fetch first 1 rows only;
   -- Set DebtorName=DName;
   --Set textValue = textNote;
  Close c1;
  END If;
 
 
 
  
 
 
 
END

Open in new window

sorry, i had already posted the SP in my orig post. I posted it again.
after "end while", i added this : SET RESULT SETS CURSOR c1;

called SP and i only get:

Return Code = 0
Statement ran successfully   (313 ms)
ohhh, I have "close c1;". I removed it. I get  the columns back with no row. I guess my cursor is blank..no?

So, now I have this:

      Open c1;
    SET textNote = ' ';
       fetch c1 into DName, chunk;
    while (EOF_COND =0 ) do
       SET textNote = textNote || ' ' || chunk ;
         fetch c1 into DName, chunk;
      END While;
SET RESULT SETS CURSOR c1;
Return;

Open in new window

I also see this msg:

SQL State: 0100C
Vendor Code: 466
Message: [SQL0466] 1 result sets are available from procedure Testing in XXX. Cause . . . . . :   Procedure Testing in XXX was called and has returned one or more result sets. Recovery  . . . :   None.
the reason you don't see the row is because of the fetch
remove the fetch from your code and you will see the row
which fetch? the one before "while" or the one inside "while"?

I have the fetch because...if i have 3 rows..it fetches each row one by one and concats.
ASKER CERTIFIED SOLUTION
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
yep, that's what i want to do.

So: declare c2 for return
       select DName, textnote from sysibm.sysdummy1

?? I will have to try it later tonight or tomorrow.
Yep, it worked. THANKS SO MUCH for your help. Have spent 2 days on this. Thanks

Kamila