Hi,
We are using Postgresql 7.3.3. We are encountering following
problems when JDBC is used to access Postgresql DB from a Java application
(using JDK1.3.1_06).
My test program does the following:
a. get a DB connection from a connection pool. For ex: con
b. Using Connection obtained in a) execute a function that returns a
Refcursor.
In Java I do the following:
String str = "begin; select sp_test_select (?, 'funcsursor');"
PreparedStatement pstmtTmp = con.prepareStatement(str);
pstmtTmp.setInt(1, 1);
ResultSet rsTmp = pstmtTmp.executeQuery();
PreparedStatement pstmt = conn.prepareStatement("FETCH ALL IN funcursor;
CLOSE funcursor;");
ResultSet rs = pstmt.executeQuery();
// Use rs
.
.
rs.close();
pstmt.close();
rsTmp.close();
pstmtTmp.close();
This is my Postgresql Function that returns a refcursor.
CREATE OR REPLACE FUNCTION sp_test_select (numeric, refcursor) returns
refcursor as '
DECLARE
id_no alias for $1;
refc alias for $2;
BEGIN
OPEN refc FOR SELECT * FROM testtable WHERE idno = id_no;
RETURN refc;
END;
' LANGUAGE 'plpgsql';
c. Using the same Connection object obtained in a) execute a function that
does not return Refcursor but updates some table. This step is carried out
after executing step b). This update is failing. No exception but update
does not take place. If I skip step b) and carry out only steps a) and c)
update works fine.
Am I doing something wrong in step b).
Also where do I get the latest JDBC driver patch for Postgresql 7.3.3.
Any help will be highly appreciated.
Thanks,
Arun Desai.