Thomas A. Lowery [EMAIL PROTECTED] wrote:
> As long as RaiseError is turned OFF ($dbh->{RaiseError} = 0),
> you may trap the errors without evals. The error message will change
> based on the database.
>
> PostgreSQL Example:
>
> #!/usr/bin/perl -w
> #
> #
>
> use strict;
> use warnings;
> use DBI;
>
> my $dbh = DBI->connect() or die $DBI::errstr;
> # To trap errors, without using eval turn RaiseError OFF
> $dbh->{PrintError} = 0; $dbh->{RaiseError} = 0; $dbh->{Warn} = 0;
>
> # Drop the test table.
> $dbh->do( q{drop table userids} ); # Ignore results (table may not exist)
>
> # Create test table.
> $dbh->do( q{CREATE TABLE userids (
> user_id character varying(64) NOT NULL)}) or
> die "Unable to create: " . $dbh->errstr;
>
> # Add primary key.
> $dbh->do( q{ALTER TABLE ONLY userids
> ADD CONSTRAINT userids_pkey PRIMARY KEY (user_id)}) or
> die "Adding primary key failed " . $dbh->errstr;
>
>
> # List of users to add.
> my @users = qw/tal skl atl rml pth tal/;
>
> foreach my $user (@users) {
> # Insert the user.
> my $added = $dbh->do( q{insert into userids values (?)}, undef, $user );
>
> # Check an error
> if ($dbh->err) {
> # Does the error message contains the phrase: duplicate key
> if ($dbh->errstr =~ m/duplicate\skey/i) {
^^^^^^^^^^^^^^
Just to clarify, as Thomas said above, each database produces
different error messages to indicate that you are trying
to insert a duplicate key - so what you search for here will
vary depending on which database you are using.
The only way to find out what message your database will
produce is to create a key (CONSTRAINT above) that will make
each row unique based on your "unique" requirments, and then
INSERT a row, and then try to insert a row with the same key
fields so that you intentionally cause the database to produce
a duplicate key error. Then use that error, or part of it,
to come up with a regular expression that will recognize
a database error as a duplicate key error.
HTH.
--
Hardy Merrill
Red Hat, Inc.
> print "User $user already exists\n";
> } else {
> print "Error: ", join( " ", $dbh->state,$dbh->err,
> $dbh->errstr, "\n");
> }
> } else {
> print "Added user $user\n";
> }
> }
>
> $dbh->disconnect;
>
> exit;
>
>
> On Fri, Sep 26, 2003 at 08:47:46PM -0400, [EMAIL PROTECTED] wrote:
> > You can wrap it in an exec block and then check $@ after completion, like this:
> >
> > eval {
> > $sth_ins_user->execute(
> > $user_id,
> > $username,
> > $password
> > );
> > };
> >
> > if ($@) {
> > # custom error-handling
> > $dbh->rollback();
> > $dbh->disconnect();
> >
> > if ($@ =~ /^DBD::ODBC::st execute failed: \[Oracle\]\[ODBC\]\[Ora\]ORA-
> > 00001: unique/) {
> > print 'That username has already been selected';
> > DieOnError($msg);
> > }
> > else {
> > print "Other error";
> > }
> > exit();
> > }
> >
> >
> > The specific syntax of what you need to check for in $@ is DB-specific, so the
> > code here will only work for Oracle.
> >
> > Paul
> >
> > Quoting [EMAIL PROTECTED]:
> >
> > > I am just begginning to utilize perl w/apache. Since the amount of
> > > documentation for perl is so overwhelming, I hope this list can help me
> > > out.
> > >
> > > Question: How can I determine that a failure on insert is caused by
> > > duplicate value/primary keys? I would like to handle this specific failure
> > > separately from other failure.
> > >
> > > thanks,
> > > -rkl
> > >
--
Hardy Merrill
Red Hat, Inc.
