When working with customer code based on ADO.net, I was surprised by the following error message:
The german message just tells us that some
UpdateCommand had an effect on “0” instead of the expected “1” rows of a
DataTable. This happened on writing some changes to a table using an
OracleDataAdapter. What really surprised me at this point was that there certainly was no other thread writing to the database during my update attempt. Even more confusing was, that my method of changing
DataTables and using the
OracleDataAdapter to write changes had worked pretty well so far.
In this case, the title “
DBConcurrencyException” turned out to be quite misleading. The text message was absolutely correct, though.
UpdateCommand is a prepared statement generated by the
OracleDataAdapter. It may be used to write the changes a
DataTable keeps track of to a database. To update a row, the
UpdateCommand identifies the row with a
WHERE-clause that matches all original values of the row and writes the updates to the row. So if we have a table with two rows, a primary id and a number, the update statement would essentially look like this:
UPDATE EXAMPLE_TABLE SET ROW_ID =:current_ROW_ID, NUMBER_COLUMN =:current_NUMBER_COLUMN WHERE ROW_ID =:old_ROW_ID AND NUMBER_COLUMN =:old_NUMBER_COLUMN
In my case, the problem turned out to be caused by string-valued columns and was due to some oracle-weirdness that was already discussed on this blog (https://schneide.blog/2010/07/12/an-oracle-story-null-empty-or-what/): On writing, empty strings (more precisely: empty VARCHAR2s) are transformed to a DBNull. Note however, that the following are not equivalent:
WHERE TEXT_COLUMN = ''
WHERE TEXT_COLUMN is null
The first will just never match… (at least with Oracle 11g). So saying that null and empty strings are the same would not be an accurate description.
WHERE-clause of the generated
UpdateCommands look more complicated for (nullable) columns of type
VARCHAR2. But instead of trying to understand the generated code, I just guessed that the problem was a bug or inconsistency in the
OracleDataAdapter that caused the exception. And in fact, it turned out that the problem occured whenever I tried to write an empty string to a column that was
DBNull before. Which would explain the message of the
DBConcurrencyException, since the
DataTable thinks there is a difference between empty strings and
DBNulls but due to the conversion there will be no difference when the corrensponding row is updated. So once understood, the problem was easily fixed by transforming all empty strings to
null prior to invoking the