AS/400 v4r4 stored procedure is NOT returning result set to VB 
Author Message
 AS/400 v4r4 stored procedure is NOT returning result set to VB

We are on AS/400 v4r4 (SF99104) and I wrote a small stored procedure
to return a result set to a VB program. We have DB2 Connect V7.2 on
NT.

CREATE PROCEDURE SAHUH.TESTPROC2()  
   RESULT SETS 1              
   LANGUAGE SQL                

BEGIN                          
 DECLARE C1 CURSOR FOR
   SELECT EMPNO                
   FROM SAHUH.EMPLOYEE        
   ORDER BY EMPNO;            
 OPEN C1;          
 SET RESULT SETS CURSOR C1;            
END      

But when I call this stored procedure, I don't get any data back. I
called using AUTOCOMMIT=0 to make sure that there is no commit. When
we debug the stored procedure, we see message in the job log as 1
RESULT SETS PROCESSED but the number of records is 0. It should be 5.

When I ran the stored procedure using AS/400 operations navigator
using CALL SAHUH.TESTPROC2, I get 5 records.

Did anybody run into similar problem ? Any help will be really
appreciated.

Thanks
Hitendra



Tue, 13 Jan 2004 03:05:03 GMT
 AS/400 v4r4 stored procedure is NOT returning result set to VB

Quote:

> We are on AS/400 v4r4 (SF99104) and I wrote a small stored procedure
> to return a result set to a VB program. We have DB2 Connect V7.2 on
> NT.

> CREATE PROCEDURE SAHUH.TESTPROC2()
>    RESULT SETS 1
>    LANGUAGE SQL

> BEGIN
>  DECLARE C1 CURSOR FOR
>    SELECT EMPNO
>    FROM SAHUH.EMPLOYEE
>    ORDER BY EMPNO;
>  OPEN C1;
>  SET RESULT SETS CURSOR C1;
> END

> But when I call this stored procedure, I don't get any data back. I
> called using AUTOCOMMIT=0 to make sure that there is no commit. When
> we debug the stored procedure, we see message in the job log as 1
> RESULT SETS PROCESSED but the number of records is 0. It should be 5.

> When I ran the stored procedure using AS/400 operations navigator
> using CALL SAHUH.TESTPROC2, I get 5 records.

> Did anybody run into similar problem ? Any help will be really
> appreciated.

OS/400 communicates with DB2 Connect via DRDA protocols.  Ops Navigator
is part of Client Access, which communicates with clients using other
protocols.  The server-side DRDA protocol support for result sets was
added to OS/400 in V5R1.  Could you perhaps use the Client Access ODBC
driver with your VB program, at least until a V5R1 upgrade is possible?
(just a thought)

--
Karl Hanson



Tue, 13 Jan 2004 21:30:37 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. DB2/400 Stored Procedure w/ADO--return result set

2. How not to return multiple result sets from a stored procedure

3. Stored Procedures from Java/VB returning Result Sets(via ADO)

4. returning result sets from stored procedures

5. Returning result sets in oracle stored procedure (OLEDB)

6. return multiple result sets from a stored procedure

7. Can't update result set returned from stored procedure using RDO

8. Returning result set from function to stored procedure is very slooooooooow

9. best way to return a paged result set from a stored procedure

10. Returning a result set from a Stored Procedure

11. returning result sets from stored procedures

12. Stored Procedure/Multiple Return Results/Set Statement


 
Powered by phpBB® Forum Software