Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
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 (Priest) 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 meditating upon the Monastery: (9)
As of 2014-11-20 21:43 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My preferred Perl binaries come from:














    Results (102 votes), past polls