Re: Postgres JDBC

Arun,

Here are a couple of things I would recommend:

1) Don't include transaction control code in your sql. Use the JDBC
methods to do this (i.e. setAutocommit()).
2) Don't include more than one statement in a sql string. (i.e. "FETCH
ALL IN funcursor; CLOSE funcursor;", should be two different calls
"FETCH ALL IN funcursor" and "CLOSE funcursor"). This may work now, but
likely won't work in the future.
3) Turn on SQL statement tracing in the server (set log_statement=ture
in postgresql.conf file). This will allow you to see the exact
statements being sent to the server from the driver and will likely help
you identifiy how/why this isn't working.
4) The latest driver builds can be found at jdbc.postgresql.org
5) If you have any further questions, please send them to the
pgsql-jdbc(at)postgresql(dot)org mail list. That way others can contribute and
everyone benefits from the exchange in information.

thanks,
--Barry

Arun Desai wrote:
> Barry,
> 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.
>