Hi,
I am converting oraperl statments to the new Perl-DBI format and do not
entirely understand the Perl-DBI syntax. I have included code with the
oraperl method and what I think is the correct Perl-DBI method. My 2
questions are:
# is $INSERT correct as written or should it be ...values ( ?, ?, ?, ...) ?
# is the insert request $csrd->execute... correct as written ?
Thanks,
Sid Katz
-------------- PERL CODE ----------------------------------
$DBASE = $ENV{"ORACLE_SID"};
$USER = 'wd53sk' ;
$PASSWD = 'wd53sk' ;
$TABLE = 'dlyprcp';
# former connect using oraperl
$lda = &ora_login($DBASE, $USER, $PASSWD) || die "$ora_errstr\n";
$on = 1 ;
&ora_autocommit($lda,$on) || die "$ora_errstr\n";
# new connect using perl-dbi
my $data_source = “dbi:Oracle:$DBASE”;
my $lda = DBI ->connect ($data_source, $USER, $PASSWD,
{ RaiseError =>1, AutoCommit => 0} )
|| die “Database connection not made: $DBI::errstr\n” ;
# insert command using oraperl....
$INSERT = "insert into $TABLE values (:1, :2, :3, :4, :5, :6, :7, :8, " ;
$INSERT = $INSERT.":9, :10, :11, :12, :13)" ;
$csrd = &ora_open($lda, $INSERT) || die "$ora_errstr\n" ;
# new insert command using perl-dbi
my $csrd = $lda->prepare( qw{$INSERT}) ||"Can't prepare stmt: $DBI::errstr\n” ;
while (<>)
{ chop;
($id, $yr, $mn, $dy, $hr, $mi, $p1, $p2, $dur, $tc, $sc, $ec, $j1,
$val, $qual, $rv, $org) = split(" ",$_) ; # split record into variables
if ($yr > $curyr)
{ $yr = $curyr; } ;
$obsdate = ($yr*10000) + ($mn*100) + $dy ; # create observation date
# in form yyyymmdd
$obstime = $hr * 100 + $mi ; # create obs hour in form hhmm
$elemcod = $p1.$p2 ;
# try insert operation using oraperl
&ora_bind($csrd,$obsdate,$id,$obstime,$elemcod,$val,$rv,$dur,
$tc,$sc,$ec,$org,'0',$qual) ;
# try insert operation using perl-dbi
$csrd->execute($obsdate,$id,$obstime,$elemcod,$val,$rv,$dur,$tc,$sc,$ec,$org,'0',$qual)
;
# rest of commands here, but deleted for brevity...