|
in reply to Placeholders, safety and the relative unimportance of efficiency
in thread SQL in Perl and setting variables
I have at times listed all of the reasons to use placeholders. But depending on how you look at it, it still all comes down to efficiency. Its more efficient typing-wise to put some question marks in SQL statements and list some variables in the execute statement than to call $dbh->quote() on each one of your parameters. Its more efficient debugging-wise to use placeholders OR use $dbh->quote when you have to track down why input like "O'Connor" won't work or how someone snuck in a 'DROP TABLE' statement in the middle of your SQL statement. So nevermind that with placeholders, Oracle will only have to parse your statement once, even if you you run the script more than once (if the statement is still in the SQL cache), the fact remains that placeholders are still a good idea :) |
Re: Re: Placeholders, safety and the relative unimportance of efficiency
by Zo (Scribe) on Apr 22, 2002 at 18:51 UTC
|
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] |
|
|
