Re: JDBC squirrely transaction behavior??

Peter Mount wrote:
>
....
>
> > This code will run for a number of insertions, then fail because the
> > select of the foo_id returns no records. This should be impossible,
> > because either (1) the foo_id was just added to the foos table, or (2)
> > it had previously been added. Either way, there should be a record
> > with a matching namefield.
>
> I can see a problem here. Your first insert inserts foo_id, and fails if
> foo_id already exists. In your code, you ignore this which is fine outside
> a Transaction. However, because you are using a transaction, the
> transaction is now in a failed state, so everything else done in that
> transaction will be ignored.

Hmmm, that may cause me a problem in a similar situation - I want to
do an update whenever an insert fails. Further, for performance reasons
the insertions are done in large transactions (thousands to tens of
thousands of inserts in each transaction). Does the above comment mean
that this ("update after failed insert") won't work as I'd like because
it's inside a transaction? Is there a way to force the state of the
transaction back to success after the failed insert, without losing the
insertions that have been made previously in the transaction [or those
that are made subsequently...]?

> The alternative is to but a connection.commit() after the insert, which
> may work in this case, but you have to think about the integrity of your
> tables.

This sounds like it would work in my case - does anyone see why it wouldn't
or have a more appropriate approach? (Through JDBC, of course.)

Thanks!

--
Steve Wampler- SOLIS Project, National Solar Observatory
swampler(at)noao(dot)edu