Re: JDBC behaviour

On Thu, 18 Feb 2016 13:48:04 +0100 (CET), Andreas Joseph Krogh
<andreas(at)visena(dot)com> wrote:
> I understand that and indeed this isn't something that should be
handled
> by the driver, however some of the response in this thread seem to
think
> it
> is an absurd expectation from the OP that failure of one statement
should
> still allow a commit. Which it isn't if you look at what other database
> systems do.
>
> Mark
>  
> If that one failed statement doesn't raise an exception, how does the
> client
> (code) know that it failed? If it does raise an exception, then what
> standard
> specifies that that specific exceptions is to be treated as "don't
> rollback for
> this type of error"?

Of course an exception is raised, but the exact handling could then be
left to the client. For example the client could catch the exception,
decide based on the specific error to execute another statement to "fix"
the error condition and then commit. Think of INSERT, duplicate key, then
UPDATE before the existence of 'UPSERT'-like statements; if the occurrence
of duplicate key is rare it can be cheaper to do than to first SELECT to
check for existence and then INSERT or UPDATE, or to UPDATE, INSERT when
update count = 0. Another situation could be where the failure is not
important (eg it was only a log entry that is considered supporting, not
required), so the exception is ignored and the transaction as a whole is
committed.

Sure, in most cases it is abusing exceptions for flow control and likely
an example of bad design, but the point is that it is not outlandish to
allow execution of other statements and eventually a commit of a
transaction even if one or more statements failed in that transaction; as
demonstrated by systems that do allow this (for SQL Server you need to set
XACT_ABORT mode on to get similar behavior as PostgreSQL).

As to standards, for batch execution, JDBC expects that a driver either
process up to the first failure and raise a BatchUpdateException with the
update counts of the successfully executed statements, or continue
processing after failure(s) and only raise the exception after processing
the remainder of the batch (where the exception contains a mix of update
counts + failure indications). In both cases a commit for the statements
that were processed successfully would still be possible if the client so
wishes (see section 14.1.3 "Handling Failures during Execution" of JDBC
4.2).

Mark