CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

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

 



Omore2ac
Novice

May 18, 2011, 12:33 PM

Post #1 of 6 (787 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 (781 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 (780 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 (778 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 (777 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 (774 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) Powered by Gossamer Forum v.1.2.0

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