If your query requires one parameter, and you are passing an array, it's correct that only the first is inserted.

What you need is to prepare once aand execute many times.

my $sth = $dbh->prepare( "INSERT INTO data_profile (file) VALUES (?)");
for (@array) {
    $sth->execute($_);
}

Alternatively, you can create a multiple insert query.

# assume that @array contains qw(abc def ghi);
my $query = 'INSERT INTO data_profile (file) VALUES ';
$query .= join(',' map {'(?)'} @array);

# now $query will be 'INSERT INTO data_profile (file) VALUES (?),(?),(?)'
$dbh->do($query, @array);

HTH

Re^2: perl and mysql parse a text file

Anonymous Monk

created: 2006-02-03 10:29:29

perlmonks

• reply
• node
• thread
• author

• node
• author
• thread

prlmnks

• node
• thread
• author

this would create a separate DB row for each file row? Is it possible to store the entire file in a single row?

Re^3: perl and mysql parse a text file

rnahi

created: 2006-02-03 11:17:20

perlmonks

• reply
• node
• thread
• author

• node
• author
• thread

prlmnks

• node
• thread
• author

Well, if you say "parse" a text file and then use an "@array", it seemed logical to assume that you wanted separate records.

With this new piece of information at hand, Fletch's post meets your needs.

I recommend you read this useful node and this insightful article (off site) before you post your next request.

my $sth=$dbh->prepare("INSERT INTO data_profile VALUES (?));
while(){
   $sth->execute($_)or die $dbh->errstr;
}
__DATA__
1   10
2   20

Read the entire contents into a single scalar (my $contents = do { local $/; };) and pass that to the database using an insert statement you've prepared. See the Handling BLOB / LONG / Memo Fields in the DBI documentation.

Fletch's answer above is a good way to go. If you need the rows in an array for some other reason, you can always join them before you insert, something like this:

@array = ;
# Other stuff with @array ...
$sth = $dbh->prepare('insert into data_profile (file) values (?)');
$sth->execute(join '' => @array);