Re: clarification needed in postgresql... + transactions... - Mailing list pgsql-jdbc
From | Alessandro Depase |
---|---|
Subject | Re: clarification needed in postgresql... + transactions... |
Date | |
Msg-id | 007301c3deba$083d8a40$0200a8c0@dedalus1 Whole thread Raw |
In response to | clarification needed in postgresql... + transactions... (dinakar <din_akar@yahoo.com>) |
Responses |
Re: clarification needed in postgresql... + transactions...
(dinakar <din_akar@yahoo.com>)
problem in handling transactions + jdbc (dinakar <din_akar@yahoo.com>) |
List | pgsql-jdbc |
> 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
pgsql-jdbc by date: