Re: Trying to get a "simple" script to work

> On Feb 20, 2015, at 1:01 PM, Larry W. Virden <[email protected]> wrote:
> 
>    $oraProdDBH->do
>     (
>     "UPDATE CSI_HR SET FIRST_NAME=$oraProdDBH->quote($first_name)  , MIDDLE_NAME
> =$oraProdDBH->quote($middle_name) , LAST_NAME=$oraProdDBH->quote($last_name) , R
> EC_MOD_DATE=SYSDATE  WHERE ALT_EMP_NO = $alt_emp_no"

As the error indicates, perl is just doing substitution and sticking in a hash reference "DBI:db=HASH(0x221d94)->quote(Neil)” instead of what you want which is the value of the method.

You have to assign the value of the quote() method to a variable to do substitution that way:

my $qfn=$oraProdDBH->quote($first_name);
my $qmn=$oraProdDBH->quote($middle_name);
my $qln=$oraProdDBH->quote($last_name);
 $oraProdDBH->do
    (
    "UPDATE CSI_HR SET FIRST_NAME=$qfn  , MIDDLE_NAME
=$qmn , LAST_NAME=$qln , REC_MOD_DATE=SYSDATE  WHERE ALT_EMP_NO = $alt_emp_no"
    ) or die "Failed to update csi_hr; $DBI::errstr\n";

Alternatively you can use parameter substitution:

my $cursor=$oraProdDBH->prepare("UPDATE CSI_HR SET FIRST_NAME=? , MIDDLE_NAME
=? , LAST_NAME=? , REC_MOD_DATE=SYSDATE  WHERE ALT_EMP_NO = ?”);

$cursor->execute($first_name, $middle_name, $last_name, $alt_emp_no);

Which (IIRC) uses the $oraProdDBH->quote() method behind the scenes to escape things.

Which accomplishes the same thing as the ->do statement above, but using parameters instead, which makes the statement marginally faster to execute in oracle, and a lot faster if you’re doing a lot of ‘em because the statement plan gets calculated only once, then cached, like all queries with parameters.

-- 
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs