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

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:[email protected]  http://www.foken.de/alexander/