Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options

Trouble inserting data using DBD:PG with arrays

by symgryph (Sexton)
on Oct 23, 2007 at 16:57 UTC ( #646720=perlquestion: print w/replies, xml ) Need Help??
symgryph has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to get some data from my logs into a postgres database. Included in the program is the 'data' I am slurping, under 'data'. I know that the way it is written it won't work, but it works fine from standard in. I get the following error message when running the program: DBI::st=HASH(0x8757070)->_prepare(...): attribute parameter '' is not a hash ref at /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi/DBD/ line 190, <> line 1. The database table looks like this:
date_combined | timestamp without time zone | origin | integer | action | text | if_name | text | if_dir | text | src | inet | dst | inet | proto | text | rule | integer | service | integer | s_port | integer | icmp | text | icmp_type | text | primary_key | bigint | not null default nextva +l('logs_primary_key_seq'::regclass) When I try the following code: #!/usr/bin/perl -w use DBI; my $dbname = 'logs'; my $dbh = DBI->connect("dbi:Pg:dbname=$dbname"); if (!$dbh) { die "Err Couldn't open connection: ".$DBI::errstr."\n"; } my $insert_query = "INSERT INTO logs (origin, action, if_name, if_dir, + src, dst, proto, rule, service,s_port, icmp_type, icmp_code) values +(?,?,?,?,?,?,?,?,?,?,?,?,?)"; $datematch = qr/(\d{1,2})([a-z]{3})(\d{4})/io; %months = ( Jan => "January", Feb => "February", Mar => "March", Apr => "April", May => "May", Jun => "June", Jul => "July", Aug => "August", Sep => "September", Oct => "October", Nov => "November", Dec => "December" ); while (<>) { my @fields = split (/\|/); $i++; # remove the leading space padding and reformat the date field $fields[1] =~ s/^\ //; $fields[1] =~ s/$datematch/$months{$2}\ $1\,$3/; my $date = "$fields[1] $fields[2]"; my $date = "$fields[1] $fields[2]"; foreach $field (3, 11, 12) { if ($fields[$field] eq "") { $fields[$field] = ""; } } for ($i = 1; $i < $#fields; $i++) { $fields[$i] =~ s/\'//g; } my $numrows = $dbh->do($insert_query,$date,$fields[3],$fields[5],$fiel +ds[7],$fields[8],$fields[11],$fields[12],$fields[13],$fields[14],$fie +lds[15],$fields[16],$fields[17],$fields[18]); if (!$numrows) { die "Failed to insert entry number $i:".$dbh->errstr."\n"; } @fields=""; } _DATA 2|9Oct2007|23:59:00||log|accept||eth-s1/s2p1c0|inbound|VPN- +1 & FireWall-1||||tcp|21|80|1326||||||||||||||||| +||||
"Two Wheels good, Four wheels bad."

Replies are listed 'Best First'.
Re: Trouble inserting data using DBD:PG with arrays
by almut (Canon) on Oct 23, 2007 at 18:20 UTC

    I think the $dbh->do() method wants an "attributes" (think of options) hashref as the second argument (which is being passed through to prepare() ). I.e. you'd need to specify an empty hashref if you don't have any options to pass:

    my $numrows = $dbh->do($insert_query, {}, $date,$fields[3],...);

    Update:  it appears that undef would work, too (in place of {}) — see the example in the docs.

Re: Trouble inserting data using DBD:PG with arrays
by andyford (Curate) on Oct 23, 2007 at 18:12 UTC

    Looking at your error message, I would guess that if you convert your code to run under "use strict" and throw in "use diagnostics", your problem will melt away.

    Update: wrong, ignore

    non-Perl: Andy Ford

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://646720]
Approved by philcrow
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (6)
As of 2017-10-22 09:36 GMT
Find Nodes?
    Voting Booth?
    My fridge is mostly full of:

    Results (272 votes). Check out past polls.