in reply to SOLVED: DBI SQL Returns Less than Direct SQL

I would look in the code. Mostly, the code that does the fetch, but maybe also the code that does the output.

Please reduce your code to 20 lines or so, preferrably a self-contained program, so we can easily reproduce your problem. If that's not possible, modify your code so it uses a hard-coded SQL statement, and show the part where you fetch the data and the part where you output it, and how they interconnect.

Also, a select count(*) ... might tell you whether the database tells Perl the same number of rows it tells phpmyadmin.

Replies are listed 'Best First'.
Re^2: DBI SQL Returns Less than Direct SQL
by DaveNagy (Initiate) on Oct 17, 2011 at 23:32 UTC
    I'm doing a basic 'prepare' and 'execute'. I've tried this with variable substitutions ('?') and hard-coded SQL. What you see here is what I've been primarily working with - a single SQL call (1 per execution) with the search words inserted directly into the SQL. Here's the stripped down code: 
    #! /usr/bin/perl
# wwfindt.pl - Find articles in WW Index databases, return in table

use DBI;
use strict;

my $server = "Falcon";
my $db = "wwindex";
# database-related variables
my $sqlString;
my $rows;
my ($dbh, $sth);
my @result;

# Counters 
my $recs = 0;

# Variables
my $searchWords;

print "WWFindt.pl starting.\n";

# Connect to MYSQL 
$dbh = DBI->connect("DBI:mysql:host=".$server.";database=".$db,"guest"
+,"",{RaiseError=>1}) or die "Failed to Connect!  $DBI::errstr";
print "Connected to database '$db' on server '$server'\n";

#print "Enter search words:";
#chomp($words = <>);
$searchWords = "saw blade storage";

# Issue SQL and check results
$sqlString = join(" ", "SELECT Source.SourceName, SourceDateVolumeIssu
+e.SourceDate, SourceDateVolumeIssue.SourceVolume, SourceDateVolumeIss
+ue.SourceIssue, Article.ArticlePage, Article.ArticleTitle, Article.Ar
+ticleSynopsis",
"FROM (Article, Source, SourceDateVolumeIssue)",
"WHERE (ArticleTitle LIKE '%".$searchWords."%' OR ArticleSynopsis LIKE
+ '%".$searchWords."%') AND (SourceDateVolumeIssueId = Article.Article
+SourceId) AND (Source.SourceId = SourceDateVolumeIssue.SourceId)",
"ORDER BY Source.SourceName, SourceDateVolumeIssue.SourceVolume, Sourc
+eDateVolumeIssue.SourceIssue, Article.ArticlePage");
print "SQL: $sqlString\n";
$sth = $dbh->prepare($sqlString);
$sth->execute();

$rows = $sth->rows();
print "DB Query done.  $rows rows returned.\n";

if ($rows == 0) {
    print "** Query for Title or Synopsis containing '$searchWords' di
+d not find anything.\n\n";
    }
else {
    while(@result = $sth->fetchrow_array()) {
        print "Name='$result[0]', Date/Vol/Iss=$result[1]/$result[2]/$
+result[3], Page=$result[4], Title='$result[5]'\n";
        } 
    }

print "WWFindt.pl completed processing.\n";
$sth->finish;
$dbh->disconnect;
exit (0);

    [download]
    Here are the results: 
    dave@davesdell:~/Perl$ ./wwfindt.pl
WWFindt.pl starting.
Connected to database 'wwindex' on server 'Falcon'
SQL: SELECT Source.SourceName, SourceDateVolumeIssue.SourceDate, Sourc
+eDateVolumeIssue.SourceVolume, SourceDateVolumeIssue.SourceIssue, Art
+icle.ArticlePage, Article.ArticleTitle, Article.ArticleSynopsis FROM 
+(Article, Source, SourceDateVolumeIssue) WHERE (ArticleTitle LIKE '%s
+aw blade storage%' OR ArticleSynopsis LIKE '%saw blade storage%') AND
+ (SourceDateVolumeIssueId = Article.ArticleSourceId) AND (Source.Sour
+ceId = SourceDateVolumeIssue.SourceId) ORDER BY Source.SourceName, So
+urceDateVolumeIssue.SourceVolume, SourceDateVolumeIssue.SourceIssue, 
+Article.ArticlePage
DB Query done.  1 rows returned.
Name='ShopNotes', Date/Vol/Iss=Sept/Oct, 2007/16/95, Page=4, Title='Ti
+ps for Your Shop'
WWFindt.pl completed processing.
dave@davesdell:~/Perl$

    [download]
    Here are pertinent trace messages: 
    mysql_st_internal_execute MYSQL_VERSION_ID 50137
>parse_params statement SELECT Source.SourceName, SourceDateVolumeIssu
+e.SourceDate, SourceDateVolumeIssue.SourceVolume, SourceDateVolumeIss
+ue.SourceIssue, Article.ArticlePage, Article.ArticleTitle, Article.Ar
+ticleSynopsis FROM (Article, Source, SourceDateVolumeIssue) WHERE (Ar
+ticleTitle LIKE '%saw blade storage%' OR ArticleSynopsis LIKE '%saw b
+lade storage%') AND (SourceDateVolumeIssueId = Article.ArticleSourceI
+d) AND (Source.SourceId = SourceDateVolumeIssue.SourceId) ORDER BY So
+urce.SourceName, SourceDateVolumeIssue.SourceVolume, SourceDateVolume
+Issue.SourceIssue, Article.ArticlePage
 <- dbd_st_execute returning imp_sth->row_num 1
    <- execute= 1 at wwfindt.pl line 38
    -> rows for DBD::mysql::st (DBI::st=HASH(0x86c09a0)~0x85816f8) thr
+#857e008
    <- rows= '1' at wwfindt.pl line 40
    -> fetchrow_array for DBD::mysql::st (DBI::st=HASH(0x86c09a0)~0x85
+816f8) thr#857e008
    -> dbd_st_fetch
        dbd_st_fetch for 08653d58, chopblanks 0
    dbd_st_fetch result set details
    imp_sth->result=086dab98
    mysql_num_fields=7
    mysql_num_rows=1
    mysql_affected_rows=1
    dbd_st_fetch for 08653d58, currow= 1
    <- dbd_st_fetch, 7 cols
    <- fetchrow_array= ( 'ShopNotes' 'Sept/Oct, 2007' '16' '95' '4' 'T
+ips for Your Shop' 'Saw blade storage case; Conduit compass & router 
+trammel; Shop-made gouge block; Circle cutter set-up gauge.' ) [7 ite
+ms] row1 at wwfindt.pl line 47
    -> fetchrow_array for DBD::mysql::st (DBI::st=HASH(0x86c09a0)~0x85
+816f8) thr#857e008

    [download]
    Finally, results from the SQL call in PHPAdmin: 
    SQL result

Host: Falcon
Database: wwindex
Generation Time: Oct 17, 2011 at 07:11 PM
Generated by: phpMyAdmin 3.3.2deb1 / MySQL 5.1.41-3ubuntu12.10
SQL query: SELECT Source.SourceName, SourceDateVolumeIssue.SourceDate,
+ SourceDateVolumeIssue.SourceVolume, SourceDateVolumeIssue.SourceIssu
+e, Article.ArticlePage, Article.ArticleTitle, Article.ArticleSynopsis
+ FROM (Article, Source, SourceDateVolumeIssue) WHERE (ArticleTitle LI
+KE '%saw blade storage%' OR ArticleSynopsis LIKE '%saw blade storage%
+') AND (SourceDateVolumeIssueId = Article.ArticleSourceId) AND (Sourc
+e.SourceId = SourceDateVolumeIssue.SourceId) ORDER BY Source.SourceNa
+me, SourceDateVolumeIssue.SourceVolume, SourceDateVolumeIssue.SourceI
+ssue, Article.ArticlePage LIMIT 0, 30 ;
Rows: 6
SourceName     SourceDate     SourceVolume     SourceIssue     Article
+Page     ArticleTitle     ArticleSynopsis
ShopNotes     July, 1992     0     4     28     Shop Solutions     Saw
+ blade storage rack; Edge jointing tip; Installing threaded inserts; 
+Clamping irregular shapes; File handles
ShopNotes     Sept/Oct, 2007     16     95     4     Tips for Your Sho
+p     Saw blade storage case; Conduit compass & router trammel; Shop-
+made gouge block; Circle cutter set-up gauge.
ShopNotes     Sept/oct, 2009     18     107     44     Saw Blade Stora
+ge     Three storage systems that protect saw blades while keeping th
+em organized.
Wood     Dec/Jan, 2008/2009     25     188     18     Shop Tips     Ex
+tension for blocked drill crank; Pushstick for safer belt sanding; Ea
+sy-to-reach scrollsaw blade storage; Using metalworking clamps for ex
+tended reach; Adding hardboard zero-clearance plate to circular saw; 
+Shoe organizer to store bottles and cans; Economical corner clamps; U
+sing chip cans for storing paint rollers on the job.
Woodsmith     June, 2002     24     141     4     Tips & Techniques   
+  PVC Pipe Clamp Storage; Name that cord; Quicker panels on the table
+ saw; Saw blade storage; Tap & die storage; Laminate liner for shop v
+acuum.
Woodsmith     Dec, 2002     24     144     4     Tips & Techniques    
+ Dangling drawer stops; PVC tool storage; Cork clamping; Mess-free cl
+eaning; Saw blade storage;

    [download]
    (I have a PDF of this PHPAdmin result set for easier reading.) Again, any suggestions are appreciated!! Dave
[reply]
[d/l]
[select]
      Pure speculation here - but the only difference between the SQL's that I noticed is the "LIMIT" clause.

      One (wierd) possibility is that one of the "FROM" sources is a view with a LIMIT clause with a value of 1.

      This may determine the rows returned, so if you add a "LIMIT 0" clause to your perl code, that may get overriden.

      Disclaimer: I have no SQL qualifacitions, and I have not RTFM.

                  "XML is like violence: if it doesn't solve your problem, use more."

[reply]
        The LIMIT is a difference but if I use a different search term (like 'rust'), the PERL program returns more than one row. LIMIT is inserted by PHPAdmin when I paste the SQL (without the LIMIT) into it's scratch pad. Dave
[reply]
        phpMyAdmin will reformat the SQL statement and add "LIMIT 0, 30" meaning first page, 30 rows.
[reply]
Re^2: DBI SQL Returns Less than Direct SQL
by DaveNagy (Initiate) on Oct 18, 2011 at 12:30 UTC

    The code that does the fetch is a PREPARE and EXECUTE. I have tried the SQL with substitutable values (putting my search terms in the EXECUTE), creating the SQL with the search terms JOINed before the PREPARE, and finally, a pure hard-coded SQL with not variables and all return one row.

    I've also tried 'selectall_array(sql-string)' and it too returns one row.

    I can see in the DBI trace that MySQL is returning one row so it doesn't matter how I output the data, there's still just one row returned.

    Thanks for all of the advice! Dave Dave
[reply]

In Section Seekers of Perl Wisdom