DBI Error Trapping

timo has asked for the wisdom of the Perl Monks concerning the following question:

The below snippet is from a program I've written to compare the data elements between database tables. This particular section reaches a table with a column having different datatypes in each table. Oracle doesn't like this and the program returns an error and fails. I'm trying to trap the error so the program can continue but I can't seem to get it to work? I've tried RaiseError, unless, and eval....

        #** Returns unique data from all included columns per table.
        $db3->{RaiseError} = 1;
        unless ($sth_minus = $db3->prepare_cached("
                                            SELECT $column_list FROM $
+database_user_1.$table_names_r->[0]".'@'."$config{database_dblink_1}
                                            MINUS
                                            SELECT $column_list FROM $
+database_user_2.$table_names_r->[0]".'@'."$config{database_dblink_2}
                                         "))
               {warn $@ if $@;}
             
        $sth_minus->execute;
[download]

Comment on DBI Error Trapping Download Code

Replies are listed 'Best First'.
Re: DBI Error Trapping by runrig (Abbot) on Dec 18, 2001 at 02:11 UTC
Setting 'RaiseError' to 1 means 'die if there is an error'. You have two choices: eval the prepare statement and then check $@ after the prepare, or set RaiseError to 0 and check the status returned from the prepare statement.	[reply]
Re: Re: DBI Error Trapping by timo (Novice) on Dec 19, 2001 at 11:43 UTC
I've changed it to: `$db3->{RaiseError}=0; unless ($sth_minus = $db3->prepare_cached(" SELECT $column_list FROM $ +database_user_1.$table_names_r->[0]".'@'."$config{database_dblink_1} MINUS SELECT $column_list FROM $ +database_user_2.$table_names_r->[0]".'@'."$config{database_dblink_2} ")) {warn $@ if $@;}` [download] but the program still dies. Any suggestions?	[reply] [d/l]
Re: Re: Re: DBI Error Trapping by htoug (Deacon) on Dec 19, 2001 at 12:15 UTC
Try something like: `{ local $db3->{RaiseError}=0; $sth_minus = $db3->prepare_cached(" SELECT $column_list FROM $database_user_1.$table_names_r->[0]". +'@'."$config{database_dblink_1} MINUS SELECT $column_list FROM $database_user_2.$table_names_r->[0]". +'@'."$config{database_dblink_2} "); warn "prepare fails with $db3->errstr" if $db3->err; }` [download] The errorcode is returned in `$db3->err` and the string in `$db3->errstr` not in `$@`. Localising `$db3->{RaiseError}` is a way of locally setting the RaiseError attribute without having to rembember the state (probably not needed here, but it is a useful idiom :-).	[reply] [d/l]
Re: Re: Re: DBI Error Trapping by runrig (Abbot) on Dec 19, 2001 at 23:14 UTC
The error string will be in $@ if you set RaiseError to 1 AND eval the prepare statement. Or do what htoug suggests above. Sorry if I was unclear :)	[reply]
Re: DBI Error Trapping by Zaxo (Archbishop) on Dec 18, 2001 at 07:29 UTC
DBI keeps the server error string around after every operation. Each DBI handle can access: `warn $db3->errstr; #... warn $sth_minus->errstr;` [download] After Compline, Zaxo	[reply] [d/l]