Stored Procedure returning a result set on the AS/400

[Guest.Visitor]

Hi, I'm trying to create a stored procedure to retrieve data from a DB2 table. The stored procedure is called from a Java program and it should return a result set to the java program. The Java program runs on the client, not on the AS/400. What is the format of a stored procedure for DB2? The examples that I have seen only deal with Oracle databases and those stored procedures don't work for DB2. Thank you for you help!

[Guest.Visitor]

Dorian, The stored procedure on the AS/400 can in fact be any existing pgm. You normal just run the SQL create procedure statement specifying the existing AS/400 pgm. I've noticed that even if the pgm has parameters and they are not complex then the OS/400 sometimes you don't even have to do a create procedure. However, if you want to return a result set then the most common method appears to be to use embedded SQL in RPGLE. But I've just been playing about with a more modern approach via SPL, Stored Procedure Language. Here's a bog standard working example with no error exception handling. Just put it in a source member an do a RUNSQLSTM specifying the source member. The Drop is commented out for this first compile. --Drop Procedure PETER/GetSpl; Create Procedure PETER/GetSpl () Result Set 1 Language SQL Reads SQL Data Main: Begin Declare ListCursor Cursor With Hold For Select ESCODE,ESLIST,ESDESC From MESEL0 Where ESLIST = 'LTERM' For Read Only; Open ListCursor; Set Result Sets Cursor ListCursor; End Main It's pretty cool really.