Re: problem in handling transactions + jdbc - Mailing list pgsql-jdbc

Don't forget to actually commit the transaction (connection.commit()).
Also you probably need
to handle either a commit() or a rollback() in the finally block,
depending on how your application
works.  Setting autocommit to true doesn't actually issue a commit, I'm
pretty sure.

On Jan 20, 2004, at 6:25 AM, dinakar wrote:

> 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@ejurka.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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>
--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
(740) 587-0114
www.ravensfield.com