Trying to get a "simple" script to work

[Larry W. Virden]

My script is intended to update 3 columns in a particular row of a table.
My script is:
#!/usr/local/perl5/bin/perl -w

use strict;
use DBI;

my $alt_emp_no = 60017; # list of employee ids
my $first_name ="Neil";
my $middle_name = "W";
my $last_name ="Han";

my $conn_data="/home/larry/connection_data";
open (CONNECTION_DATA, $conn_data)
or die "Failed to open connection data file\n";
my $orasid = <CONNECTION_DATA>;
my $user_name = <CONNECTION_DATA>;
my $password = <CONNECTION_DATA>;

close (CONNECTION_DATA);

chomp ($orasid);
chomp ($user_name);
chomp ($password);

my $oraProdDBH = DBI->connect("dbi:Oracle:", $user_name, $password)
or die "Failed to connect to $DBI::errstr\n";

$oraProdDBH->{RaiseError} = 1;
$oraProdDBH->{AutoCommit} = 0;

# Update a name of a user not in Ceridian.

print "UPDATE CSI_HR SET FIRST_NAME=$oraProdDBH->quote($first_name) ,
MIDDL
E_NAME=$oraProdDBH->quote($middle_name) ,
LAST_NAME=$oraProdDBH->quote($last_nam
e) , REC_MOD_DATE=SYSDATE WHERE ALT_EMP_NO = $alt_emp_no \n";

$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"
) or die "Failed to update csi_hr; $DBI::errstr\n";

$oraProdDBH->commit();
$oraProdDBH->disconnect;


When I run this script, I get the error:

UPDATE CSI_HR SET FIRST_NAME=DBI::db=HASH(0x221d94)->quote(Neil) ,
MIDDLE_NAME=DBI::db=HASH(0x221d94)->quote(W) ,
LAST_NAME=DBI::db=HASH(0x221d94)->quote(Han) , REC_MOD_DATE=SYSDATE WHERE
ALT_EMP_NO = 60017
DBD::Oracle::db do failed: ORA-00933: SQL command not properly ended (DBD
ERROR: error possibly near <*> indicator at char 32 in 'UPDATE CSI_HR SET
FIRST_NAME=DBI<*>::db=HASH(0x221d94)->quote(Neil) ,
MIDDLE_NAME=DBI::db=HASH(0x221d94)->quote(W) ,
LAST_NAME=DBI::db=HASH(0x221d94)->quote(Han) , REC_MOD_DATE=SYSDATE WHERE
ALT_EMP_NO = 60017') [for Statement "UPDATE CSI_HR SET
FIRST_NAME=DBI::db=HASH(0x221d94)->quote(Neil) ,
MIDDLE_NAME=DBI::db=HASH(0x221d94)->quote(W) ,
LAST_NAME=DBI::db=HASH(0x221d94)->quote(Han) , REC_MOD_DATE=SYSDATE WHERE
ALT_EMP_NO = 60017"] at ./update_hrname.pl line 55.

I have tried with and without a newline, with and without a semicolon.

I have other code which is nearly identical to this (other columns being
updated, but basically the same code.

I am assuming that I have made some hair brained mistake - for that I
apologize.
I just haven't been able to understand what I need to do to fix this.

I am hoping some kind person might explain the fix in a way that I can
understand.

[Bruce Johnson]

Post by Larry W. Virden
$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

[Larry W. Virden]

I wanted to thank you all again for your help. I used Mr. Johnson's
approach and it worked great.

[Alexander Foken]

Hello!

Look at the error message. The $dbh object was stringified to
HASH(0x221d94). Method calls aren't interpolated in double quoted
strings as you expect. There are ways to work around that, but you do
not want that. Don't mix SQL commands and values. Don't use the quote()
method. Use placeholders. Placeholders are more efficient and avoid SQL
injection. See http://bobby-tables.com/ Think of quote() as a method
used only internally by DBI, if used at all. There are nearly no cases
where you need to quote manually. More:
http://perlmonks.org/?node_id=930038,
http://perlmonks.org/?node_id=811732, http://perlmonks.org/?node_id=839078

No semicolon at the end of the SQL statement, that's only for sqlplus
and similar tools.

Don't check manually for errors, use RaiseError in connect().
$oraProdDBH->do(...) in your code will die on errors because you set
RaiseError after connect(), no need to check for errors manually there.

You may want to check that the expected number of rows was changed.
Remember that DBI may return -1 for an unknown number of rows, and "0E0"
to indicate 0 rows, but still return a true value (i.e. "or die" won't
work to check that at least one row was changed).

You want something like this:

...
my $oraProdDBH = DBI->connect("dbi:Oracle:", $user_name,
$password,{RaiseError =>1, AutoCommit => 0});
...
$oraProdDBH->do(
"UPDATE CSI_HR SET FIRST_NAME=?, MIDDLE_NAME=?, LAST_NAME=?,
REC_MOD_DATE=SYSDATE WHERE ALT_EMP_NO=?",
undef,
$first_name,$middle_name,$last_name,$alt_emp_no
);
...


Alexander

Post by Larry W. Virden
My script is intended to update 3 columns in a particular row of a table.
#!/usr/local/perl5/bin/perl -w
use strict;
use DBI;
my $alt_emp_no = 60017; # list of employee ids
my $first_name ="Neil";
my $middle_name = "W";
my $last_name ="Han";
my $conn_data="/home/larry/connection_data";
open (CONNECTION_DATA, $conn_data)
or die "Failed to open connection data file\n";
my $orasid = <CONNECTION_DATA>;
my $user_name = <CONNECTION_DATA>;
my $password = <CONNECTION_DATA>;
close (CONNECTION_DATA);
chomp ($orasid);
chomp ($user_name);
chomp ($password);
my $oraProdDBH = DBI->connect("dbi:Oracle:", $user_name, $password)
or die "Failed to connect to $DBI::errstr\n";
$oraProdDBH->{RaiseError} = 1;
$oraProdDBH->{AutoCommit} = 0;
# Update a name of a user not in Ceridian.
print "UPDATE CSI_HR SET
FIRST_NAME=$oraProdDBH->quote($first_name) , MIDDL
E_NAME=$oraProdDBH->quote($middle_name) ,
LAST_NAME=$oraProdDBH->quote($last_nam
e) , REC_MOD_DATE=SYSDATE WHERE ALT_EMP_NO = $alt_emp_no \n";
$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"
) or die "Failed to update csi_hr; $DBI::errstr\n";
$oraProdDBH->commit();
$oraProdDBH->disconnect;
UPDATE CSI_HR SET FIRST_NAME=DBI::db=HASH(0x221d94)->quote(Neil) ,
MIDDLE_NAME=DBI::db=HASH(0x221d94)->quote(W) ,
LAST_NAME=DBI::db=HASH(0x221d94)->quote(Han) , REC_MOD_DATE=SYSDATE
WHERE ALT_EMP_NO = 60017
DBD::Oracle::db do failed: ORA-00933: SQL command not properly ended
(DBD ERROR: error possibly near <*> indicator at char 32 in 'UPDATE
CSI_HR SET FIRST_NAME=DBI<*>::db=HASH(0x221d94)->quote(Neil) ,
MIDDLE_NAME=DBI::db=HASH(0x221d94)->quote(W) ,
LAST_NAME=DBI::db=HASH(0x221d94)->quote(Han) , REC_MOD_DATE=SYSDATE
WHERE ALT_EMP_NO = 60017') [for Statement "UPDATE CSI_HR SET
FIRST_NAME=DBI::db=HASH(0x221d94)->quote(Neil) ,
MIDDLE_NAME=DBI::db=HASH(0x221d94)->quote(W) ,
LAST_NAME=DBI::db=HASH(0x221d94)->quote(Han) , REC_MOD_DATE=SYSDATE
WHERE ALT_EMP_NO = 60017"] at ./update_hrname.pl
<http://update_hrname.pl> line 55.
I have tried with and without a newline, with and without a semicolon.
I have other code which is nearly identical to this (other columns
being updated, but basically the same code.
I am assuming that I have made some hair brained mistake - for that I
apologize.
I just haven't been able to understand what I need to do to fix this.
I am hoping some kind person might explain the fix in a way that I can
understand.

[Larry W. Virden]

Thank you all so much. When I get back to my keyboard on Monday I will
change my code as recommended.

I really appreciate your help