i'm getting this error:
DBD::mysql::st execute failed: Column count doesn't match value count at row
1 at ./test-get.pl line 91, <FILE> line 1.
DBD::mysql::st execute failed: Column count doesn't match value count at row
1 at ./test-get.pl line 91, <FILE> line 1.
and i've been counting placeholders, columns, and variables and everything
seems to match up. any ideas what i'm missing?
btw, line 91 is the $shipsth->execute( .. ) call. this seems to have 20
fields.
#!/usr/bin/perl
use strict;
use warnings;
#use Carp::Always;
use DateTime;
use DateTime::Format::Natural;
use LWP::UserAgent;
use LWP::Simple;
use Web::Scraper;
use Data::Dumper::Simple;
use lib '/home/shawn/test/ais';
use uscgDBConnect;
my $pageth = $dbh->prepare('INSERT INTO page (
vid, date, data
) VALUES( ?, ?, ? )'
) or die "QUERY FAIL: $DBI::errstr\n$!\n";
my $docsth = $dbh->prepare('INSERT INTO docs (
vid, issue, expiration, document, agency
) VALUES( ?, ?, ?, ?, ? )'
) or die "QUERY FAIL: $DBI::errstr\n$!\n";
my $shipth = $dbh->prepare('INSERT INTO ship (
vid, service, name, tonnage_gt, build, lastrmby,
altvin, depth, hullid, flag, endservice, cargoauth,
tonnage_grt, length, vin, tonnage_net, callsign,
imo, deadweight, breadth
) VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
?, ?, ?, ?, ?, ?, ?, ?, ?, ? )'
) or die "QUERY FAIL: $DBI::errstr\n$!\n";
my( $infile ) = $ARGV[ 0 ] =~ m/^([\ A-Z0-9_.-]+)$/ig;
my( $vid ) = $ARGV[ 1 ] =~ m/^([\ A-Z0-9_.-]+)$/ig;
my $ship = scraper {
process '//*/div[@id="PanelResults"]/table/tr/td', 'table[]' => scraper {
process '//span', 'name' => '@id', 'attr' => '@title';
};
process '//*//table[@id="GridViewDocuments"]/tr', 'docs[]' => scraper {
process '//tr', 'attr' => '@title';
};
};
open(FILE, "< $infile" );
my $content = do { local $/; <FILE> };
{
$pageth->execute( $vid, time(), $content );
my $res = $ship->scrape( $content )
or die "Can't define content to parser $!";
my %values;
foreach my $data ( @{$res->{ table } } ) { # Define scraped
data to simpler values hash
next unless $data->{ name } and $data->{ attr };
foreach my $line (split /\n/, $data->{ attr } ) {
my ( $key, $val ) = split /:/, $line;
$values{ $key } = killspace( $val );
}
}
foreach my $data ( @{$res->{ docs } } ) { # Define scraped
documents to simpler docs hash
next unless $data->{ attr };
my %docs;
foreach my $line (split /\n/, $data->{ attr } ) {
my ( $key, $val ) = split /:/, $line;
$docs{ $key } = killspace( $val );
}
$docsth->execute( $vid,
correctdate( $docs{ 'Issue Date' } ),
correctdate( $docs{ 'Expiration Date' } ),
$docs{ 'Document' },
$docs{ 'Agency' }
) or die "EXECUTE FAIL: $DBI::errstr\n $!\n";
}
$shipth->execute( $vid,
$values{ 'Service' },
$values{ 'Vessel Name' },
correctnum( $values{ 'Gross Tonnage(GT ITC)' } ),
correctdate( $values{ 'Build Year' } ),
$values{ 'Last Removed From Service By' },
$values{ 'Alternate VINs' },
correctnum( $values{ 'Depth' } ),
$values{ 'Hull Number' },
$values{ 'Vessel Flag' },
correctdate( $values{ 'Out Of Service Date' } ),
$values{ 'Cargo Authority' },
correctnum( $values{ 'Gross Tonnage(GRT)' } ),
correctnum( $values{ 'Length' } ),
$values{ 'VIN' },
correctnum( $values{ 'Net Tonnage(NRT)' } ),
$values{ 'Vessel Call Sign' },
$values{ 'IMO Number' },
correctnum( $values{ 'Deadweight' } ),
correctnum( $values{ 'Breadth' } )
) or die "EXECUTE FAIL: $DBI::errstr\n $!\n";
}
sub killspace {
my( $val ) = @_;
$val =~ s/[\000-\031]//g; # Remove non alpha-numeric characters
$val =~ s/\s+/ /g; # Remove multiple spaces in a row
$val =~ s/^\s//; # Remove leading space
$val =~ s/\s$//; # Remove trailing space
return $val;
}
sub correctdate { # make valid sql DATE field
my( $date ) = @_;
my $parser = DateTime::Format::Natural->new;
my $dt = $parser->parse_datetime( $date );
return $dt->ymd('-');
}
sub correctnum { # Remove letters
my( $string ) = @_;
$string =~ s/[a-zA-Z]+//g;
return $string;
}
--- SQL DB ---
mysql> describe page;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| vid | int(10) unsigned | NO | PRI | NULL | |
| date | int(10) unsigned | YES | | NULL | |
| data | text | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> describe ship;
+------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| vid | int(10) unsigned | NO | PRI | NULL | |
| service | varchar(25) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| tonage_gt | decimal(5,2) | YES | | NULL | |
| build | tinyint(4) | YES | | NULL | |
| lastrmby | varchar(50) | YES | | NULL | |
| altvin | varchar(25) | YES | | NULL | |
| depth | decimal(5,2) | YES | | NULL | |
| hullid | int(10) unsigned | YES | | NULL | |
| flag | varchar(60) | YES | | NULL | |
| endservice | date | YES | | NULL | |
| cargoauth | varchar(50) | YES | | NULL | |
| tonage_grt | decimal(5,2) | YES | | NULL | |
| length | decimal(5,2) | YES | | NULL | |
| vin | int(10) unsigned | YES | | NULL | |
| tonage_net | decimal(5,2) | YES | | NULL | |
| callsign | varchar(10) | YES | | NULL | |
| imo | int(10) unsigned | YES | | NULL | |
| deadweight | int(10) unsigned | YES | | NULL | |
| breadth | decimal(5,2) | YES | | NULL | |
+------------+------------------+------+-----+---------+-------+
20 rows in set (0.00 sec)
mysql> describe docs;
+------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| vid | int(10) unsigned | NO | | NULL | |
| issue | date | YES | | NULL | |
| expiration | date | YES | | NULL | |
| document | varchar(250) | YES | | NULL | |
| agency | varchar(250) | YES | | NULL | |
+------------+------------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
