Unexpected (?) try/catch Behavior

[Rob Wilkerson]

Disclaimer: I know enough Perl to get by when I absolutely have
to use it, so maybe this is the correct behavior, but having never
seen it in any other language, I thought I'd post the question to the
group and maybe learn something.

I have a scenario where I need to try to insert a record into a MySQL
database and, if the insert fails due to, well, anything, then perform
an update instead. To do this, I'm using the try/catch construct of
the Error module:

use Error qwtry);

try {
$sql = qq {
INSERT INTO table (
field1,
field2,
field3,
field4,
field5
)
VALUES ( ?, ?, ?, ?, ? )
};

$sth = $mysql->prepare ( $sql );
$sth->execute ( $value1, $value2, $value3, $value4, $value5 );
$sth->finish();
}
catch Error with {
$sql = qq {
UPDATE table
SET field1 = ?,
field2 = ?,
field3 = ?
WHERE field4 = ?
AND field5 = ?
};
$sth = $mysql->prepare ( $sql );
$sth->execute ( $value1, $value2, $value3, $value4, $value5 );
$sth->finish();
};

The code seems to be doing exactly what I expect. That is, dropping
into the catch block and performing the update where a record exists,
but the errors being caught are not being suppressed. I still get a
lot of:

DBD::mysql::st execute failed: Duplicate entry '121993-14196' for key
1 at ./get_metrics.pl line 247.

It's not the end of the world, but I'd prefer to suppress the message
if there's a way to do that. Is this expected behavior? It certainly
caught me by surprise and I spent a while trying to debug until I
realized that if I looked past the messages, the work was getting
done.

I did try using the eval{} if ($@){} combo, but got the same result.

Thanks.

 

[Radoulov, Dimitre]

Rob Wilkerson wrote:
[...]

I have a scenario where I need to try to insert a record into a MySQL
database and, if the insert fails due to, well, anything, then perform
an update instead.

[...]


Not answering your Perl question, but
what's wrong with:

INSERT ... ON DUPLICATE KEY UPDATE



Regards
Dimitre

 

[Peter Makholm]

The code seems to be doing exactly what I expect. That is, dropping
into the catch block and performing the update where a record exists,
but the errors being caught are not being suppressed. I still get a
lot of:

DBD::mysql::st execute failed: Duplicate entry '121993-14196' for key
1 at ./get_metrics.pl line 247.

Being in a try block does not prevent DBI from printing error
messages. You have to set the PrintError attribute on you database
handle to 'off'.

//Makholm

 

[Rob Wilkerson]

Rob Wilkerson wrote:

Not answering your Perl question, but
what's wrong with:

INSERT ... ON DUPLICATE KEY UPDATE

Regards
Dimitre

Hmmm. Nothing at all, except that I'd never needed it and wasn't aware
of its existence. I'll definitely look at that since it'd be a much
cleaner solution all the way around.

Thanks for the clue.

 

[Rob Wilkerson]

Being in a try block does not prevent DBI from printing error
messages. You have to set the PrintError attribute on you database
handle to 'off'.

Damn. I've built this thing to reuse a single database handle, so I
can't really do that. I might want those errors somewhere else.

Thanks for the insight.

 

[Mart van de Wege]

Damn. I've built this thing to reuse a single database handle, so I
can't really do that. I might want those errors somewhere else.

You can set the attribute anywhere you like. So you clear it to for
just the try block alone, and set it back to one afterward.

The other option is to clear PrintError and set RaiseError. That'll
make your script die on DBI errors unless you wrap your DBI method
calls in eval {} blocks. In case you want to continue on error, you
can just print the error message yourself.

I haven't used Error yet, so I don't know how it deals with DBI's
RaiseError.

Mart

 

[Rob Wilkerson]

Rob Wilkerson wrote:

[...]> I have a scenario where I need to try to insert a record into a MySQL

database and, if the insert fails due to, well, anything, then perform
an update instead.

[...]

Not answering your Perl question, but
what's wrong with:

INSERT ... ON DUPLICATE KEY UPDATE

So it turns out that I wasn't using this because it won't work (even
though I only figured that out now). The table I'm inserting/updating
has a dual primary key. That appears to be a no-no.

 

[xhoster]

First off, I don't agree with your tactic. If you get a duplicate key
error, then by all means do an update instead if that is the logical thing
to do. But if you get a "Server is currently on fire" error, I don't see
that going on to try to update is the right thing to do.

Damn. I've built this thing to reuse a single database handle, so I
can't really do that. I might want those errors somewhere else.

Thanks for the insight.

Use local to localize the effect.

{
local $mysql->{PrintError}=0;
## do whatever. Errors will not be Printed
};
# at this point, PrintError has its original value.

Xho

--
-------------------- http://NewsReader.Com/ --------------------
The costs of publication of this article were defrayed in part by the
payment of page charges. This article must therefore be hereby marked
advertisement in accordance with 18 U.S.C. Section 1734 solely to indicate
this fact.