null problems

[zeero]

Hi all, I have a jsp page that has a block of code to update a record in a mysql DB if it's there, however i'm trying to error trap it so it checks to see if the record is even in the field first before it updates. Would it be something like?

...
else{
     String query3 = "select roomnumber from table where roomnumber= ('"+roomnumber+"')";
     ResultSet result3 = null;		
try {
      result3 = stmt.executeQuery(query3);
    } catch (Exception e) {
		message = "Error";
      }
	
	if ((result3 != null) && (result3.next())) {
		result3 = stmt.executeQuery(query3);
		 if (roomnum == null) {
			message = "Error: The room number was not found in the database.
		}
}
...

assuming null = empty set

 

[LFI]

It occurs to me that the whole try/catch block section is not needed at all and the last part of your code is fine as is.

However, if you are getting confused by nulls and how to handle them and all you want to know is if the room number is in the database, consider changing your SQL to:

select count(roomnumber) from table where roomnumber= ('"+roomnumber+"')"

Then, if the room number is not there, the result is 0. Any results greater than 0 indicates its presence. You will not have to deal with nulls at all.

Good luck.

--Dave

 

[sedj]

I would go with something a bit more clean like :

String sql = "select roomnumber from table where roomnumber= ('" +roomnumber +"')";
ResultSet rs = stmt.executeQuery(sql);
boolean foundRoom = rs.next();
rs.close();
stmt.close();

if (foundRoom) {
   // room already exists/taken
} else {
   // room is free
}

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[LFI]

sedj,

I like that. It "reads" more neatly.

Thanks!

--Dave

 

[zeero]

yes, thank you both!

 

[sedj]

Also, in catching Exceptions, this is meaningless as
it tells you nothing at all:

try {
  ...
} catch (Exception e) {
  message = "Error";
}

I would always do this, becasuse it gives you the exact exception, and the points in the Java source code in which the exception occurred :

try {
  ...
} catch (Exception e) {
  e.printStackTrace(System.err);
}

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[zeero]

oh yeah, i didn't mean to copy and paste that, that was part of my error checking for something I screwed up earlier. Sorry about that sedj.