Perl: Breaking out of foreach loop when last array element is encountered

Question

Perl noob here. I have a small script (see below) that I'm using to build a MySQL INSERT statement.

use strict;

my @records = qw/Record1 Record2 Record3/;

my $insert = "
INSERT INTO table
VALUES
 ";

foreach my $record (@records) {
        $insert .= "('" . $record . "'),\n ";
}

print "$insert\n";

Current Output

INSERT INTO table
VALUES
 ('Record1'),
 ('Record2'),
 ('Record3'),

I need to know how to break at the last element of the @records array and append a ; instead of ,

Desired Output

INSERT INTO table
VALUES
 ('Record1'),
 ('Record2'),
 ('Record3');

No this was just a simplified example. The actual array will come from anyone of a number of different sources and the array length will vary. — user3320741, Feb 17 '14 at 21:47
Check here: http://stackoverflow.com/questions/303216/how-do-i-break-out-of-a-loop-in-perl You can make a call to last; — Jeef, Feb 18 '14 at 10:52

simbabque · Accepted Answer · 2014-02-18T08:01:52.190

7

You can do that with map and join.

my @records = qw/Record1 Record2 Record3/;

my $insert = "
INSERT INTO table
VALUES
 ";

$insert .= join ',', map { '(' . $dbh->quote($_) . ')' } @records;
$insert .= ';'; # this line is not needed

The quote method of $dbh is better than just putting the stuff into quotes because it handles bad stuff for you. The map is not much different from a foreach loop, and the join will take care of putting the commas in between the elements, and not the last one.

edited Feb 18 '14 at 08:01

answered Feb 17 '14 at 21:47

simbabque

53,749
8
73
136

That definitely appears to be a more elegant solution, however, now I receive the error `Global symbol "$record" requires explicit package name`. Where is the `$record` variable defined? – user3320741 Feb 17 '14 at 22:01
Thanks for fixing @tobyink. I was about to do that. – simbabque Feb 17 '14 at 22:02
I think `$record` should have been `$_`, so have edited the answer accordingly. – tobyink Feb 17 '14 at 22:02
1

The `$insert .= ';';` can be omitted entirely. – ikegami Feb 17 '14 at 22:15
@ikegami true. I put it there explicitly because OP has it in the desired output. – simbabque Feb 18 '14 at 07:11

score 1 · Answer 2 · answered Feb 18 '14 at 09:10

1

On a related matter, I always try to avoid putting data and sql statements on the same line, thus minimize the risk of sql injection. In perl you have a prepare/execute mechanism available for this:

my @records = qw/Record1 Record2 Record3/;
$sth = $dbh->prepare("INSERT INTO table VALUES ?");

foreach my $record (@records) {
      $sth->execute($record);   
}

http://bobby-tables.com/perl.html

answered Feb 18 '14 at 09:10

Simson

3,373
2
24
38

While this is good advice in general, it does not work for this question. OP asks specifically how to get all rows into one `INSERT` statement. For a very large amount of data that has severe performance implications, because we have one transaction versus a lot. `prepare` internally uses `quote` on each `?` value, and that's what needs to be done if the number of columns (or rows) is variable. – simbabque Feb 18 '14 at 11:57

Perl: Breaking out of foreach loop when last array element is encountered

2 Answers2