problem in handling transactions + jdbc

Hi Jurka,

let me explain my problem once again..

currently i am using Tomcat 4.x, Postgresql 7.3.x,
Linux OS, JSP, Java for buliding a web application.

i received a mail from my client stating that : they
are facing some problem in postgres database server.
when ever the database grows in size, postgres is
trying to use Vacuum database option to shrink it.

while do so, the vacuum process would be unable to
free up dead rows, because they'd still be accessible
to some idle transactions...

whenever my application throws any exception the
transactions are left unhandled so like this some
transactions are idle even after some 5 to 6 days...

now the problem comes to the server memory. it is
occupying lot of memory..

---previously i was using the below code.....

preStmt = con.prepareStatement("BEGIN; SELECT
fn_list_patientsaudit('" + pstrPatientName + "'," +
intNoRecords + "," + intStart + ",'cursor_name');");

resultSet = preStmt.executeQuery();

String strCn = "cursor_name" ;

preStmt = con.prepareStatement("FETCH ALL IN \"" +
strCn + "\"; END;");

resultSet = preStmt.executeQuery();

while (resultSet.next()) {
---------
------
}

instead of getting the cursor name from the postgres,
i am giving my own name to the cursor.

--client reports the problem... now i have changed my
code to below...

con.setAutoCommit(false);
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();
while (resultSet.next())
{
-----
-----
}

con.setAutoCommit(true);

iam using finally block to close my connection and
connction related objects...

will this work without any problem ?. i mean will this
work for 500 users simul. without any problem...

please advice...

thanks,
dinakar

--- Kris Jurka <books(at)ejurka(dot)com> wrote:
>
>
> 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
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

__________________________________
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus