perl update oracle database with values from another database

Question

I am writing a perl script to update a table in an oracle database with data from a mysql database.

I am new to perl so any help would be appreciated.

I currently have the following which does not update the oracle database but also does not throw any errors.

The databases have both been initialised.

I would like the oracle tblrecommendations table to have the performance updated with whats in the mysql tblrecommendations table.

Thanks in advance.

#transfer data
sub do_crc_company_performance {

my ($sth_mysql, $sth_oracle);
my $sql_details = <<END_SQL;
select
  tblRecommendations.code,
  tblRecommendations.performance
from 
  crc.tblRecommendations
where
  length(tblRecommendations.code) = '3'
END_SQL

# variables to bind values to

my ($code, $performance);

eval {
    # prepare our select statement for mysql
    $sth_mysql = $dbh_mysql->prepare($sql_details);
    $sth_mysql->execute;
    $sth_mysql->bind_columns(\($code, $performance));
    # create oracle insertion query
    $sth_oracle = $dbh_oracle->prepare(q{UPDATE TBLRECOMMENDATIONS
                                        SET PERFORMANCE = '$performance'
                                        WHERE CODE = '$code'});
    while ( $sth_mysql->fetch ) {
        $performance = Encode::decode_utf8($performance); # set the flag
        # feed the data into the tblRecommendations table
        $sth_oracle->execute();
    }
};

if ($@) {
    # what went wrong
    push (@errors, "Unable to update company details: $@");
    # rollback our transaction
    $dbh_oracle->rollback()
} 
$sth_oracle->finish if ($sth_oracle);
$sth_mysql->finish if ($sth_mysql);
}

score 3 · Accepted Answer · edited May 23 '17 at 10:31

Your problem is your q{} quoting, which is literal string quoting with no interpolation. Thus, you are searching for records where the code field is set to the five character literal string value $code.

One solution would be to quote with interpolation — either "" or qq{}. However, this is prone to unpleasant SQL injection and thus strongly discouraged.

A better solution, as you discovered, is to use bind values and let the RDBMS driver take care of quoting and escaping for you. However, you do not need an intermediary $sth in this case:

$dbh_ora->do(q{UPDATE tbl SET foo = ? WHERE bar = ?}, undef, $new_foo, $bar);

Now, I infer that you have RaiseError set (good!), and you don't care about the number of rows UPDATEd, so you don't even need to capture the return value of that call to do().

score 1 · Answer 2 · answered Oct 24 '13 at 20:29

For anyone interested in the final solution that worked for me, here it is.

sub do_crc_company_performance {

my ($sth_mysql, $sth_oracle);
my $sql_details = <<END_SQL;
select
  tblRecommendations.code,
  tblRecommendations.performance
from 
  crc.tblRecommendations
where
  length(tblRecommendations.code) = '3'
END_SQL


# variables to bind values to
my ($code, $performance);

eval {

    # prepare our select statement for mysql
    $sth_mysql = $dbh_mysql->prepare($sql_details);
    $sth_mysql->execute;
    $sth_mysql->bind_columns(\$code, \$performance);
    # create oracle insertion query

    while ( $sth_mysql->fetch ) {
        $performance = Encode::decode_utf8($performance); # set the flag
        # feed the data into the tblRecommendations table
        $sth_oracle = $dbh_oracle->do('UPDATE tblrecommendations SET performance = ? WHERE code = ?', undef, $performance, $code);
    }
};

if ($@) {
    # what went wrong
    push (@errors, "Unable to update company details: $@");
    # rollback our transaction
}

}

score 0 · Answer 3 · answered Oct 24 '13 at 00:49

0

I don't see a COMMIT in your code, which is necessary to make your changes permanent. Somewhere in there (either after each insert or after the fetch loop) you want:

$sth_oracle->commit;

answered Oct 24 '13 at 00:49

DCookie

42,630
11
83
92

Autocommit is enabled. I removed the commit to get rid of the warning message each time the script is run. – JordanC Oct 24 '13 at 01:01
Found a way to do this. "$sth_oracle = $dbh_oracle->do('UPDATE tblrecommendations SET performance = ?, updated = ? WHERE code = ?', undef, $performance, $updated, $code);" It may not be the best but it works. I'll post tomorrow when I am able to answer my own question. – JordanC Oct 24 '13 at 01:31

perl update oracle database with values from another database

3 Answers3