Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses

Re: Followup on executing multiple mysql commands in one call

by Neighbour (Friar)
on May 31, 2012 at 07:46 UTC ( #973452=note: print w/replies, xml ) Need Help??

in reply to Followup on executing multiple mysql commands in one call

Yes, there is :)
For example:
open(INPUT, '<', 'Path/file_with_updates.sql') or die ("Error opening +file with updates: " . $!); while (<INPUT>) { $dbh->do($_) or die("Error executing query [$_]: " . $dbh->errstr) +; } close(INPUT);
Do note that this is a rather blatant security risk, since you're executing whatever's in those files directly in the database, but that's your choice. Since SET-commands retain their value during the session, it's safe to just call $dbh->do on each retrieved line.
However, a better way would be to generate the queries in your perl script and use placeholders. For example:
my $sth = $dbh->prepare('UPDATE Games SET Away_Team_ID=(SELECT Team_ID + FROM Teams WHERE Name = ?) WHERE Date = ?') or die("Error preparing +statement: " . $dbh->errstr); open(INPUT, '<', 'Path/file_with_data') or die ("Error opening file wi +th updates: " . $!); while (<INPUT>) { my ($teamname,$gamedate) = split(','); $sth->execute($teamname, $gamedate) or die("Error executing update +: " . $dbh->errstr); } close(INPUT);
This assumes an inputfile which consists (on each line) of a team name, followed by a comma, and then a date in a format that your used database can grok.

Replies are listed 'Best First'.
Re^2: Followup on executing multiple mysql commands in one call
by membender (Novice) on May 31, 2012 at 08:30 UTC

    Thank you Neighbour (and Corion) for all your help. I will check all your suggestions out to see what works best.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://973452]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (4)
As of 2018-04-24 16:58 GMT
Find Nodes?
    Voting Booth?