Re^2: Can insert but not select

Thanks! Here is the code in question.

$rowCheckQuery = qq{SELECT color_id FROM color WHERE color=?};
              foreach my $color (@color){

                      # trim whitespace
                      $color =~ s/^\s+|\s+$//gm;
                      # print "Color is - ", $color, " -\n\n";

                      my $sth = $dbh->prepare($rowCheckQuery);
                      $sth->execute($color);

                      while(my $color_test = $sth->fetchrow_arrayref) 
+{
                              push @color_test, $color_test;
                      }

              }
[download]

Also I double checked the user I am using for the script and it has global select permissions. I can select on other tables in the same db.... just very oddly not two of my tables. This might be more of a mysql issue. I'm not sure.

Thanks again.

Comment on Re^2: Can insert but not select Download Code

Replies are listed 'Best First'.
Re^3: Can insert but not select by kyle (Abbot) on Jan 03, 2009 at 05:42 UTC
And what behavior do you see from this? Note that `fetchrow_arrayref` always returns the same array reference (see the DBI documentation). That means if you want to store all the rows (as you appear to be attempting), you have to copy the array. `while(my $color_test = $sth->fetchrow_arrayref) { push @color_test, [ @{$color_test} ]; }` [download]	[reply] [d/l] [select]
Re^4: Can insert but not select by zoophagous (Novice) on Jan 03, 2009 at 05:49 UTC
The behavior I am seeing is the foreach loop hangs. The loops executes if I put an insert statement in place of the select. Or will execute if I select from a different table. If I put a print statement below the $sth execute it doesn't print and the loop doesn't iterate.	[reply]
Re^5: Can insert but not select by zoophagous (Novice) on Jan 03, 2009 at 06:41 UTC
Digging around on the mysql boards I found out others have this problem as well. All, like me, are running active perl on windows boxes and using the mysqlPP driver. I switched to the mysql driver and presto - works like a champ. The mysqlPP driver is part of the default active perl PPM set. The mysql PPM has to be downloaded from University of Winnipeg. Thanks for the assistance and hope this helps someone digging around for the answer!	[reply]
Re^3: Can insert but not select by MidLifeXis (Monsignor) on Jan 03, 2009 at 12:10 UTC
In addition to comments already made, you also want to prepare your statement outside of the loop. You are effectively negating the performance benefit of running the prepare command. --MidLifeXis	[reply]