aspose file tools*
The moose likes JDBC and the fly likes ResultSet problems Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "ResultSet problems" Watch "ResultSet problems" New topic
Author

ResultSet problems

Ronald Whalen
Ranch Hand

Joined: Mar 27, 2001
Posts: 38
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

Joined: Jul 09, 2001
Posts: 1879

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

Joined: Mar 27, 2001
Posts: 38
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

Joined: Jul 09, 2001
Posts: 1879

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

Joined: Mar 27, 2001
Posts: 38
Jamie,
Thanks. The problem was caused by a committ statement. Again thanks for the help.
Ronald
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: ResultSet problems