postgresql with jdbc and stored procedures (functions): ResultSet

jdbc, psql, stored-functions, stored-procedures

Solution

No need to use a CallableStatement with the `{call ...}` syntax.

Just use a select and a regular `Statement`:

Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("select * from getStat()");
while (rs.next())
{
  System.out.println(rs.getString(1));
  System.out.println(rs.getString(2));
  System.out.println(rs.getInt(3));
}

Problem

I just tried to call a stored function from the server (getStat), that is looking like this: ``` create type stat as (type text, location text, number int); create function getStat() returns setof stat as 'select distinct table1.type, table1.location, table1.number from table1, table2 where table2.finding=10 order by number desc;' language 'sql'; ``` Now here is the jdbc code: ``` CallableStatement callable = null; String storedProc = "{call getStat(?, ?, ?)}"; try { callable = connection.prepareCall(storedProc); callable.registerOutParameter(1, java.sql.Types.VARCHAR); callable.registerOutParameter(2, java.sql.Types.VARCHAR); callable.registerOutParameter(3, java.sql.Types.INTEGER); boolean results = callable.execute(); System.out.println(callable.getString(1)); System.out.println(callable.getString(2)); System.out.println(callable.getInt(3)); while(results){ ResultSet rs = callable.getResultSet(); while(rs.next()){ System.out.println(rs.getString(1)); System.out.println(rs.getString(2)); System.out.println(rs.getInt(3)); } //rs.close(); results = callable.getMoreResults(); } ``` Okay, and now the problem: When I am calling it, it just prints out the first line, of the whole bulk, that should be printend. Yeah, that is clear, because I execute the following code: ``` System.out.println(callable.getString(1)); System.out.println(callable.getString(2)); System.out.println(callable.getInt(3)); ``` But I do the same in the while loop...and nothing more is displayed. Maybe the problem is something obvious, but I am missing that :( Thanks!

Original source