Perl Guru Forums: Perl Programming Help: Beginner: @arrays and DBI

CGI/Perl Guide | Learning Center | Forums | Advertise | Login

Site Search: in


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	LOG IN

Home: Perl Programming Help: Beginner:
@arrays and DBI

Omore2ac
Novice

May 18, 2011, 12:33 PM

Post #1 of 6 (463 views)

@arrays and DBI	Can't Post

Hey all,

I recently tried writing the values of an @arrays into a database via a $STH.

my $sth = $dbh->do(qq{INSERT INTO station (@station_head) VALUES (@station_data)});

Doing this, I get the following error:

"DBD::SQLite::db do failed: near ")": syntax error at tableparser5.plx line 125 (#the line above), <> line 468.

DBD::SQLite::db do failed: near ")": syntax error at tableparser5.plx line 125, <> line 468."

I've tried multiple ways of writing the statement without avial. I have verified that my @array holds the information required. So I am assuming that this - that is writing the values of the array - cannot be done. So I tried another solution.

my $sth = $dbh->do(qq{INSERT INTO station ($station_head[0..$#station_head]) VALUES ($station_data[0..$#station_data])});

This fails as well with the following errors:

"Argument "" isn't numeric in array element at tableparser5.plx line 125, <> line 468.

Use of uninitialized value within @station_head in concatenation (.) or string at tableparser5.plx line 125, <> line 468."

Now my question is: How exactly can I write the info in my @array into a database? The objective is to iterate through my %pair, on each iteration, push those values to @station_head and @station_data, then write that as a whole to the database instead of writing to the database on each iteration.

Code Relevant for Understanding is posted below:

for (\$row_count ){
my @cords = ($j.$row_count.$idx);
my ($cords) = @cords;
my @hash_pair;
my @station_data;
my @station_head;
my %pair = ( # long and unnecessary information);

while (@hash_pair = (each(%pair))){
if ($hash_pair[1] == $cords){
warn "[$hash_pair[0]] => ($cell)\n";
push (@station_head, $hash_pair[0]);
push (@station_data, $cell);

# print "[","^","-",@station_head,"-","^","]";
#print " ";
# print "[","^","-",@station_data,"-","^","]","\n";

}

my $sth = $dbh->do(qq{INSERT INTO station (@station_head) VALUES (@station_data)});

Thanks for any help!

miller
User

May 18, 2011, 1:29 PM

Post #2 of 6 (457 views)

Re: [Omore2ac] @arrays and DBI [In reply to]	Can't Post

You can't insert an array into a sql statement like that. You must actually build the sql statement like the following:

Code

my $fields = join ',', @station_head; 
my $placeholders = join ',', ('?') x @station_data; 
my $sth = $dbh->do(qq{INSERT INTO station ($fields) VALUES ($placeholders)}); 
$sth->execute(@station_data) or die $dbh->errstr;

- Miller

BillKSmith
Veteran

May 18, 2011, 1:37 PM

Post #3 of 6 (456 views)

Re: [Omore2ac] @arrays and DBI [In reply to]	Can't Post

SQL requires commas between the column names and between the values. Refer to $LIST_SEPARATOR in perldoc perlvar,

Code

  
 
{  
 
    local $" = ',';  
 
    my $sth = $dbh->do(qq{INSERT INTO station (@station_head) VALUES (@station_data)});  
 
}

Good Luck,
Bill

Omore2ac
Novice

May 18, 2011, 1:39 PM

Post #4 of 6 (454 views)

Re: [miller] @arrays and DBI [In reply to]	Can't Post

Thanks Miller. I had no clue. Where can I find documentation on this to thorougly understand the reasoning? DBI on CPAN?

Omore2ac
Novice

May 18, 2011, 1:40 PM

Post #5 of 6 (453 views)

Re: [BillKSmith] @arrays and DBI [In reply to]	Can't Post

Thanks BillKSmith. Disregard previous request Miller. Thank you both for you help. I'll read up on $List_separator.

Thanks again for the help guys. Truly thankful.

miller
User

May 18, 2011, 1:49 PM

Post #6 of 6 (450 views)

Re: [BillKSmith] @arrays and DBI [In reply to]	Can't Post

@BillKSmith

The list separator can help with the field names, but not the values. Unless they are all integers they still need to either be enclosed in quotes and escaped.

The best course of action is always to use placeholders to avoid SQL injection attacks and other annoyances, but I'm sure you already know that.

- Miller

Search for (options)

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives