Perl DBI Postgresql: Returning undef for data that is there

Vote count:

0

I'm getting very weird results and I know their must be something small that I'm doing wrong. I'm trying to check and see if a row exist in a postgresql database table and on the first loop I get an actual value. On the second iteration of the loop and all iterations there after I get an undef. Why? Is there something I must do that I'm not doing. I'm not using prepare so I shouldn't have to call finish etc.

Any insight will greatly help me debug this issue.

Sorry the code is so nasty for now. I've been on a debugging hunt and made things pretty ugly.

Also sorry for the ugly sample output. I don't know how to format it well with stackoverflow.

Please not the "Select Name" printout in sample output. All iterations after the first returns undef. The sql call in question is towards the end of the file. This is the line

my $selectSQL = "select name from crawler_url where url='http://ift.tt/1yPeLYQ;{'uri'}' ";

Perl code:

#!/usr/bin/perl

use LWP::Simple;                # From CPAN
use JSON qw( decode_json );     # From CPAN
use JSON::Parse 'parse_json';
use Data::Dumper;               # Perl core module
use HTML::TreeBuilder 5 -weak;
use Mojo::DOM;
use DBI;
use String::Util qw(trim);
use strict;                     # Good practice
use warnings;                   # Good practice

my $initialize = 0;
my $debug = 1;


&main;

sub main
{
    my $dbh = connect2db();

    unless(defined($dbh))
    {
            exit 1;
    }


my $trendsurl;

    my $sth = $dbh->prepare("SELECT company_name from companies where active=1");
    $sth->execute;
    while( my $company = $sth->fetchrow_hashref() )
    {
            #print Dumper($company)."\n";

            my $sth2 = $dbh->prepare("SELECT url from crawlers where company_name='$$company{'company_name'}' ");
            $sth2->execute;
            while( my $url = $sth2->fetchrow_hashref() )
            {
                    #print " NOW ON URL $$url{'url'} ##########\n";
                    $trendsurl = $$url{'url'};
        chomp($trendsurl);
        $trendsurl = trim($trendsurl);
        print "URL: ".$trendsurl."\n";


        my $json = get( $trendsurl );
        die "Could not get $trendsurl!" unless defined $json;


        my $parsed_json = parse_json($json);

        my $items = $parsed_json->{'sections'}[0]->{'items'};



        foreach my $item_hash (@$items)
        {
            #print Dumper($item_hash)."\n";

            my $category = $item_hash->{'name'};
            print "Lip Product Category: $category\n";
            foreach my $item ( @{ $item_hash->{'items'} } )
            {
                print Dumper($item)."\n";

                my $selectSQL = "select name from crawler_url where url='http://ift.tt/1yPeLYQ;{'uri'}' ";

                print $selectSQL."\n" if($debug);

                #my $sth3 = $dbh->prepare($selectSQL);
                #$sth3->execute;

                #my $test = $sth3->fetchrow_hashref();
                my ($productCount) = $dbh->selectrow_array($selectSQL);

                my $date = localtime;
                chomp($productCount);
                trim($productCount);
                chomp($item->{'name'});
                trim($item->{'name'});

                print "Select Name: '$productCount'\n";
                print "Item Name: '$item->{'name'}'\n";
                print "Do they equal: ", index($productCount, $item->{'name'}), " \n";

                print Dumper($productCount);



                if( index($productCount, $item->{'name'}) == -1 )
                {
                    my $insertSQL = "insert into crawler_url (first_seen,url,name,category,last_checked) values ('$date','http://ift.tt/1yPeLYQ;{'uri'}','$item->{'name'}','$category','$date') ";
                    print $insertSQL."\n" if($debug);
                    my $retVal = $dbh->do($insertSQL);

                    $insertSQL = "insert into urls (company_name,url) values ('$$company{'company_name'}','http://ift.tt/1yPeLYQ;{'uri'}') ";
                    print $insertSQL."\n" if($debug);
                    $retVal = $dbh->do($insertSQL);

                }
                else
                {
                    #We have seen this before
                    my $updateSQL = "update crawler_url SET (url,name,category,last_checked) = ('http://ift.tt/1yPeLYQ;{'uri'}','$item->{'name'}','$category','$date' )";
                    print $updateSQL."\n" if($debug);
                    my $retVal = $dbh->do($updateSQL);
                }

            }
        }

    }
}
}

sub connect2db {
        return DBI->connect("dbi:Pg:dbname=iNotify","ghost","BANK1917");
}

Sample output:

URL: http://ift.tt/1K7TTOf

Lip Product Category: Lipstick

$VAR1 = {
          'uri' => '/product/shaded/168/310/Products/Lips/Lipstick/Lipstick/index.tmpl',
          'description' => 'Colour plus texture for the lips. Stands out on the runway...',
      'name' => 'Lipstick',
      'thumbnail' => '/images/products/56x56/M300.jpg',
      'header' => '/images/pnav/product/headers/pnav_M300_200x12_off.gif',
      'id' => 'CAT168PROD310'
    };

select name from crawler_url where url='http://ift.tt/XqGI6O'

Select Name: 'Lipstick                                                                                                                        '

Item Name: 'Lipstick'


Do they equal: -1

$VAR1 = 'Lipstick                                                                                                                        ';
update crawler_url SET (url,name,category,last_checked) = ('http://ift.tt/XqGI6O','Lipstick','Lipstick','Wed Jan 28 21:15:40 2015' )

$VAR1 = {
      'id' => 'CAT168PROD34492',
      'thumbnail' => '/images/products/56x56/MX5G8N.jpg',
      'header' => '/images/pnav/product/headers/pnav_MX5G8N_200x12_off.gif',
      'description' => "Miley Cyrus\x{2019}s shade of VIVA GLAM Lipstick. Her super-sexy hot...",
      'name' => 'VIVA GLAM Miley Cyrus Lipstick',
      'uri' => '/product/shaded/168/34492/Products/Lips/Lipstick/VIVA-GLAM-Miley-Cyrus-Lipstick/index.tmpl'
    };

select name from crawler_url where url='http://ift.tt/1ztTfdy'

Select Name: ''

Item Name: 'VIVA GLAM Miley Cyrus Lipstick'


Do they equal: 0

$VAR1 = undef;

insert into crawler_url (first_seen,url,name,category,last_checked) values ('Wed Jan 28 21:15:40 2015','http://ift.tt/1ztTfdy','VIVA GLAM Miley Cyrus Lipstick','Lipstick','Wed Jan 28 21:15:40 2015')

insert into urls (company_name,url) values ('MAC                                                             ','http://ift.tt/1ztTfdy')

$VAR1 = {
      'uri' => '/product/shaded/168/34798/Products/Lips/Lipstick/Isabel-and-Ruben-Toledo-Lipstick/index.tmpl',
      'description' => 'Formulated to shade, define and showcase the lips in a rouge-y...',
      'name' => 'Isabel and Ruben Toledo Lipstick ',
      'header' => '/images/pnav/product/headers/pnav_MWWE1T_200x12_off.gif',
      'thumbnail' => '/images/products/56x56/MWWE1T.jpg',
      'id' => 'CAT168PROD34798'
    };

select name from crawler_url where url='http://ift.tt/1yPeLYW'

Select Name: ''


Item Name: 'Isabel and Ruben Toledo Lipstick '

Do they equal: 0

$VAR1 = undef;

insert into crawler_url (first_seen,url,name,category,last_checked) values ('Wed Jan 28 21:15:40 2015','http://ift.tt/1yPeLYW','Isabel and Ruben Toledo Lipstick ','Lipstick','Wed Jan 28 21:15:40 2015')

insert into urls (company_name,url) values ('MAC                                                             ','http://ift.tt/1yPeLYW')

asked 1 min ago

Nick.D

33

Perl DBI Postgresql: Returning undef for data that is there