JDK-4032732 : Unable to distinguish an SQL NULL string from empty string ('')
  • Type: Enhancement
  • Component: core-libs
  • Sub-Component: java.sql:bridge
  • Affected Version: 1.1
  • Priority: P3
  • Status: Closed
  • Resolution: Not an Issue
  • OS: windows_nt
  • CPU: x86
  • Submitted: 1997-02-17
  • Updated: 1997-05-06
  • Resolved: 1997-05-06

Name: mc57594			Date: 02/17/97

The ResultSet.wasNull() function returns true after a
getString() or getObject() call on a VARCHAR field that contains
an empty string (i.e. '').  A WHERE clause, however, distinguishes
between the two.  Thus, if you execute this statement:

 update table set varcharfield='' where primary_key=1

and then execute:

 select varcharfield from table where primary_key=1

using ResultSet.getString() followed by ResultSet.wasNull() to
obtain the field value, the ResultSet.wasNull() will return true.
This is not correct, as can be demonstrated by excuting this

 select varcharfield from table where primary_key=1 and
  varcharfield is null

This statement will (correctly) return no rows. indicating that
the varcharfield does not contain a SQL NULL, but rather an
empty string.

This was obseved with the JDBC-ODBC bridge against an SQL Anywhere
5.0 database.  It's important, because there is a class of
applications that use what you might term "optimistic locking"
for updates: they read a row from the table, allow the user to
modify some fields, then do an 'update where field=oldvalue and
field=oldvalue and ...' to ensure that the row hasn't been
changed by someone else during the interim.  This will fail:
the 'update where varcharfield is null and ...' won't succeed
because the varcharfield isn't really null (this is how we
found the problem in the first place).

We have a workaround (check for "varcharfield is null or varcharfield='' ")
but this is pretty ugly...

company  -  SilverStream Software  , email  -  ###@###.###

EVALUATION This is not a bug. Some databases consider an empty string to be synonymous with NULL.

WORK AROUND Name: mc57594 Date: 02/17/97 ======================================================================