Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things

SQL in Perl and setting variables

by Zo (Scribe)
on Nov 20, 2001 at 00:06 UTC ( #126362=perlquestion: print w/replies, xml ) Need Help??

Zo has asked for the wisdom of the Perl Monks concerning the following question:

Hello all,
Here's the code:
$asql = "SELECT batch_line, row_id FROM test_batch_lines WHERE exe_c += 'N' "; $bsql = "UPDATE test_batch_lines SET exe_c = 'Y' WHERE exe_c = 'N' " A +ND row_id = '$XYZ' " ;

Now my question is, how do I load the variable XYZ with the data coming in from 'row_id' which was pulled in from the SELECT query?
This is just the basic part of a perl script that extracts and manipulates data from Oracle8 and prints out to a text file on a Win2000 server.
Thanks for all the help in advance.

Brother Zo.

Replies are listed 'Best First'.
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); }
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 .

    <megaphone> Throw down the gun and tiara and come out of the float! </megaphone>

      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...

      But this issue is extremely important. You will certainly want to be able to handle more realistic values like "O'Connor" and "Smith's Bakery". If you let DBI handle the quoting for you, you will save yourself a lot of trouble fixing annoying bugs.


        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."

        <megaphone> Throw down the gun and tiara and come out of the float! </megaphone>

      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 :)
        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);
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
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

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://126362]
Approved by root
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (3)
As of 2019-05-22 05:48 GMT
Find Nodes?
    Voting Booth?
    Do you enjoy 3D movies?

    Results (138 votes). Check out past polls.

    • (Sep 10, 2018 at 22:53 UTC) Welcome new users!