I'm trying to get a stored procedure that returns multiple lines.
Something like this should work for a selectable stored procedure


CREATE PROCEDURE getnames
RETURNS (
fname VARCHAR(30),
lname VARCHAR(30)
) AS
BEGIN
FOR SELECT fname, lname
FROM name
INTO :fname, :lname
DO
SUSPEND;
END



But for various reasons I need to call it from a JDBC Callable statement
where the relevant java code looks something like this.
..
CallableStatement statement = connection.prepareCall("{ call getnames() }");
statement.execute();
ResultSet rs=statement.getResultSet();
while (rs.next()) {
System.out.println(rs.getString(1)+" "+rs.getString(2));
}
rs.close();
st.close();
..

However this only returns the first row. I would like it to return all
rows. Is there some way of rewriting the procedure to make this
possible? I suspect I have to remove the suspend and and return a
cursor, but somehow I can't get that to work. Any suggestions?

Regards
Uno Engborg


[Non-text portions of this message have been removed]