java.sql.SQLException: Closed Statement: next

[Farakh khan]

Please check the following code its giving the error "java.sql.SQLException: Closed Statement: next"

        if ((cnn==null)||cnn.isClosed()){
cnn=db.getOracleConnection();
}
 
String mailmsg=msg1+msg2+msg3; 
String emails="",category="";
 
String  query5="select e_id from emailcategory";
String query14="select email from email where category=? and active=1";
 
int count=0; 
PreparedStatement ps5=cnn.prepareStatement(query5);
rs=ps5.executeQuery();
while(rs.next()){
category=rs.getString(1);
count+=1;         
           PreparedStatement ps1=cnn.prepareStatement(query14);
           ps1.setString(1, category);
           rs1=ps1.executeQuery();
           while(rs1.next()){
           emails=rs1.getString(1);         
 
if(emails==null || emails.equals("")){
emails="";
}else{
sendEmail.send("[e-mail]", emails, subject, mailmsg);
}
           }
           ps1.close();
 
out.println("<br><br><br><center><span class=gull>"+
"Successfully Sent to "+count+" category(s)<br>"+
"Wait Please for next category.....</span></center>");

Thread.sleep(14000);
 
}
ps5.close();

out.println("<br><br><br><br><br><center><span class=gull>Successfully Sent</span></center>");

[ October 16, 2008: Message edited by: Farakh khan ]

[sridhar Ranganathan]

Hi,
I have tested your above code with Ms-Access,Its working fine.
If your getting the error still,you can try this manner.
<CODE>

String id[]=new String[];
int cnt=0;
while(rs.next())
{
cnt++;
id[cnt]=rs.getString(1);
}

for(int i=0;i<id.length();i++)
{
PreparedStatement ps1=conn.prepareStatement(query14);
ps1.setString(1, id[i]);
rs1=ps1.executeQuery();

while(rs1.next())
{
emails=rs1.getString(1);
if(emails==null || emails.equals(""))
{
emails="";

}else
{
sendEmail.send("[e-mail]", emails, subject, mailmsg); }
}
ps1.close();
}
</CODE>

Thanks,
Sridhar.R

[Farakh khan]

I am trying to get category in first resultset and in next resultset trying to get this category emails.

regards
[ October 16, 2008: Message edited by: Farakh khan ]

[Rob Spoor]

Oops, sent to the wrong forum per accident. Can someone move this to JDBC please?

[Farakh khan]

Originally posted by Rob Prime:
Oops, sent to the wrong forum per accident. Can someone move this to JDBC please?

may be

[Campbell Ritchie]

Originally posted by Rob Prime:
Oops, sent to the wrong forum per accident. Can someone move this to JDBC please?

Only for you, Rob.

[Farakh khan]

Can anybody please help me instead of frequently changing the forum of my question?

Regards

[Paul Clapham]

Help yourself and us by providing useful details like the stack trace and by telling us which line of code throws the exception.

[Farakh khan]

Thanks for your reply.

The information that I provided in my first post is not enough?

ps1.close(); is creating problem by throwing error

Thanks again & kind regards

[Brian Mozhdehi]

Im pretty sure your issue is that you are in a result set, looping and creating new statement(s) and new result set(s) within the loop. Meaning, within rs.next(). Not to be "unhelpful" I didnt look super closely, but I think thats your issue. You cant do that.

If you need to retreive data, then use this data to retrieve something else in a loop, create two sections of code, one that retreives the criteria and stores it in say an ArrayList, then close and clean up your statements and result sets and start a new loop thru the ArrayList doing your second set of SQL. Be sure to clean up as you loop around each time.

[Farakh khan]

Thanks a lot