I just realized that in Oracle, you can compare a NUMBER column with a String value. For example, if there is a table emp with a column emp_no of type NUMBER(4),
You can do SELECT name FROM emp WHERE empno=123 , no surprise there.
But you can also do SELECT name FROM emp WHERE empno='123' or SELECT name FROM emp WHERE empno=' 123 ' and this gets you the same results.
Although SELECT name FROM emp WHERE empno=' 12 3' gives you an Oracle 'Invalid Number' error.
I am just curious if it is the same for other major databases, because sometimes it is convenient to call setString() for integer parameters, rather than parse it first, esp. when you can be pretty sure that the String being passed is a valid number.
IMHO relying on implicit type conversions is always a bad idea. From a performance point of view your code examples need to convert the supplied string to a number before comparing it to the column values. At worst they will convert the column value to a string for each row that needs to be compared (although it doesn't appear that way in this case). Supplying the right data type in the first place will eliminate the potential for unexpected database errors like the one in your example. It's also difficult for a programmer using your code in future to determine whether you did it accidentally or on purpose, i.e. is it a latent bug that's just working by accident?
That's my opinion anyway.
posted 16 years ago
Oh, and behaviour in other major RDBMS varies. From memory (not 100% reliable!), INGRES would allow it; MS SQL Server, Sybase and MS Access would not; MySql ... errr ... might (unless you wanted to do it in a sub-query ) and I don't know DB2.
Just to see what happens, I set the PreparedStatement parameter (which is being compared with a NUMBER column), using setString(), since I was getting the value as a HTTP request parameter, and I was surprised that it actually worked.
Thanks Julian, Jeanne. I realize it is a bad idea, the idea of not having to use parseInt, and try-catch for NumberFormatException was tempting though [ August 09, 2004: Message edited by: Sonny Gill ]