According to the error "Connection is busy with results for another hstmt"
you need another connection, not just another statement.
(BTW, I would highly recommend using a connection pool.)
Greg
-----Original Message-----
From: Shashank Arora [mailto:niceguyleo@i...]
Sent: Friday, September 13, 2002 1:11 PM
To: Servlets
Subject: [servlets] Re: Commit problems when updating databse using
servlets.!
Hey :)
I do close all the resources as u said when they aren't needed, but the
problem still remains the same, i fact my code is more or less on the
format of yours. In your code you declared a statement 1, then the result
set 1, then loop starts and then declared statement 2 and result set 2
and then u closed the result set 2 and stmt 2 , but u din't close the
result set 1 and the stmt 1 as u needed it for the nest time th loop
operates,. right. that is exactly wht i am doing, so u see we can't close
the statement we are using, if u look into the code u'll see that.
So the exception still is raised. and if i close the prev stmt1 before
using result set 2 then as it's obvious the loop funtions only once and
then throws an error.
I guess this is really a strange prob. may be u r using thin driver and i
am using JDBC:ODBC bridge driver, so that may be the difference.
Thanks anyways,
Shashank :)
> At 11:34 13/09/2002 +0000, you wrote:
Hi,
try to follow the rule of closing the resource after you don't
need it anymore
ie
rs=st.createstatement(...);
//use of re
rs.close
in a similar way you must handle statements
Giovanni
>Hey :)
>
>thanks 4 the useful tip...but i m really sorry to say that inspite of
>using 2 diff statements i am still getting the exception. so i am sendin
>you the code as well this time, i know this is a bit troublesome but i
>would really be glad if u can help me out. I've also specified in the code
>as to where is the excetion getting raised. i've truncated the catch
>statements in this code. The second result set raises the exception. Do
>look into the matter buddy.
>
>Anyways, which Server are you using.i m using Tomcat 4.0.3 and the
>database is SQL Server 2000, do tell me ur specifications.
>
>i guess u can really help me.
>
>Thanks,
>Shashank :)
>
>
>CODE STARTS HERE.......
>
>
>con=cms.DBConnection.makeConnection();
>con.setAutoCommit(false);
>stmt=con.createStatement();
>
>rs=stmt.executeQuery("Select company_code,company_name from COMPANY where
>user_id='"+uName+"'");
>while(rs.next()){
> company_code=rs.getInt("company_code");
> company_name=rs.getString("company_name");
>}
>rs.close();
>int if_found=0;
>if(ENT_CODE==2){
>rs=stmt.executeQuery("select r_admin from ACCESSINFO where
>user_id='"+uName+"'");
> if(rs.next()){
> if_found=1;
> }
>}
>
>if(if_found==0){
>rs=stmt.executeQuery("Select CD.complaint_no as
>complaint_no,relocation_no,computer_brand,room_no,complaint_date,assigned_
d
>ate,engineer_code from COMPLAINTDETAILS as CD,COMPLAINTSTATUS AS CS where
>CD.complaint_no=CS.complaint_no and CS.status='pending' and
>company_code="+company_code+" order by
>engineer_code,assigned_date,room_no,CD.complaint_no");
>}
>else{
> rs=stmt.executeQuery("Select CD.complaint_no as
>complaint_no,relocation_no,computer_brand,room_no,complaint_date,company_c
o
>de,assigned_date,engineer_code from COMPLAINTDETAILS as CD,COMPLAINTSTATUS
>AS CS where CD.complaint_no=CS.complaint_no and CS.status='pending' order
>by company_code,engineer_code,assigned_date,room_no,CD.complaint_no");
>}
>
>boolean done=false;
>done=rs.next();
>if(!done)
>{
> out.println("<html><head></head><body><center><br><br><br><font
>face=verdana color=red size=2><b>Sorry, no Complaints have been taken by
>any of the Engineers.</b></center></body></font></html>");
>rs.close();
>return;
>}
>out.println("</head><body>");
>out.println("<center><table bgcolor=brown cellpadding=4 cellspacing=1>");
>out.println("<tr><td align=center><font color=White face=verdana
>size=2><b>List of Complaints Assigned specifically to the
>Engineers</b></td></tr></table></center>");
>if(if_found==0)
>out.println("<br><font color=Black size=2 face=verdana><b>Please attend to
>the the Complaints assigned to you and Update the
status.</font></b><br>");
>
>else
>out.println("<br><font color=Black size=2 face=verdana><b>The Following
>Complaints assigned to the specified engineers have not been handled
>yet.</font></b><br>");
>out.println("<br><table bgcolor=white width=100% cellpadding=2
>cellspacing=1 border=0><tr>");
>out.println("<td bgcolor=gray><font color=White size=2
>face=verdana><b>Complaint No</font></b></td>");
>out.println("<td bgcolor=gray><font color=White size=2
>face=verdana><b>Machine ID</font></b></td>");
>out.println("<td bgcolor=gray><font color=White size=2
>face=verdana><b>Brand Name</font></b></td>");
>out.println("<td bgcolor=gray><font color=White size=2
>face=verdana><b>Room No</font></b></td>");
>out.println("<td bgcolor=gray><font color=White size=2
>face=verdana><b>Registering Date</font></b></td>");
>out.println("<td bgcolor=gray><font color=White size=2
>face=verdana><b>Assigning Date</font></b></td>");
>out.println("<td bgcolor=gray><font color=White size=2
>face=verdana><b>Engineer Assigned</font></b></td>");
>if(if_found==1)
>out.println("<td bgcolor=gray><font color=White size=2
>face=verdana><b>Company Name</font></b></td></tr>");
>
>int cnt=1;
>String clr="red";
>
>while(done){
> cnt++;
>if((cnt%2)==0)
>{
> clr="#e9e9e9";
>}
>else
>{
>clr="#dddddd";
>}
>out.println("<tr bgcolor="+clr+">");
>int cno=rs.getInt("complaint_no");
>out.println("<td ><font color=Brown size=2
>face=verdana>"+cno+"</font></td>");
>out.println("<td ><font color=Brown size=2 face=verdana>"+rs.getString
>("relocation_no")+"</font></td>");
>out.println("<td ><font color=Brown size=2 face=verdana>"+rs.getString
>("computer_brand")+"</font></b></td>");
>out.println("<td ><font color=Brown size=2 face=verdana>"+rs.getInt
>("room_no")+"</font></td>");
>
>DateFormat fmt = DateFormat.getDateInstance(DateFormat.SHORT);
>String regDate = fmt.format(rs.getDate("complaint_date"));
>out.println("<td ><font color=Brown face=verdana
>size=2>"+regDate+"</font></td>");
>String assigned_date = fmt.format(rs.getDate("assigned_date"));
>
>out.println("<td ><font color=Brown size=2
>face=verdana>"+assigned_date+"</font></td>");
>int engineer_code=rs.getInt("engineer_code");
>
>stmt1=con.createStatement();
>String engineer_name="";
>
>
>rs1=stmt1.executeQuery("Select engineer_name,company_code from ENGINEER
>where engineer_code="+engineer_code);
>
>/*the error comes here (it says E: java.sql.SQLException: [Microsoft][ODBC
>SQL Server Driver]Connection is busy with results for another hstmt)*/
>
>while(rs1.next()){
>engineer_name=rs1.getString("engineer_name");
>company_code=rs1.getInt("company_code");
>}
>out.println("<td ><font color=Blue size=2
>face=verdana><b>"+engineer_name+"</font></b></td>");
>rs1.close();
>
>if(if_found==1){
>rs1=stmt1.executeQuery("select company_name from COMPANY where
>company_code="+company_code);
>while(rs1.next()){
>
> company_name=rs1.getString("company_name");
>
>}
>rs1.close();
>}
>
>if(if_found==1)
>out.println("<td ><font color=Blue size=2
>face=verdana>"+company_name+"</font></td>");
>if(if_found==0)
>out.println("<td bgcolor=white><font color=Blue face=verdana size=2><a
>href=\"../jsps/CMSHandleComplaint.jsp?CNO="+cno+"&ID="+session.getId()
>+"&ENG_NAME="+engineer_name+"&ENTRY_CODE=1\" OnMouseOver=\"window.status
>('Click here to Update the Status of the Selected Complaint..');return
>true;\" OnMouseOut=\"window.status=('');return true;\"
>OnFocus=\"window.status=('Click here to Update the Status of the Selected
>Complaint..');return true;\" OnBlur=\"window.status=('');return true;\">
>[Update]</a></font></td>");
>
>out.println("<td bgcolor=white><font face=verdana color=Brown size=2><a
>href=\" cms.CMSGetComplaintDesc?CNO="+cno+"\" target=\"_new\">[Details]
></a></font></td>");
>
>out.println("</tr>");
>
>done=rs.next();
>}
>
>out.println("</table></body></html>");
>rs.close();
>System.out.println("you r outside rs2");
>}
>
>
>CODE ENDS HERE............
>
>
>
>
>
> > At 14:45 11/09/2002 +0000, you wrote:
>I did some tries and on oracle it works well, I suspect you are reusing
>the
>same statement instead of getting a new one for the nested resultset
>here is my working example.
>
>import java.sql.ResultSet;
>import java.sql.Connection;
>import java.sql.DriverManager;
>import java.sql.Statement;
>
>public class ProvaResultSet {
> public ProvaResultSet() {
> }
>
> public static void main(String[] args)throws Exception {
> ProvaResultSet provaResultSet = new ProvaResultSet();
> Class.forName("oracle.jdbc.OracleDriver");
> Connection conn = DriverManager.getConnection
>
>("jdbc:oracle:thin:@server:1521:test", "user", "password");
> Statement st=conn.createStatement();
> ResultSet rs=st.executeQuery("Select * from recent_document
where
>id_doc=1316");
> while (rs.next()) {
> Statement st1=conn.createStatement();
> ResultSet rs1=st1.executeQuery("Select * from doctor where
>id_doc=" + rs.getString("ID_DOC"));
> if (rs1.next()) {
> System.out.println(rs1.getString("ID_DOC"));
> }
> rs1.close();
> st1.close();
> }
> rs.close();
> st.close();
> conn.close();
> }
>}
>hope it helps,
> Giovanni
> >Hi Giovanni :)
> >I am using the JDBC-ODBC bridge driver and the database i am using is
SQL
> >Server 2000, so do try and look into the matter and let me know. i can
> >send u my code if u want buddy.
> >
> >so i'll be hopin that u sought out this.
> >
> >Thanks again,
> >Shashank :)
> >
> > > At 12:57 11/09/2002 +0000, you wrote:
> >It' s a very strange problem, can you tell me what driver and database
are
> >you using?
> >In the meanwhile I'll do some tries with my Oracle DB
> >Giovanni
> > >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
> > > > > > > >---
> > > > > > > >Change your mail options at http://p2p.wrox.com/manager.asp
or
> > > > > > >
> > > > > > >
> > > > > > >----------------------------------------
> > > > > > >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...
> > >----------------------------------------
> > >
> > >
> > >
> >
> >
> >----------------------------------------
> >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...
----------------------------------------
View Cart
