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

Barry:

Thanks for the response!

Your answer was what I thought to be the case.  So, when is it better to
use a PL/pgSQL function versus building the SQL and using a
PreparedStatement?

Much appreciated!

Scot

Barry Lind wrote:

> 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
>>
>
>
>
>

--
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