Re: Postgresql JDBC question

On 09/07/2003 11:27 Arun Desai wrote:
> 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');"
Why the begin? To use transactions via JDBC you should
setAutoCommit(false) on the connection before recreating the statement.
This is basic JDBC. Refer to the tutorials at java.sun.com.

> 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();

And here you should be commiting the transaction with pstmtTmp.commit().

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

Obviously it would.

> Am I doing something wrong in step b).

Yes. You're starting a transaction and not committing it.

HTH

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+