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