CallableStatement, functions and ResultSets
От | Scot P. Floess |
---|---|
Тема | CallableStatement, functions and ResultSets |
Дата | |
Msg-id | 3E6C9E28.8010502@mindspring.com обсуждение исходный текст |
Ответы |
Re: CallableStatement, functions and ResultSets
|
Список | 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
В списке pgsql-jdbc по дате отправления: