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
On 20.02.2015 21:01, Larry W. Virden wrote:
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
<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.
--
Alexander Foken
mailto:alexan...@foken.de http://www.foken.de/alexander/
