Re: SQL in Perl and setting variables
by runrig (Abbot) on Nov 20, 2001 at 00:24 UTC
|
You will want to use a placeholder on the update statement for efficiency (especially with Oracle): my $dbh = DBI->connect(...,{RaiseError=>1});
$asql = <<EOT;
SELECT batch_line, row_id
FROM test_batch_lines
WHERE exe_c = 'N'
EOT
my $sel_h = $dbh->prepare($asql);
$bsql = <<EOT;
UPDATE test_batch_lines
SET exe_c = 'Y'
WHERE exe_c = 'N'
AND row_id = ?
EOT
my $upd_h = $dbh->prepare($bsql);
$sel_h->execute;
$sel_h->bind_columns(\my ($batch_line, $row_id));
while ($sth->fetch) {
# ...
$upd_h->execute($row_id);
}
| [reply] [Watch: Dir/Any] [d/l] |
Placeholders, safety and the relative unimportance of efficiency
by petdance (Parson) on Nov 20, 2001 at 01:59 UTC
|
Use placeholders, but not because they're more efficient.
Use them because they're safer.
What if the value of $XYZ is a single quote? Your SQL will
no longer parse. You either have to escape the quotes
in $XYZ, or use placeholders.
The efficiency issues mentioned above are true, but not
worth worrying about right now. The gains from using
placeholders are likely to
be small. Chances are that the bottleneck on the program
will be the DB updating itself, rather than the parsing.
As an aside, I despair of the flag of efficiency being
raised at every opportunity, to the exclusion of
correctness and safety. See
A Tirade Against The Cult Of Performance
.
xoxo,
Andy
--
<megaphone>
Throw down the gun and tiara and come out of the float!
</megaphone>
| [reply] [Watch: Dir/Any] |
|
| [reply] [Watch: Dir/Any] |
|
Some people will probably naively ignore this advice because they think that it's unlikely that their program will ever use a
value of just a single quote...
You can always tell the inexperienced programmers. They
say things like "Oh, that'll never happen."
xoxo,
Andy
--
<megaphone>
Throw down the gun and tiara and come out of the float!
</megaphone>
| [reply] [Watch: Dir/Any] |
|
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 :)
| [reply] [Watch: Dir/Any] |
|
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] [Watch: Dir/Any] [d/l] |
|
|
|
|
Re: SQL in Perl and setting variables
by filmo (Scribe) on Nov 20, 2001 at 00:33 UTC
|
Use the DBI module and placeholders ("?") in your SQL statements. Any routine that updates recursively will execute faster if done using placeholders as opposed to preparing the statement over and over again.
use DBI;
DBI connection stuff here...
# no error handling code included in this sample
$sth = $dbh->prepare(qq|SELECT row_id FROM test WHERE exe='N'|);
$rows = $sth->execute();
$sth2 = $dhb->prepare(qq|UPDATE test SET exe = 'Y' WHERE row_id = ?|);
while ($row_id = $sth->fetchrow_array()) {
# now execute the 2nd statement replacing the
# placeholder with the variable $row_id
$sth2->execute($row_id);
}
$sth->finish();
$sth2->finish();
$dbh->disconnect();
--
Filmo the Klown | [reply] [Watch: Dir/Any] [d/l] |
Re: SQL in Perl and setting variables
by hopes (Friar) on Nov 20, 2001 at 00:26 UTC
|
It depends of how do you want to manage your database.
If you want a portable code, you should use DBI
You can also search for an ODBC driver for Oracle and then use Win32::ODBC or Win32::OLE and Win32::ADO.
You can get info about recorsets and so on to extract your data.
See DBI, and Win32::ODBC and Win32::ADO and Win32::OLE or make a SuperSearch on this terms
Remember that if you use activestate perl, you should install DBI if you want to use it. (It is not part of the distribution)
Hope this helps
Hopes | [reply] [Watch: Dir/Any] |