Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

ResultSet problems

 
Ronald Whalen
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Got a little problem. I use the following method call to access a MySQL DB. Prior to executing this method, other DB calls work. I printout connection vars,statement var to see if there is anytthing allocated and they are. I can access this table just prior to set the user_id. I want to simply retrieve it based on username. I always get an exception on the line where the query is done. I can manually enter the query string into the DB and I get the results I desire. It seems like a connection is there and so is the statement. I would appreciate any help.

Thanks.
Ronald
public int getUserID(String userName) throws SQLException {
//ResultSet resultSet=null;
int tempUserID;
try{
System.out.println("getUserID");
Statement temp=connection.createStatement();
System.out.println("Connection = "+connection);
String sqlString="SELECT USER_ID FROM USER WHERE USER_NAME=\""+userName+"\"";
System.out.println("Statement= "+s);
System.out.println(sqlString);

ResultSet resultSet1=temp.executeQuery(sqlString);

System.out.println(resultSet1);


if(resultSet1.next()){
tempUserID=resultSet1.getInt(1);
temp.close();
return tempUserID;

}

else{
temp.close();

}
}catch(Exception e){System.out.println("Problem querying for UserID");}
return 0;
}
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
try replacing your double quotes to single quotes in your query string. Using single quotes is the SQL standard.
change:
String sqlString="SELECT USER_ID FROM USER WHERE USER_NAME=\""+userName+"\"";
TO:
String sqlString="SELECT USER_ID FROM USER WHERE USER_NAME='"+userName+"' ";
Jamie

[This message has been edited by Jamie Robertson (edited August 21, 2001).]
 
Ronald Whalen
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jamie,
Thanks for responding. I tried that but it still does not work. I am absolutley baffled. All other db calls work. Just this one does not. Hmmm......
Ronald
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
try:
String sqlString="SELECT USER_ID FROM USER WHERE USER_NAME='"+userName+"' ";
System.out.println(sqlString);
after executing your code, cut and paste exactly what was printed out to the SQL Editor and run it. Log in to the SQL session using the same parameters your program uses to log in.
by the way, you say a SQLException is thrown...What is the error number and error description.
Jamie
As a last resort, use the fully qualified tablename (schema-name.USER).

 
Ronald Whalen
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jamie,
Thanks. The problem was caused by a committ statement. Again thanks for the help.
Ronald
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic