Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister

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.

Replies are listed 'Best First'.
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?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://973452]
choroba misses the context
[Corion]: choroba: Optimizing how Perl reads source code on startup (and then processes it line-by-line)
[Corion]: But in general, it seems to be an interesting approach I should think about - whenever I'm searching for something, to consider if I could search for the end of the token instead of the start of the token

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (9)
As of 2016-12-06 15:04 GMT
Find Nodes?
    Voting Booth?
    On a regular basis, I'm most likely to spy upon:

    Results (108 votes). Check out past polls.