Perl inserting into MySQL DB

Question

What I have:

• MySQL DB
  
• A table with 9 columns.
• An array that gets generated with the values that need to get inserted into the table.

Note: The array that gets generated automatically will have a different length every time but the length will not exceed the number of columns that I have in the table.

The column names will be something like field1, field2, field3 etc. where the name will always being with the word field and then followed by a number.

I think this can be done using Perl's map function but I'm not that good with using this function and need some guidance.

I'd like to do something like this:

while ( (@Row) = $sql_stmt_h->fetchrow_array() ) {
 my $sql="
     INSERT INTO tablename (field$x) 
     VALUES (map function here ... which also needs to increment the $x in field$x so that it moves onto the next column name which would be field2 if we put the first value in field1. )";
}

Answer 1

Create your Insert statement to insert to all fields using ? placeholders for the data.

my $sql="
 INSERT INTO tablename (field1 field2 field3 field4 field5 field6 field7 field8 field9 ) 
 VALUES (? ? ? ? ? ? ? ? ?)";
my $insert_sth = $dbh->prepare($sql);

Then do something like.

my @new_data = get_array_with_random_length();
splice(@new_data, @new_data, 0, undef x (9 - @new_data)); #pad @newdata to 9 elements w/ undefs (DBI will xlate to NULL)
$insert_sth->execute(@new_data);