Given a transaction that posts some data, and it bounces back with a
MySqlException (in the .Net connector) because of a duplicate key/unique
value, in general one needs to know which column and value were
involved. Of course one could parse the error message, but that's a
little tacky, and on a more practical level carries the risk that the
wording could change in a future or localized version.

Is there a programmatic way to do it? The MySqlException class doesn't
seem to offer anything obvious.

Thanks,

--
"Always look on the bright side of life."
To reply by email, replace no.spam with my last name.

On Apr 11, 8:24 pm, Russell Wallace <russell.no.s... (AT) gmail (DOT) com> wrote:
In what cases would this not be a duplicate of the Primary Key?
Unless you are building the insert statements dynamically, don't you
know at code development time what the PK columns are? I think that
would explain why there isn't something about this in the exception
class.

Ed

Ed Prochak wrote:
I'm using a synthetic primary key, so in a given table there will
typically be two unique keys, the synthetic primary and the domain level
key.

Even if I were using only one key, a transaction in general will add
multiple records to multiple tables, so it's necessary to know which
table and which record the duplicate occurred in.

--
"Always look on the bright side of life."
To reply by email, replace no.spam with my last name.

On 13.04.2007 01:02, Russell Wallace wrote:
I think there is no standard way to access this information because the
reasoning is as follows: the application wanted to insert X,Y,Z within a
transaction. If there is a constraint violation it does not matter what
item caused the violation, the whole transaction cannot succeed. This
goes with the assumption that the application had a reason it wanted to
insert X,Y,Z and there is no alternative X,Y,R.

Other than that, if the exception is thrown for each violating insert
statement you could find out by wrapping each insert in try catch. But:
if your DB happens to wait for COMMIT you don't have any other chance as
to try to verify before insert. This method however has its own
drawbacks and uncertainties...

robert

Robert Klemme wrote:
Hmm, this seems odd; I understand that people differ in how they prefer
to set up keys (domain, integer, GUID) but no matter how the keys are
set up, if XYZ is unacceptable there are only two possibilities: either
the program must find an R such that XYR is acceptable, or it must
prompt the user to do so; and in both cases it needs to know Z. I'm
surprised MySQL haven't addressed this yet.

Such as, X goes through, then Y... then the whole transaction bounces,
because in the time before COMMIT, another user slipped in with a
transaction that used X.

Looks like I'm going to have to bite the bullet and parse the message.

--
"Always look on the bright side of life."
To reply by email, replace no.spam with my last name.

Russell Wallace wrote:
It shouldn't be that likely that another user "slips in" with the identical
records to insert unless your artificial key is not protected against such
collisions. Most scenarios involving natural keys would minimize such a
collision.

Of course, sequential artificial keys usually return whatever the correct next
value is, so I don't understand how this "other user" is able to coopt that.

How come it's so likely that more than one user wants to input data with the
same values? That seems to be unusual.

--
Lew

Russell Wallace wrote:
First we write the invoice number without the items, then we write the
items once we know the the invoice number then we rewrite the keys to
the items in the invoice file. We have a routine to get the next
generated key. By generated it is just self incrementing. As the key
is fetched it keeps the system from issuing the key to some other
application. There is a similar painful process if you have to go back
and write tax info.

Lew wrote:
Well, mostly it happens with things like invoice numbers (which can't be
autoincs because the user may want to do things like include alpha
components), you have INV100 in the system, two people try to post
INV101 at the same time, the program has to know the reason for the
collision to know it has to bump one of them to INV102.

But other cases can happen too, people trying to enter new products with
the same product code, say - that's less likely, but correctness can't
depend on likelihood, it's necessary that it be handled when it does
happen. Similarly for randomly generated surrogate keys, if I go to 128
bits the probability of collision is extremely small, so it's okay for
the handling of such to be slow - but it still has to work.

--
"Always look on the bright side of life."
To reply by email, replace no.spam with my last name.

Last Boy Scout wrote:
*nods* "Painful" is exactly how I figured it would be - that's why I'd
prefer if possible to avoid any back and forth, and just throw all the
data one way in a single transaction, if it goes through great, if it
bounces fix the offending value.

--
"Always look on the bright side of life."
To reply by email, replace no.spam with my last name.