Re: CallableStatement, functions and ResultSets - Mailing list pgsql-jdbc

Scot,

You should definitely use the PreparedStatement, that will be much more
efficient.  Going through the stored function just adds a lot of
overhead without providing any added value.

As for the CallableStatement problem.  Off the top of my head I am not
sure why that isn't working.

--Barry

Scot P. Floess wrote:
> 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
>