Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

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.


Comment on Re: Followup on executing multiple mysql commands in one call
Select or Download Code
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?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://973452]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (6)
As of 2015-07-04 10:13 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (59 votes), past polls