The following is an insert script I working on. I need to trap the unique constraint
error and send a message back to the user. I am really confused. I have read the
error handling chapter of the O'reilly Perl DBI book, but I just don't get it. I must
have some kind mental block. If I could get a working example hopefully the light
will come on. This will eventually work with a html Form.
Thanx
Joe Condle
Script started on Fri Apr 12 06:43:36 2002
$ more test.pl
#!/usr/bin/perl -w
#
#
use strict;
use DBI qw(:sql_types);
my $dbh = DBI->connect ( 'dbi:Oracle:xxxxxxxxxxx',
'xxxxxx',
'xxxxx',
{
RaiseError => 0,
PrintError => 1,
AutoCommit => 0
}
) || "Database connection not made :$DBI::errstr";
my $ssn = '987654321';
my $fname = 'Mike';
my $lname = 'Beer';
my $city = 'Pgh';
my $state = 'Pa';
my $zip = '98765';
my $sql = qq{ INSERT INTO employee VALUES (?, ?, ?, ?, ?, ?)};
my $sth = $dbh->prepare( $sql );
$sth->bind_param(1,$ssn,SQL_VARCHAR);
$sth->bind_param(2,$fname,SQL_VARCHAR);
$sth->bind_param(3,$lname,SQL_VARCHAR);
$sth->bind_param(4,$city,SQL_VARCHAR);
$sth->bind_param(5,$state,SQL_VARCHAR);
$sth->bind_param(6,$zip,SQL_VARCHAR);
$sth->execute();
$dbh->commit;
open(DAN, "> debug.txt");
printf DAN "Errors \n";
printf DAN "err - %s \n",sth->err();
printf DAN "errstr - %s \n",$sth->errstr();
printf DAN "state - %s \n",$sth->state();
printf DAN "errstr - %s \n",$DBI::errstr;
close(DAN);
$sth->finish();
$dbh->disconnect();
$ test.pl
DBD::Oracle::st execute failed: ORA-00001: unique constraint (CONDLE.EMPLOYEE_PK_SSN)
violated (DBD ERROR: OCIStmtExecute) at ../test.pl line 35.
Can't locate object method "err" via package "sth" (perhaps you forgot to load "sth"?)
at ./test.pl line 40.
Issuing rollback() for database handle being DESTROY'd without explicit disconnect().
$
$ more debug.txt
Errors
$
script done on Fri Apr 12 06:44:01 2002
