hi, I've a script which inserts

[frasernm]

hi,

I've a script which inserts (correctly) into several tables. I'm trying to get errors from dbi so that I can rollback, and then display a more user-friendly error message than the standard database error if something goes wrong (usually the form not filled out correctly).

On an error my script will end up in the if($@) section and so rollsback, but I can't get the errors to print out. Any ideas?

Thanks,

Fraser

eval
{
### check that there are no empty fields in the required arrays
### (null values are set for those without competencies/compliances)
if ((checkForNULLs(\@competencies) + checkForNULLs(\@compliances)) > 0)
{
die "Not all required fields were filled";
}

### have all required data, we can start adding to database
if ($team ne '-' && $team ne '')
{
if (!defined($isStrat) || $isStrat eq '')
{
$isStrat = 'N';
}

my $teamSQL = "INSERT INTO aat_agenthasteam "
. "(ahtagentid, ahtmonth, ahtisstrat, ahtteamname)"
. "VALUES "
. "('$id', TO_DATE('$month', 'FMMonth YYYY'), '$isStrat', '$team')";

$dbh->do($teamSQL) or die $dbh->errstr();
}

### insert report variables into summary table
my $qSummary = $dbh->quote($summary);

my $sumSQL = "INSERT INTO aat_agentmonthlyassessment "
. "(amaagentid, amamonth, amasummary, amadateadded, amaenteredby) "
. "VALUES "
. "('$id', TO_DATE('$month', 'FMMonth YYYY'), $qSummary, getrmctime, '$ldapID')";

$dbh->do($sumSQL) or die $dbh->errstr();

### insert competencies into database
for (my $i=0; $i<@compIDs; $i++)
{
my $qComment;

my $compNo = $compIDs[$i];

if ($comments[$i] eq '')
{
$qComment = 'NULL';
}
else
{
$qComment = $dbh->quote($comments[$i]);
}

my $sql = &quot;INSERT INTO aat_agenthascompetency &quot;
. &quot;(ahcagentid, ahccompid, ahcmonth, ahcenteredby, ahccompetency, ahccompliance, ahcnotes) &quot;
. &quot;VALUES &quot;
. &quot;('$id', $compNo, TO_DATE('$month', 'FMMonth YYYY'), '$ldapID', $competencies[$i], $compliances[$i], $qComment)&quot;;

$dbh->do($sql) or die $dbh->errstr();
}
}; ### end of outer eval

### commit if no problems, else rollback
if ($@)
{
$dbh->do('rollback');

print $@; #doesn't work

if ($@ =~ /CK_AHCCOMPETENCY/)
{
print &quot;<P>All competencies must have value 0, 1, 2, 3, 4 or 5.</P>&quot;;
}

### print error message
print &quot;<P>No change has been made to the database</P>&quot;;

displayEnterReportForm($dbh, $id, $month, $ldapID, $team, $isStrat, \@competencies, \@compliances, \@comments, $summary);
}
else
{
$dbh->do('commit');
print &quot;<P>All entries have been added to the database.</P>&quot;;
}

 

[gorgor]

The best way I've found to do it is to take each major section of your code and make it into its own subroutine. Instead of making the code 'die', if the subroutine is a success, return(1) and if it's a failure, return(0). Then, when calling your subroutine from the 'main program', instead of just calling the subroutine, you would call it like this:

if(!(subroutine()) # if action failed (returned a 0)
{
print &quot;action has failed!<br>\n&quot;;
exit; #script will stop executing
}
else # whatever was in the subroutine was a success!
{
print &quot;action was a success!!<br>\n&quot;;
#now the next code will continue...
}

exit;

You will still need to use 'die' as a safety in cases where it will cause a 'fatal' error and you code would not be able to continue anyway.

Another thing you'll need to do to get the 'errors' to print to the web page is include a header just before your first possible 'print' statement. Otherwise, the browser won't recognize the text you're trying to print to the browser.

print &quot;Content-type: text/html\n\n&quot;;

 

[MikeLacey]

this check here:

if ((checkForNULLs(\@competencies) + checkForNULLs(\@compliances)) > 0)
{
die &quot;Not all required fields were filled&quot;;
}


seems to be the wrong way around to me...

The convention is that sub's return 0 for failure and a non-zero value for success; what does checkForNULLS() return exactly? Mike
________________________________________________________________

&quot;Experience is the comb that Nature gives us, after we are bald.&quot;

Is that a haiku?
I never could get the hang
of writing those things.