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);
| [reply]
[d/l] |
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);
| [reply]
[d/l] |
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);
| [reply]
[d/l] |
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 :,(
| [reply]
[d/l] |
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.
| [reply] |