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
Description

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
statement:

 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  -  ###@###.###
======================================================================

Comments
EVALUATION This is not a bug. Some databases consider an empty string to be synonymous with NULL.
2004-06-11

WORK AROUND Name: mc57594 Date: 02/17/97 ======================================================================
2004-06-11