CallableStatement, functions and ResultSets - Mailing list pgsql-jdbc

I hope someone can help me.  And thanks ahead of time!

Here is my problem:  I have a table and a function that simply performs
a select * from table.  The function returns a setof table.  I want to
use a CallableStatement and execute the function, but get an exception
stating "Cannot display a value of type RECORD"

Here is my table:

create table state_table
(
     abbreviation char ( 2 ) unique not null,
     name         text       not null
);


Here is the function:

CREATE OR REPLACE FUNCTION state_find () RETURNS SETOF state_table AS '
DECLARE
     _aRow state_table%ROWTYPE;

BEGIN
     FOR _aRow IN SELECT * FROM state_table LOOP
    RETURN NEXT _aRow;
     END LOOP;

     RETURN;
END;
' LANGUAGE 'plpgsql';

Here is the snippet of java code:

     private static void retrieveState ( final ResultSet rs ) throws
Exception
     {
    System.out.println
    (
        "Abbreviation:  <" + rs.getString ( 1 ) +
        ">  Name:  " + rs.getString ( 2 )
    );
     }

     private static void retrieveStates ( final Connection db ) throws
Exception
     {
    final CallableStatement stmt =
        db.prepareCall ( "{call state_find ()}" );

    final ResultSet rs = stmt.executeQuery ();

    while ( rs.next () )
    {
        retrieveState ( rs );
    }

    stmt.close ();
     }

Here is my exception:

java.sql.SQLException: ERROR:  Cannot display a value of type RECORD

         at
org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
         at
org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505)
         at
org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)
         at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
         at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:153)
         at org.jpim.populate.Retrieve.retrieveStates(Retrieve.java:58)
         at org.jpim.populate.Retrieve.main(Retrieve.java:83)

If I change my method as such:

     private static void retrieveStates ( final Connection db ) throws
Exception
     {
    final PreparedStatement stmt =
        db.prepareStatement ( "select * from state_find ()" );
    final ResultSet rs = stmt.executeQuery ();

    while ( rs.next () )
    {
        retrieveState ( rs );
    }

    stmt.close ();
     }

It all works.  I've seen on the archives that this is what one needs to
do.  And that's fine by me.  But my question is this:  should I just use
a PreparedStatement that does "select * from state_table" or have a
function that does the "select * from table" and then use a
PreparedStatement to "select * from function()"

My gut feeling is to use a PreparedStatement with "select * from
state_table"  I guess I'd really like to know which is more efficient?
And also, I'd like to know why one can't use a CallableStatement?

Thanks again!

Scot

--
Scot P. Floess - 27 Lake Royale - Louisburg, NC  27549 - 252-478-8087

Open Source Home Page
--------------------------------------
http://javapim.sourceforge.net
http://jplate.sourceforge.net
http://jwaretechniques.sourceforge.net

Open Source Project Host
-----------------------------------------------
http://sourceforge.net/projects/javapim
http://sourceforge.net/projects/jplate
http://sourceforge.net/projects/jwaretechniques