plpgsql refcursor
От | Franco Bruno Borghesi |
---|---|
Тема | plpgsql refcursor |
Дата | |
Msg-id | 1071518799.1909.16.camel@taz.oficina обсуждение исходный текст |
Ответы |
Re: plpgsql refcursor
|
Список | pgsql-jdbc |
Hi all.
I'm having problems obtaining resultsets from a pgsql function, I've read the jdbc documentation, and read similar messages in the lists, but I still cannot make it work. Perhaps someone can see what's wrong with my code, because I can't find it
my plgpsql function is:
CREATE OR REPLACE FUNCTION usuarios_load(INTEGER) RETURNS REFCURSOR AS '
DECLARE
result REFCURSOR;
BEGIN
OPEN result FOR SELECT * FROM usuarios WHERE id=$1;
RAISE NOTICE ''usuarios_load() -> %'', result;
RETURN result;
END;
' LANGUAGE 'plpgsql';
my java code is:
conn.setAutoCommit(false);
cstmt=conn.prepareCall("{ ? = call usuarios_load( ? ) }");
cstmt.registerOutParameter(1, Types.OTHER);
cstmt.setInt(2, 33);
cstmt.execute();
rs=(ResultSet)stmt.getObject(1);
while (rs.next()) {
System.out.println(rs.getString(0)); << throws exception
}
the logs says:
2003-12-15 16:54:35 LOG: statement: set datestyle to 'ISO'; select version(), case when pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else getdatabaseencoding() end;
2003-12-15 16:54:35 LOG: statement: set client_encoding = 'UNICODE'
2003-12-15 16:54:35 LOG: connection received: host=192.168.1.162 port=49948
2003-12-15 16:54:35 LOG: connection authorized: user=sapiens database=franco
2003-12-15 16:54:35 LOG: statement: begin;
2003-12-15 16:54:35 LOG: statement: select * from usuarios_load( 33 ) as result;
2003-12-15 16:54:35 LOG: statement: SELECT * FROM usuarios WHERE id= $1
CONTEXT: PL/pgSQL function "usuarios_load" line 5 at open
2003-12-15 16:54:35 NOTICE: usuarios_load() -> <unnamed portal 1>
2003-12-15 16:54:35 LOG: statement: SELECT $1
CONTEXT: PL/pgSQL function "usuarios_load" line 14 at return
2003-12-15 16:54:35 LOG: statement: SELECT typname FROM pg_catalog.pg_type WHERE oid = 1790
2003-12-15 16:54:35 LOG: statement: FETCH ALL IN "<unnamed portal 1>"; << this is where exception is thrown
2003-12-15 16:54:35 LOG: statement: commit;begin;
2003-12-15 16:54:35 LOG: unexpected EOF on client connection
thanks in advance!
I'm having problems obtaining resultsets from a pgsql function, I've read the jdbc documentation, and read similar messages in the lists, but I still cannot make it work. Perhaps someone can see what's wrong with my code, because I can't find it

my plgpsql function is:
CREATE OR REPLACE FUNCTION usuarios_load(INTEGER) RETURNS REFCURSOR AS '
DECLARE
result REFCURSOR;
BEGIN
OPEN result FOR SELECT * FROM usuarios WHERE id=$1;
RAISE NOTICE ''usuarios_load() -> %'', result;
RETURN result;
END;
' LANGUAGE 'plpgsql';
my java code is:
conn.setAutoCommit(false);
cstmt=conn.prepareCall("{ ? = call usuarios_load( ? ) }");
cstmt.registerOutParameter(1, Types.OTHER);
cstmt.setInt(2, 33);
cstmt.execute();
rs=(ResultSet)stmt.getObject(1);
while (rs.next()) {
System.out.println(rs.getString(0)); << throws exception
}
the logs says:
2003-12-15 16:54:35 LOG: statement: set datestyle to 'ISO'; select version(), case when pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else getdatabaseencoding() end;
2003-12-15 16:54:35 LOG: statement: set client_encoding = 'UNICODE'
2003-12-15 16:54:35 LOG: connection received: host=192.168.1.162 port=49948
2003-12-15 16:54:35 LOG: connection authorized: user=sapiens database=franco
2003-12-15 16:54:35 LOG: statement: begin;
2003-12-15 16:54:35 LOG: statement: select * from usuarios_load( 33 ) as result;
2003-12-15 16:54:35 LOG: statement: SELECT * FROM usuarios WHERE id= $1
CONTEXT: PL/pgSQL function "usuarios_load" line 5 at open
2003-12-15 16:54:35 NOTICE: usuarios_load() -> <unnamed portal 1>
2003-12-15 16:54:35 LOG: statement: SELECT $1
CONTEXT: PL/pgSQL function "usuarios_load" line 14 at return
2003-12-15 16:54:35 LOG: statement: SELECT typname FROM pg_catalog.pg_type WHERE oid = 1790
2003-12-15 16:54:35 LOG: statement: FETCH ALL IN "<unnamed portal 1>"; << this is where exception is thrown
2003-12-15 16:54:35 LOG: statement: commit;begin;
2003-12-15 16:54:35 LOG: unexpected EOF on client connection
thanks in advance!
Вложения
В списке pgsql-jdbc по дате отправления: