Hi Monks! I had posted a question about DB exception handling a few weeks ago. With the help of one of the users I was able to resolve part of the problem but that fix introduced another issue which has got me puzzled.

I have attached my code below that is doing a simple DB line insert but the exception handling is not catching the 'Cast Specification' error or any error related to the Bound. The issue I encountered was primarily with $temp_date scalar and I have since inserted checks for when the values are single digits so to avoid any errors for that variable. But my concern is that I can not account for all anomalies, and I shouldn't have to because thats the reason I have the exception handling but why it isn't capturing that error is beyond me. I tried a trace and that revealed that the disconnect call is clearing the error and so I even tried to store the error before the disconnect but that didn't work either. I would greatly appreciate any help in resolving this issue!! Thanks in advance!

Trace:
    DBI 1.616-ithread default trace level set to 0x0/2 (pid 2892 pi 32
+bb4) at db_eval_test.pl line 42 via db_eval_test.pl line 40
    !! info: '' CLEARED by call to prepare method
    -> prepare for DBD::ODBC::db (DBI::db=HASH(0x26f2e0c)~0x26f2d7c 'I
+NSERT INTO RecordCount VALUES (?,?,?,?,?,?)') thr#32bb4
    <- prepare= ( DBI::st=HASH(0x26f303c) ) [1 items] at db_eval_test.
+pl line 51
    -> bind_param for DBD::ODBC::st (DBI::st=HASH(0x26f303c)~0x26f2ffc
+ 1 'SMOKE\service-test') thr#32bb4
    <- bind_param= ( 1 ) [1 items] at db_eval_test.pl line 52
    -> bind_param for DBD::ODBC::st (DBI::st=HASH(0x26f303c)~0x26f2ffc
+ 2 'TEST') thr#32bb4
    <- bind_param= ( 1 ) [1 items] at db_eval_test.pl line 53
    -> bind_param for DBD::ODBC::st (DBI::st=HASH(0x26f303c)~0x26f2ffc
+ 3 '2013081107999999test') thr#32bb4
    <- bind_param= ( 1 ) [1 items] at db_eval_test.pl line 54
    -> bind_param for DBD::ODBC::st (DBI::st=HASH(0x26f303c)~0x26f2ffc
+ 4 'test_file') thr#32bb4
    <- bind_param= ( 1 ) [1 items] at db_eval_test.pl line 55
    -> bind_param for DBD::ODBC::st (DBI::st=HASH(0x26f303c)~0x26f2ffc
+ 5 '2014-08-12 16:6:45.584') thr#32bb4
    <- bind_param= ( 1 ) [1 items] at db_eval_test.pl line 56
    -> bind_param for DBD::ODBC::st (DBI::st=HASH(0x26f303c)~0x26f2ffc
+ 6 22) thr#32bb4
    <- bind_param= ( 1 ) [1 items] at db_eval_test.pl line 57
    -> execute for DBD::ODBC::st (DBI::st=HASH(0x26f303c)~0x26f2ffc) t
+hr#32bb4
    !! ERROR: 1 '[Microsoft][ODBC SQL Server Driver]Invalid character 
+value for cast specification (SQL-22018)' (err#1)
    <- execute= ( undef ) [1 items] at db_eval_test.pl line 59
    !! ERROR: 1 CLEARED by call to disconnect method
    -> disconnect for DBD::ODBC::db (DBI::db=HASH(0x26f2e0c)~0x26f2d7c
+) thr#32bb4
    <- disconnect= ( 1 ) [1 items] at db_eval_test.pl line 60
    -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x26f2ffc)~INNER) thr#3
+2bb4
    <- DESTROY= ( undef ) [1 items] at db_eval_test.pl line 40
    -> DESTROY for DBD::ODBC::db (DBI::db=HASH(0x26f2d7c)~INNER) thr#3
+2bb4
    <- DESTROY= ( undef ) [1 items] at db_eval_test.pl line 40
    -- DBI::END ($@: , $!: )
    !! info: '' CLEARED by call to disconnect_all method
    -> disconnect_all for DBD::ODBC::dr (DBI::dr=HASH(0x26f287c)~0x26f
+28fc) thr#32bb4
    <- disconnect_all= ( '' ) [1 items] at DBI.pm line 744
!   -> DESTROY in DBD::_::common for DBD::ODBC::dr (DBI::dr=HASH(0x26f
+28fc)~INNER) thr#32bb4
!   <- DESTROY= ( undef ) [1 items] during global destruction
[download]

use strict;
use warnings;
use DBI;
use Time::HiRes qw/gettimeofday/;
use Win32::ODBC;

my @timedata = localtime();
my $sec = $timedata[0];
$sec = '0'.$sec if $sec <10;
my $min =  $timedata[1];
$min = '0'.$min if $min <10;
my $hour =  $timedata[2];
$hour = '0'.$hour if $hour <10;
my $day = $timedata[3];
my $month = $timedata[4]+1;
$month = '0'.$month if $month < 10;
my $year = $timedata[5]+1900;

(my $seconds, my $ms) = gettimeofday();
$ms = ($ms-($ms%1000))/1000;

my $time = $hour.":".$min.":".$sec.".".$ms;
my $date = $year."-".$month."-".$day;
my $temp_date = $date.' '.$time;

my $server_name = 'SMKAPP';
my $database_name = 'TESTDATA';
my $database_user = '';
my $database_pass = '';

my $env =  'SMOKE\\service-test';
my $partner = 'TEST';
my $msgid = '2013081107999999test';
my $DB_insert ='true';
my $filename = 'test_file';
my $line_count = 22;

if($DB_insert =~ /true/i) {
    my $DSN = "driver={SQL Server};server=$server_name;database=$datab
+ase_name;uid=$database_user;pwd=$database_pass;";  

    eval {
        my $dbh = DBI->connect("dbi:ODBC:$DSN");
        DBI->trace(2,'C:\db_test_log3.txt');        
        my $sql = "INSERT INTO RecordCount VALUES (?,?,?,?,?,?)";
        my $sth = $dbh->prepare($sql);
        $sth->bind_param(1, $env);
        $sth->bind_param(2, $partner);
        $sth->bind_param(3, $id);
        $sth->bind_param(4, $filename);
        $sth->bind_param(5, $temp_date);
        $sth->bind_param(6, $line_count);
    
        $sth->execute();
        $dbh->disconnect();
    };
    if($@) {        
        print "DB Failure: $@";
    }
}

exit 0;
[download]