comment on

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 '10.1.0.1' is not a hash ref at /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi/DBD/Pg.pm 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] = "0.0.0.0";
      }
   }

   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|1.13.229.61|log|accept||eth-s1/s2p1c0|inbound|VPN-
+1 & FireWall-1||1.19.14.22|10.21.21.8|tcp|21|80|1326|||||||||||||||||
+||||
[download]

"Two Wheels good, Four wheels bad."

In reply to Trouble inserting data using DBD:PG with arrays by symgryph

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		`&`
	<		`<`
	>		`>`
	[		`[`
	]		`]`

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.