Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

Re: Re: Re: Placeholders, safety and the relative unimportance of efficiency

by Zo (Scribe)
on Apr 23, 2002 at 18:14 UTC ( #161387=note: print w/replies, xml ) Need Help??


in reply to Re: Re: Placeholders, safety and the relative unimportance of efficiency
in thread SQL in Perl and setting variables

Here's my latest attempt, but I keep getting the error:
ORA-01008 cannot bind variable or I would get invalid column name
I thought maybe someone could look and have some input on this... where I might be at fault.. and I will still play around...
Thanks in advance, Zo.
#!perl -w use strict; use DBI; # output file my $newfile = "./test_output.txt"; open(BATCH, ">>$newfile") || die "Can't open $newfile : the test_outpu +t.txt file. $!"; # sets and prints the system date to the log file my $datestamp = `Date /T`; print BATCH $datestamp; # initialize the variable to count the batch lines created my $linecount = 0; # connection to database my $dbh = DBI->connect("dbi:Oracle:CS1", "admin", "admin123"); my $sql = "SELECT batch_line, rowidtochar(rowid) FROM batch_lines"; my $sth = $dbh->prepare($sql); $sth->execute(); my ($batch, $row_id); $sth->bind_columns(\$batch, \$row_id); my $sth2 = $sth; my $count=1; while($sth->fetch()) { ##### test print # print BATCH "$row_id <$count>"; # print "$row_id <$batch>\n"; # $count++; ##### $batch=~s/p12/servername/g; $batch=~s/D:/\nD:/g; $batch=~s/%/%%/g; # write to the file my $timestamp = `Time /T`; chop $timestamp; ##### test print print BATCH "$row_id <$count> $batch"." ".$timestamp; ##### # print BATCH $batch." ".$timestamp; # writes to screen print $batch; # counts the batch line being read $linecount++; ##### test count $count++; ##### # executes the batch line # system($batch); ##### update print BATCH "\n^^^^^^^^^^^^^^$row_id^^^^^^^\n"; my $bsql = "UPDATE batch_lines SET exe_c = 'Y' WHERE exe_c = 'N +' AND rowid = chartorowid($row_id) "; $sth2= $dbh->prepare($bsql); $sth2->execute(); ##### print BATCH "\n########### $row_id #########\n" } $sth->finish(); $sth2->finish(); # close oracle connection $dbh->disconnect(); # print count of batch lines print BATCH "\nLines extracted this period: ".$linecount."\n"; # close new .txt file close (BATCH);
  • Comment on Re: Re: Re: Placeholders, safety and the relative unimportance of efficiency
  • Download Code

Replies are listed 'Best First'.
Re: Re: Re: Re: Placeholders, safety and the relative unimportance of efficiency
by runrig (Abbot) on Apr 24, 2002 at 03:01 UTC
    I guess the table doesn't have a primary key? Also you should use RaiseError or else check the status of every DBI statement. Anyway you're probably being redundant by specifying 'where rowid = .. and exe_c = ..'. And you can still use placeholders(psuedocode ahead):
    my $dbh = DBI->connect('...', 'user','passwd', {PrintError=>0, RaiseError=>1}); my $sql = <<EOT; select ... from .. where exe_c = 'N' EOT my $sel_h = $dbh->prepare($sql); $sql = <<EOT; update ... set exe_c = 'N' where rowid = ? EOT my $upd_h = $dbh->prepare($sql); $sel_h->execute; $sel_h->bind_columns(...); while ($sel_h->fetch) { ... $upd_h->execute($rowid); } $dbh->disconnect;
    ------------
    ooo  O\O  ooo tilly was here :,(
    

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://161387]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (11)
As of 2019-07-23 13:54 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    If you were the first to set foot on the Moon, what would be your epigram?






    Results (27 votes). Check out past polls.

    Notices?