Ive got this loop in a
servlet that needs to do multiple queries on seperate tables in a db.(MySql) I have to do it in seperate queries because a join is impossible with the data I am trying to return. Anyways if you look at the loop you will see the 2 inner queries & their corresponding if statements. If I pull those inner out this thing runs like a wolf & return over 50 friends to this list but when I insert these inner queries I only get 1 line....period! Ive tryed commenting out the inner if statements, naming the ResultSets different things nothing makes it work, except pulling the inner queries & their corresponding if statements completely out............My head is starting to bleed from banging it on the desk! Heres is the method with the offending loop:I had to pull out the HTML because it was freaking out this BB.
private void showFriends(PrintWriter out,
String sesReturn, String userName, Statement stmt) throws SQLException {
////////// headers for columns
[html pulled out here]
String query = "Select friends_list.friend, mastMem.memStatus, mastMem.gender from friends_list, mastMem WHERE friends_list.friend=mastMem.userName AND friends_list.userName='"+userName+"'";
ResultSet rs = stmt.executeQuery(query);
while (rs.next()){
String friendName = rs.getString(1);
int memStatusA = rs.getInt(2);
String memStatus = "";
String gender = "";
switch (memStatusA){
case 1 :
memStatus = "F";
break;
case 2 :
memStatus = "P";
break;
case 3 :
memStatus = "L";
break;
}
int genderA = rs.getInt(3);
switch (genderA){
case 1 :
gender = "M";
break;
case 2 :
gender = "F";
break;
}
String friendNameLong = "";
friendNameLong = friendName + gender + memStatus;
[more html pulled out here]
query = "SELECT MAX(intMail.dateSent) from intMail where fromUserName='"+friendNameLong+"' AND toUserName='"+sesReturn+"'";
String dateFrom = "";
ResultSet rs2 = stmt.executeQuery(query);
if (rs2.next()){
if (rs2.getString(1) == null){
dateFrom = "This member has never sent you mail";
}
else{
dateFrom = rs2.getString(1);
}
}
[one line html here]
query = "SELECT MAX(intMail.dateSent) from intMail where fromUserName='"+sesReturn+"' AND toUserName='"+friendNameLong+"'";
String dateTo = "";
ResultSet rs3 = stmt.executeQuery(query);
if (rs3.next()){
if (rs3.getString(1) == null){
dateTo = "You have never sent email to this member";
}
else{
dateTo = rs3.getString(1);
}
}
}
}
Anyone care a guess here? I dont know if its the MySql (im getting no errors) or the loop......are the rs's expiring somhow from the first query? Thanks for any help you can give here.
[This message has been edited by DC Dalton (edited August 06, 2001).]