Re: clarification needed in postgresql... + transactions...

On Mon, 19 Jan 2004, dinakar wrote:

> hi all,
>
> i need a clarification in java + postgresql.
>
> currently iam using tomcat 4.0, jdk 1.4, postgresql
> 7.3.x.
>
> i using the below code to fetch data from database,
>
> con =
> DriverManager.getConnection("jdbc:postgresql://192.168.2.51:5432/wsas_test","wsas",
> "wsas");
>
> //con.setAutoCommit(false);
> System.out.println(con.getAutoCommit());
> preStmt = con.prepareStatement("BEGIN;SELECT
> fn_list_allpatients('cursor_name');");
> resultSet = preStmt.executeQuery();
> String strCn = "cursor_name";
> preStmt = con.prepareStatement("FETCH ALL IN \"" +
> strCn + "\";END;");
> resultSet = preStmt.executeQuery();
> //con.setAutoCommit(true);
> while (resultSet.next())
>           {
>     System.out.println(resultSet.getString(1) +
> resultSet.getString("patient_title"));
> }
>
> if i dont use the setautocommit to false and true
> respectively the above code is not working,

Writing BEGIN and END in your own code is frowned upon.  Using
setAutoCommit and commit should be all you need.

> currently iam facing a problem that some transactions
> are ideal even after closing the connection to
> database...

You are probably not closing the connection.  This could be the case of
just a missing close() or poor exception handling.  If you post a self
contained test case someone will likely be able to identify your problem.

Kris Jurka