Thanks for your input kennethk! First of all 2 & 3 were just basic testing when I first attempted this task and should have removed it when I included here as a question. I agree with 1 and 5, thats where my knowledge lacked, I have since implemented your suggestion. As for 4, I have tried to learn some more about Binding values and been trying for a good chunk of today to get it to work but keep getting different errors: Invalid character value for cast specification & Invalid string or buffer error (see below). I kinds know the former is relating to the date field but haven't figured it out yet, I am lost on the latter.

After looking up Binding values, I understand that its inefficient using interpolating values especially when executing the script multiple times as it doesnt use cached info but besides the inefficiency are there other drawbacks to interpolating? I would prefer to Bind but having spent a significant time troubleshooting the errors I am ready to give in, hence trying to evaluate the risk.

Also, the errors I get do not seem to be caught by the exception handler. Any insight on that?

DBD::ODBC::st execute failed: MicrosoftODBC SQL Server DriverInvalid charact er value for cast specification (SQL-22018) at C:\db_eval_test.pl line 67.

DBD::ODBC::st execute failed: MicrosoftODBC SQL Server DriverInvalid string or buffer length (SQL-HY090) at C:\db_eval_test.pl line 65.

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_log.txt');

        my $sql = "INSERT INTO RecordCount VALUES (?,?,?,?,?,?)";    
                
        my $sth = $dbh->prepare($sql);
        $sth->bind_param(1, 'test_env');
        $sth->bind_param(2, $partner);
        $sth->bind_param(3, $id);
        $sth->bind_param(4, $filename);
        $sth->bind_param(5, "$date $time");
        $sth->bind_param(6, $line_count);
    
        $sth->execute();
        $dbh->disconnect();
    };
    if($@) {        
        print "DB Failure: $@";
    }
}
[download]

After looking up Binding values, I understand that its inefficient using interpolating values especially when executing the script multiple times as it doesnt use cached info but besides the inefficiency are there other drawbacks to interpolating?

While the efficiency gain is good, the primary reason I use placeholders is escaping. You can very easy corrupt data values through naive interpolation, and that doesn't even get into the security risk of SQL injection (oblig xkcd://327). It also makes debugging easier, since it uncouples the SQL syntax errors from the parameter value validity. I never interpolate values into my SQL.

Also, the errors I get do not seem to be caught by the exception handler. Any insight on that?

I'm glad you apparently tracked down your errors, though I am very surprised that the exception handler wasn't catching those expressions.

---

#11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.

I was able to fix the errors. The first error was because I had two separate values going in one bind "$date $time". I used a temp variable to store them and that worked. The second erorr was due to the string I was trying to Bind for the 1st value, it had "\" in it and that causing the string failure. See the revised code that works fine. Thanks for your help.

my $temp_date = $date.' '.$time;
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_log.txt');        

        if (!$dbh) {            #testing connection to DB
            print "Could not connect to database: $DBI::errstr";
        }
        
        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: $@";
    }
}
[download]