Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
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 having an uproarious good time at the Monastery: (10)
As of 2014-12-26 19:13 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (174 votes), past polls