Script for updating DB

Question

I have the following piece of perl script for updating Oracle Database. It's working perfectly fine; but, I want to know how to simplify this script:

use DBI;
my $dbh;
my $bad_form = "text.txt";

open (OUT, $bad_form) or die "Could not open $bad_form";
while (<OUT>){
    chomp $_;
    my ($item_id, $description, $form_id_1, $form_id_3) = split(/\|/, $_);

    my $new_form_id_1 = form_1($form_id_1);
    my $new_form_id_3 = form_3($form_id_3);

    if ( $new_form_id_1 ne 'null' ){
            my $sql = "update item_table set form_id_1 = '$new_form_id_1' where item_id = '$item_id'";
            my $item = $dbh->prepare($sql) or (log_error("Couldn't prepare statement: " . $dbh->errstr) and return 2);
            $item->execute() or (log_error("Couldn't execute statement: " . $item->errstr) and return 2);
    }else{
            $form_id_1 = "correct value";
            $new_form_id_1 = "no updated";
    }

    if ( $new_form_id_3 ne 'null' ){
            my $sql = "update item_table set form_id_3 = '$new_form_id_3' where item_id = '$item_id'";
            my $item = $dbh->prepare($sql) or (log_error("Couldn't prepare statement: " . $dbh->errstr) and return 2);
            $item->execute() or (log_error("Couldn't execute statement: " . $item->errstr) and return 2);

    }else{
            $form_id_3 = "correct value";
            $new_form_id_3 = "no updated";
    }
}

Where function form_1() and form_3() are used to generated new value for form_id_1,3.

Any help would be appreciated. Thank you a lot for your help.

I'm voting to close this question as off-topic because it belongs on Code Review SE — Matt Jacob
– Matt Jacob, Commented Dec 14, 2016 at 6:40

Vesa Karjalainen · Accepted Answer · 2016-12-14 08:17:01Z

1

use strict;

Move your two prepare()s out of the while loop, using ? as placeholder. Then call only execute($new_form_id_x, $item_id) for the appropriate prepared statement handle inside the loop.

answered Dec 14, 2016 at 8:17

Vesa Karjalainen

1,1058 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Script for updating DB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related