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

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

I think this is not a problem in the behaviour of the JDBC driver.
I don't know the details in the case of PostgreSQL, but I'm quite sure that
its behaviour follows the behaviour of every other DBMS I know, i.e., when
you
execute a commit, not only updates/deletes/inserts are committed, but also
all the cursor are closed.
So, you are trying to access a cursor already closed with the FETCH
statement and this is most probably the reason because it doesn't work.

Having said this, I think that there are some odd things in your code (the
problem here is that we don't know what your fn_list_allpatients function
shoud do):
- why are you executing the query again? after the first executeQuery you
should have already everything you need in resultSet.
- you are using 'cursor_name' both as a parameter for the function and as
the name for the cursor. Maybe they are two different things, isn't it?

> i need to know will the above code create any problem
> in multiuser application...

Difficult to say: what are you doing in the function? [from a Java point of
view, difficult to say, not knowing the environment in which you are running
the code above and not knowing which is the scope and definition of the
resultSet variable (we can just imagine that this is a java.sql.ResultSet,
but we cannot say, for example, if it is static and you are running that
code inside of a servlet)... however these are not issues which are
pertinent with pgsql-jdbc.]

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

Please, explain better. What do you mean with ideal? (sorry, maybe this is
due to the fact I'm italian and there could be meanings I don't know for an
english word)

Bye
Alessandro Depase