>This is what I was thinking, the problem then becomes that I'm
>not aware of way to determine the error with
>some degree of accuracy so that I don't mistake:
> insert error because of duplication
>with:
> insert error because of database connectivity (or other factors)
>
>Is it possible to do that? I guess I could parse the error responce
>from the backend, but maybe there's an easier/more-correct way?
Not sure what interface you are using, But for example, perl will
easily tell the difference.
======================================================================== execute
$rv = $sth->execute || die $sth->errstr; $rv = $sth->execute(@bind_values) ||
die$sth->errstr;
Perform whatever processing is necessary to execute the prepared statement. An undef is returned if
an error occurs, a successful execute always returns true regardless of the number of rows affected
(evenif it's zero, see below). It is always important to check the return status of execute (and most
otherDBI methods) for errors.
For a non-select statement, execute returns the number of rows affected (if known). If no rows were
affected then execute returns "0E0" which Perl will treat as 0 but will regard as true. Note that it
isnot an error for no rows to be affected by a statement. If the number of rows affected is not known
thenexecute returns -1.
========================================================================
which means the return value will be 0 if the insert is blocked, but
undef in there is a connectivity error.
In other words, failing to insert where a unique index prevents the
insertion is not an error.
PHP is similar.
One trick is to insert all tuple into a temporary table. Then do an
update using the natural join. The do the insert from that same
table.
If you can use a copy to create the temporary table, I think your
performance will be best.
Typically I would index the primary key of the temp table so that the
join proceeds well, but you may want to bench yourself with and
without the index. I don't think it's needed in the case you
describe.
--
Karl DeBisschop <kdebisschop@alert.infoplease.com>
617.832.0332 (Fax: 617.956.2696)
Information Please - your source for FREE online reference
http://www.infoplease.com - Your Ultimate Fact Finder
http://kids.infoplease.com - The Great Homework Helper
Netsaint Plugins Development
http://netsaintplug.sourceforge.net