• 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

Comparing NUMBER columns with String values

 
Ranch Hand
Posts: 1211
Mac IntelliJ IDE
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

Had anybody had any problems with this before?
 
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Sonny,

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.

Jules
 
JulianInactive KennedyInactive
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

Jules
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
DB2 does not allow you to do it. You would get a runtime error about invalid syntax.

You really should consider using a prepared statement. That way the driver will handle all the type conversion details.
 
Sonny Gill
Ranch Hand
Posts: 1211
Mac IntelliJ IDE
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I was actually using a PreparedStatement.

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 ]
 
reply
    Bookmark Topic Watch Topic
  • New Topic