How do I trap DBI errors?

[megapode@gmail.com]

Good day all,

I have a program that receives files from another machine and then must
update an audit trail to show bytes received, date and time of receipt
etc etc... The audit trail is updated as the file moves through my
system so that I can always determine if I received a file and how and
when I disposed of it.

One of the requirements, of course, is that if the program cannot
connect to or cannot update the audit trail table then the program
should generate an error condition and not accept the file that is
being sent.

Below is some of the code from the program, an in particular the
subroutine that updates the audit trail. I have established that as
things currently stand it is possible for my system to receive a file
even though the audit trail cannot be updated. The receiving program
connects to the database for each receipt, so it SHOULD be constantly
checking if it can connect to the audit trail and if it can write.
Unfortunately it is NOT detecting situations where it can't connect to
the database (EG when the table is corrupted).

First the actual call to the subroutine and the checking of the
results....

my @results = updateAuditTrail($valString);
# could we update the audit trail?
if($results[0] == 0)
{
# audit trail was updated...
$trackNo = $results[2];

# check whether to queue for transmission or
transformation
my $auditValue = "";
if($transformSet){
# insert the data into the WaitingTransforms table,

# the ID in the audit table will be returned
$auditValue =
insertWaitingTransforms("File=>$filePath", "Rule=>$fileType");
}
else
{
# insert the data into the WaitingSends and
SendDetails tables,
# the ID in the audit table will be returned
$auditValue = insertWaitingSends("File=>$filePath",
"Rule=>$fileType");
}
# check if the insert operation failed...
$auditValue =~ s/.+\(AuditEntry: ([0-9]+)\)/$1/;
# remaining message should now be numeric
(Audittrail.audit ID)
# if the insert succeeded.
unless($auditValue =~ /^\d+$/)
{
$errorStatus += 512;
$whatHappened = "Error from QueuesDatabase.pm!
($auditValue)";
}
}
else
{
# there was an error when we tried to update the audit
trail
$errorStatus += $results[0];
$whatHappened = "$results[1] File not uploaded\!";
}
# if no error, report the tracking number, else delete the
uploaded file...
unless($errorStatus)
{
print "NCMtrack: $trackNo\n";
}
else
{
unlink $filePath;
}

Then actual routine that updates the audit trail.

sub updateAuditTrail
{
my $stringToInsert = shift;

# initialise return values
my $ref = '';
my $errorCode = '';
my $errorMsg = '';

# now update the audit trail
# connect to the Database...
if(my $audit =
DBI->connect('DBI:mysql:Audittrail','transformer','peb cak'))
{
my $fieldsString =
'(INPUT_FILENAME,OUTPUT_FILENAME,DATE_TIME,ORIGIN, PROCESS_NAME,TYPE,STATUS,MESSAGE,TFR_START,TFR_END ,BYTES)';

# create the SQL command
my $cmdString = 'INSERT INTO audit '.$fieldsString.' values
'.$stringToInsert;

# perform the query...
my $cmd = $audit->prepare($cmdString);
$cmd->execute;

# now get the value of the auto increment field for the row we
have just inserted
$ref = $audit->{mysql_insertid};

# if the write to the audit table was unsuccessful, returnand
error message
unless ($ref)
{
$errorCode = 512;
$errorMsg = "Cannot write to the audit table in the Audit
Trail database.";
}

# disconnect from the db
$audit->disconnect;
}
else
{
$errorCode = 512;
$errorMsg = "Cannot connect to the Audit Trail database.";
}
return($errorCode,$errorMsg,$ref);
}

[megapode@gmail.com]

[email]megapode@gmail.com[/email] wrote:

> One of the requirements, of course, is that if the program cannot
> connect to or cannot update the audit trail table then the program
> should generate an error condition and not accept the file that is
> being sent.

snip!

And thanks for the off-list reply. It seems that checking $DBI::errstr
does the trick.