Followup on executing multiple mysql commands in one call

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.
[download]

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

Replies are listed 'Best First'.
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);` [download] 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);` [download] 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.	[reply] [d/l] [select]
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.	[reply]
Re: Followup on executing multiple mysql commands in one call by Corion (Patriarch) 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.	[reply] [d/l] [select]
Re: Followup on executing multiple mysql commands in one call by poj (Abbot) 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');` [download] poj	[reply] [d/l]