• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Why do these queries break this loop????? Im lost!

 
Ranch Hand
Posts: 287
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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).]
 
Ranch Hand
Posts: 254
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It's probably because you are using the same statement object for all queries in code above
create new statement object for every query and make sure to close all objects
when you are done
//your code
Statement stmt = myconnobj.createStatement();
ResultSet rs = stmt.executeQuery(query);
//blah
..
Statement stmt2 = myconnobj.createStatement();
ResultSet rs2 = stmt2.executeQuery(query);
//more blah
...
stmt2.close();
//more blah blah
...
Statement stmt3 = myconnobj.createStatement();
ResultSet rs3 = stmt.executeQuery(query);
....
stmt3.close();

//more code

stmt.close();
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In the inner loops, it might be faster to use PreparedStatements instead of statements. Create it once, then reuse. you'd be surprised of the performance gained.
Jamie
 
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
One warning about PreparedStatements - they *may* speed things up, but they may not. Two reasons I can think of:
1) your statement gets "prepared" over on the database, database is heavily used, your prepared statement gets flushed before you get a chance to use it (this would actually slow down preformance, I guess)
2) some drivers (e.g. Sybase jConnect) actually ignore your request to prepare the statement by default (I guess the assumption is that you aren't going to reuse it enough to make it worthwhile to pre-compile the statement). For the jConnect drivers you have to create the Connection with a property set that states you really want to pre-compile. So in this case, if you used the default PreparedStatement, you'd find no difference.
Having said that, I agree that you should use them. Just be aware.
reply
    Bookmark Topic Watch Topic
  • New Topic