Why not set the second query up in it's own method and send the necessary
results from the first query to it as parameters and return the results of
the second query.  You can use the same connection but the statements and
resultSets will be separate objects.  This would also make it much easier to
ensure everything gets closed.


Greg


-----Original Message-----
From: Shashank Arora [mailto:niceguyleo@i...]
Sent: Wednesday, September 11, 2002 12:57 PM
To: Servlets
Subject: [servlets] Re: Commit problems when updating databse using
servlets.!


Hey Giovanni :)
thanks 4 the info.i did as u said and i really hope nothin weird happens
now. i certainly hope so.
Thanks 4 ur immenese help anyways

There is one more prob if u can help me out, i m creating a result set to
retrive the results of a query and before closing the result se i've got
to invoke another result set on the same statement as the display is
generated on the fly so, essentially the results of the second query takes
in  a parameter the 1st query generates and since there is a loop workin,
as i've ot to display many such resuls, all of which are dynamic , i 've
gt to use one result set inside other.
But when i do it an error is generated by the server  (tomcat 4.0.3)
as "hstmt busy with the results of another statement"..so the result of
the senond result set interferes with the first one, so wht i am doin is
creating another connection object there and using it to get the results
as using just another statament doesn't seem to solve the issue..
i hope my prob is clear and i def hope that u'll be able to give me a
solution.

Thanks
Shashank :)
> At 11:06 11/09/2002 +0000, you wrote:
here is a smple 'pattern'
try{
first db operation
} catch (Exception e) {
  rollback
}
you could also write code like this
try {
first db operation
try {
//non db operation
} catch (AnException e) {
other db operation
}
} catch (Exception e) {
  rollback
}
and so on , you must try and then decide, it depends from your application
Giovanni
>Hey :)
>
>how will i know which exception may be needing rollback and whicn does
>not?? i mean there can be so many exceptions...wht if i perform rolback
>only when i catch like Exception...will it suffice 4 every condition??
>do let me know
>
>Thanks
>Shashank :)
>
>
> > At 10:31 11/09/2002 +0000, you wrote:
>Hi Shashank
>my answer is pretty simple
>you must catch every Exception that imposes a rollback
>if after modifying the db data you got non sqlexception from which you
>can't recover you must rollback, so my advice is to catch all the
>exceptions
>you can have multiple catch and perform rollback only in some of them.
>Giovanni
>
> >Hi Giovanni :)
> >
> >thanks budydy, ya u were right i forgot to do that in my code...i did it
> >at other olaces but i din't do it here...but one thing that i wanna
> >ask ..do i catch general Exception or SQLException..??actually i have
> >caught SQLException and perfomed the rollback there..does Exception
class
> >also account for SQLExections.?? in that case i can only specify
Exception
> >n perform the rolback there..prob then i needn't catch SQL Exceptions
> >specifically. do let me know if u can..
> >thanks
> >Shashank :)
> >
> > > At 10:00 11/09/2002 +0000, you wrote:
> >Shashank
> >   check this
> >at the end of the servlet you must either commit or rollback
> >usually this leads to this code
> >cn=pool.get(); //if you do pooling connections
> >try {
> >//DB operations
> >cn.commit();
> >}catch (Exception e){
> >    cn.rollback();
> >} finally {
> >    pool.release(cn); //if you do pooling connections
> >}
> >if you don't have the catch it may be the case you got an exception and
> >you
> >don't rollback, the next servlet is locked by the preceding one error.
> >hope it helps,
> >Giovanni
> >
> > >hi Giovanni :)
> > >
> > >i did look into the code but there seems no condition for a deadlock.
> > >actually all i ma doin is that i am inserting some data in 2 table and
> > >then updating the counter values in the other table accordingly, and
> > >after then i am accessing the prev table (wherein i inserted the
values)
> > >for displayin the results. So it doesn't seem to be creatin the
deadlock
> > >but doono wht is happening...
> > >
> > >Shashank :)
> > >
> > >
> > > > At 08:12 11/09/2002 +0000, you wrote:
> > >it seems a  locking problem, this can happen if another application or
> > >another thread is updating the same data.
> > >the worse situation is deadlock, in this case all the processes are
> >locked.
> > >here is an example
> > >process A updates table T1 row n
> > >process B updates table T2 row k
> > >process A updates table T2 row k (and waits since B did not issue a
> >commit)
> > >process B updates table T1 row n (deadlock the processes are locking
>each
> > >other waiting forever)
> > >hope it helps,
> > >          Giovanni
> > > >Hey Giovanni :)
> > > >
> > > >Thanks a lot 4 ur tip. I;ve used it in my code and it's workin fine,
>but
> > > >sometime it does behave strangely when there are more than 1
updates.
>I
> > > >mean the table that i am accessing seems to be hang up forever.
> >conection
> > > >seems busy there only. It does happen sometimes. Have u any idea why
> >this
> > > >is happening.??
> > > >
> > > >Thanks anyways,
> > > >Shashank :)
> > > >
> > > >
> > > > > At 12:16 10/09/2002 +0000, you wrote:
> > > >you must commit only after your operations execute successfully and
> > > >rollback the first time one fails.
> > > >Giovanni
> > > > >Hi :)
> > > > >
> > > > >i've developed an appliction using Servlets and the server i am
>using
> >is
> > > > >SQL server 2000. I am using the function setAutoCommit(false) to
>make
> > >the
> > > > >commit feature of the databse non automatic.
> > > > >The problem i am having is that i have to update the database more
> >than
> > > > >once in a servlet. so do i need to specify commit after every
update
> >or
> > > > >not?? in that case if i am performing 4 updates what if the 1st 3
> > >updates
> > > > >are successfull  i.e. are commited but the 4th one raises an
> >exception,
> > >so
> > > > >how will i then rollback the database., i mean to the database
that
>is
> > > > >before all the 4 updates, i.e. how do i create checkpoints in my
> >servlet
> > > > >code??? i guess you are getting my point, eihter all the updates
>shud
> >be
> > > > >successful none be successfull.
> > > > >Plz somebody help me out of this, i really am at the end of
> >development
> > >of
> > > > >this application and this is really proving really horible for me.
> > > > >
> > > > >Thanks,
> > > > >Shashank Arora
> > > >
> > > >
> > > >----------------------------------------
> > > >Giovanni Cuccu
> > > >Sw Engineer@d...
> > > >Dianoema S.p.A.
> > > >Via de' Carracci 93 40131 Bologna
> > > >Tel: 051-4193911
> > > >e-mail:gcuccu@d...
> > > >----------------------------------------
> > > >
> > > >
> > > >
> > >
> > >
> > >----------------------------------------
> > >Giovanni Cuccu
> > >Sw Engineer@d...
> > >Dianoema S.p.A.
> > >Via de' Carracci 93 40131 Bologna
> > >Tel: 051-4193911
> > >e-mail:gcuccu@d...
> > >----------------------------------------
> > >
> > >
> > >
> >
> >
> >----------------------------------------
> >Giovanni Cuccu
> >Sw Engineer@d...
> >Dianoema S.p.A.
> >Via de' Carracci 93 40131 Bologna
> >Tel: 051-4193911
> >e-mail:gcuccu@d...
> >----------------------------------------
> >
> >
> >
>
>
>----------------------------------------
>Giovanni Cuccu
>Sw Engineer@d...
>Dianoema S.p.A.
>Via de' Carracci 93 40131 Bologna
>Tel: 051-4193911
>e-mail:gcuccu@d...
>----------------------------------------
>
>
>


----------------------------------------
Giovanni Cuccu
Sw Engineer@d...
Dianoema S.p.A.
Via de' Carracci 93 40131 Bologna
Tel: 051-4193911
e-mail:gcuccu@d...
----------------------------------------