Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

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

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


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

Hello again... OK.. it's been a long time since I've had to deal w/this issue, but now I have to finish up with it. I am going to post all the code from the script and then I will tell you what I need and can't figure out. I guess I'm what one might call a "code-rock"... just can't penetrate my brain w/the information!
I'm running on WindowsNT server w/Perl v.5.06... The script runs now, but I have to make it more specific to the data that is pulling in, more unique to the lines from the database. Again I have the Perl Cookbook and Perl in a Nutshell and have gone online to all sights figuring this out, and I humbley come here...

I connect no problem, extract what I need, then update. This all works. Now I need to select in 2 fields, not one.
I am selecting a field where exe_c = 'N'. Now I also need to select were exe_c = 'N' and rowid = ? (unknown). The rowid is the identifier generated in Oracle that I don't know, but is unique to the row that I'm pulling in. After the row is selected and worked on in the loop, I would like to update exe_c from 'N' to 'Y' where exe_c = 'n' and the rowid = ?.

$asql = "SELECT batch_line, rowid FROM xyz.batch_lines WHERE exe_c = 'N' ";

but how would I still do the work to the batch_line that I'm taking in AND take in the rowid as a variable (which is unknown) then use it (or bind it, which I tried but can't figure out) to the UPDATE statement?

$bsql = "UPDATE xyz.batch_lines SET exe_c = 'Y' WHERE exe_c = 'N' AND row_id = ? ";

The following is the script that I am currently running. Thank you for all the help in advance.
use DBI; use strict; # output file $newfile = "./batch_line_output.txt"; # open the new .txt file open(BATCH, ">>$newfile") || die "Can't open $newfile : the batch_line +_output.txt file. $!"; # sets and prints the system date to the log file $datestamp = `Date /T`; print BATCH $datestamp; # initialize the variable to count the batch lines created $linecount = 0; $asql = "SELECT batch_line FROM xyz.batch_lines WHERE exe_c = 'N' "; $bsql = "UPDATE xyz.batch_lines SET exe_c = 'Y' WHERE exe_c = 'N' "; # connect to oracle and extract data my $dbh1 = DBI->connect("dbi:Oracle:CS001", "admin", "admin123"); my $statement = $dbh1->prepare($asql); $statement ->execute(); while ($newfile= $statement->fetchrow_array) { $newfile=~s/p13/p12/g; $newfile=~s/D:/\nD:/g; $newfile=~s/%/%%/g; # write to the file $timestamp = `Time /T`; chop $timestamp; print BATCH $newfile." ".$timestamp; # writes to screen print $newfile; # counts the batch line being read $linecount++; # executes the batch line system($newfile); # Mark batch lines in table as read $statement2 = $dbh1->prepare($bsql); $statement2 ->execute(); } $statement->finish(); $statement2->finish(); # close oracle connection $dbh1->disconnect(); # print count of batch lines print BATCH $linecount; # close new .txt file close (BATCH);
  • Comment on Re: Re: Placeholders, safety and the relative unimportance of efficiency
  • Download Code

Replies are listed 'Best First'.
Re: Re: Re: Placeholders, safety and the relative unimportance of efficiency
by Zo (Scribe) on Apr 22, 2002 at 19:44 UTC
    I tried to put in the bind_column, but still not working... or I'm getting an error:
    Bind columns called with 2 refs when 1 needed.
    thanks again for any help!
    #!perl -w use DBI; # output file $newfile = "./output.txt"; # open the new .txt file open(BATCH, ">>$newfile") || die "Can't open $newfile : the output.txt + file. $!"; # sets and prints the system date to the log file $datestamp = `Date /T`; print BATCH $datestamp; # initialize the variable to count the batch lines created $linecount = 0; # variable with query to go against xyz.batch_lines table $asql = "SELECT batch_line FROM xyz.batch_lines WHERE exe_c = 'N' "; $bsql = "UPDATE cscadmin.crs_batch_lines SET exe_c = 'Y' WHERE exe_c = + ? "; # connect to oracle and extract data my $dbh1 = DBI->connect("dbi:Oracle:CS001", "admin", "admin123"); my $statement = $dbh1->prepare($asql); $statement->execute; $statement->bind_columns(\my ($batch_line, $row_id)); $statement->execute(); while ($newfile= $statement->fetchrow_array) { $newfile=~s/p13/p12/g; $newfile=~s/D:/\nD:/g; # $newfile=~s/\b5%\b/\b5%%\b/g; $newfile=~s/%/%%/g; # write to the file $timestamp = `Time /T`; chop $timestamp; print BATCH $newfile." ".$timestamp; # writes to screen print $newfile; # counts the batch line being read $linecount++; # executes the batch line # system($newfile); # Mark batch lines in table as read $statement2 = $dbh1->prepare($bsql); $statement2 ->execute($row_id); } $statement->finish(); $statement2->finish(); # close oracle connection $dbh1->disconnect(); # print count of batch lines print BATCH $linecount; # close new .txt file close (BATCH);
Re: Re: Re: Placeholders, safety and the relative unimportance of efficiency
by Zo (Scribe) on Apr 23, 2002 at 18:14 UTC
    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);
      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 :,(
      
Re: Re: Re: Placeholders, safety and the relative unimportance of efficiency
by Zo (Scribe) on Apr 26, 2002 at 13:47 UTC
    Hello..
    Just a 'thanks' to everyon for all the help with this. I've learned much. I have a working query now.
    brother monk Zo.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (6)
As of 2019-10-18 18:25 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?