You don't need:

my $rowCheckQuery = qq{SELECT color_id FROM color WHERE color=?};
[download]

in the loop. Simplify with

my $stmt = qq{SELECT color_id FROM color WHERE color=?};\
my $color_id;
foreach my $color (@color){
  $color =~ s/^\s+|\s+$//gm;
  $color_id = $dbh->selectrow_array($stmt, undef, $color); 
}
[download]

Required reading.

my $dbh = DBI->connect("DBI:mysqlPP:host=localhost;database=foo",'bar'
+,'baz',{ AutoCommit => 1, RaiseError => 1, PrintErrors=>1}) or die "C
+annot connect";

... some stuff...

foreach my $color (@color){

            # trim whitespace
                   $color =~ s/^\s+|\s+$//gm;

                #  see if the color already exists in the color table.
                   my $rowCheckQuery = qq{SELECT color_id FROM color W
+HERE color=?};
                my $sth = $dbh->prepare($rowCheckQuery);
                $sth->bind($color);
                $sth->execute() or die $dbh->errstr;
}
[download]

Thanks all!

After adding the PrintErrors => 1 I now have an error message to go off of.

Can't set DBI::db=HASH(0x1ba5f4c)->{PrintErrors}: unrecognised attribute name or invalid value at C:/Perl/site/lib/DBD/mysqlPP.pm line 306

I suspect the issue is that I am using this to test to see if a value is already in a db. I am expecting empty returns and when I get an empty return I get this behavior. When I insert a test value into the db and hard code the select statement to the test value it works like a champ.

Is there a better or more correct way to go about testing for existing values in a db?

Thanks again!

That error just says that PrintErrors is not a valid setting for DBI. It should be PrintError.

If you want to solve this, you need to follow some of the advice you're getting. Check the return value from connect(), add strict and warnings. Also, where are you getting your @colors from? I think this is not your real code. Are you reading it from another statement handle, also stored in a variable called $sth?

Well, Sometimes I m checking values like that..

my $sth = $dbh->prepare(qq{...});
$sth->execute();
if($sth->rows){ #If handler has affected rows

}
[download]

OR

my $sth = $dbh->prepare(qq{...});
$sth->execute();
while(my $row = $sth->fetchrow_hashref()){ #just do something if $sth 
+can return any records

}
[download]

There is more then one way to do (c) ))) Yuo can also try my $hash = $sth->fetchrow_hashref() || undef;

You're not checking the return code from connect().

{RaiseError=>1} takes care of that.

No, it doesn't. It only takes care of errors from methods called on the returned handle. You always have to check the return value of connect with or die $DBI::errstr.

(Update: your problem was the package, ho-ho: Re^5: Can insert but not select. I took the PP in the mysqlPP in your sample to be a typo. Silly me.)

Your problem is bad code somewhere, not a DBI + SELECT issue. This is one of those moments I go through sometimes where I'm dead certain there is something mysterious going on but in fact Occam is right: your code is wrong; not the code of 100,000 other devs. :)

Is this really all you're running?

That will execute but it won't print anything and you're not capturing or testing its results. You need to do something with the statement handle after its executed or at least check its return value. Try this or a near variant-

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("DBI:mysql:foo;mysql_read_default_file=~/.my.cn
+f",
                       undef,
                       undef,
                       { AutoCommit => 1,
                         RaiseError => 1, }
                      );

my @color = qw( red blue green puce majenta lavender lake teal );

my $sth_color_exists = $dbh->prepare("SELECT 1 FROM color WHERE color 
+= ?");

# uncomment to get lots of info about what's going on.
# DBI->trace(2);

for my $color ( @color )
{
    $color =~ s/^\s+|\s+$//g;
    my $rv = $sth_color_exists->execute($color);
    printf(qq{There %s %d row%s found for color = "%s"\n},
           $rv == 1 ? "was" : "were",
           $rv,
           $rv == 1 ? "" : "s",
           $color
           );
}
$sth_color_exists->finish();
[download]

Note that the statement handle only has to be created once and reused as much as you like. I personally would create hash of the colors with one big query instead. Something like this thought it's a little tricky-

my $array_ref = $dbh->selectcol_arrayref("SELECT color, 1 FROM color",
                                         { Columns => [1,2] }
                                        );
my %colors = @{ $array_ref };

# Now rewrite the loop above along the lines of-
#    for my $color ( sort keys %colors )
[download]