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

Followup on executing multiple mysql commands in one call

by membender (Novice)
on May 31, 2012 at 01:29 UTC ( #973409=perlquestion: print w/ replies, xml ) Need Help??
membender has asked for the wisdom of the Perl Monks concerning the following question:

I have pre-existing "batch" files of many mysql UPDATE commands, which are interspersed with commands which set temporary mysql variables using SET @vid:= ... ;

I would like to send a string containing many or all of these batched commands without having to do perl variable assignment in between and/or having to execute each UPDATE command individually. Better and more efficient to have the DB set the variables and execute all the commands at once than perform all the back-and-forthing in the other method.

As an example,

SET @tid:=(SELECT Team_ID FROM Teams WHERE Name='Thunder'); UPDATE Games SET Away_Team_ID=@tid WHERE Date='2012-05-29'; SET @tid:=(SELECT Team_ID FROM Teams WHERE Name='Spurs'); UPDATE Games SET Home_Team_ID=@tid WHERE Date='2012-05-29'; UPDATE ... ; UPDATE ... ; SET ... ; etc.

Is there a way to put all the statements in the above code into a perl string and call prepare() and execute() on that string and have mysql do all the work? It is possible and easy using phpmyadmin -- just paste all of the statements into the SQL textarea and they are ALL executed in "batch" mode on Submit. Note that I am not looking for any record sets in return during this phase.

I hope this clears things up a bit and still expect that there is a solution. Thank you (again).

Comment on Followup on executing multiple mysql commands in one call
Download Code
Re: Followup on executing multiple mysql commands in one call
by Neighbour (Friar) on May 31, 2012 at 07:46 UTC
    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.

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

Re: Followup on executing multiple mysql commands in one call
by Corion (Pope) on May 31, 2012 at 07:57 UTC

    As long as your SQL file does not contain trigger declarations, you can also use DBIx::RunSQL to run SQL. This assumes that the MySQL server retains variables set by the "SET" command and these are not a feature of the mysql command line program. If these are a feature of the mysql command line program, then just run your SQL through that instead.

Re: Followup on executing multiple mysql commands in one call
by poj (Curate) on May 31, 2012 at 08:02 UTC
    Use a stored procedure like this maybe,
    my $create_proc_upd = <<'eof'; CREATE PROCEDURE upd() BEGIN SET @tid:=(SELECT Team_ID FROM Teams WHERE Name='Thunder'); UPDATE Games SET Away_Team_ID=@tid WHERE Date='2012-05-29'; SET @tid:=(SELECT Team_ID FROM Teams WHERE Name='Spurs'); UPDATE Games SET Home_Team_ID=@tid WHERE Date='2012-05-29' END eof $dbh->do('DROP PROCEDURE IF EXISTS upd'); $dbh->do($create_proc_upd); $dbh->do('CALL upd');
    poj

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (9)
As of 2014-07-30 23:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (241 votes), past polls