Re: SQL in Perl and setting variables


There's more than one way to do things
	PerlMonks

Re: SQL in Perl and setting variables

by filmo (Scribe)

on Nov 20, 2001 at 00:33 UTC ( [id://126379]=note: print w/replies, xml )

Need Help??

in reply to SQL in Perl and setting variables

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();
[download]

--
Filmo the Klown