Beefy Boxes and Bandwidth Generously Provided by pair Networks Cowboy Neal with Hat
No such thing as a small change
 
PerlMonks  

SOLVED: DBI SQL Returns Less than Direct SQL

by DaveNagy (Initiate)
on Oct 17, 2011 at 21:05 UTC ( #932010=perlquestion: print w/ replies, xml ) Need Help??
DaveNagy has asked for the wisdom of the Perl Monks concerning the following question:

I have code that issues an SQL SELECT against a MySQL database that is returning 1 row. If I copy & paste the SQL (from my own PRINT or from what I see in the DBI trace) into PHPMYADMIN's SQL for the same database on the same server, the result set is 6 rows.

Before I post code, which is gonna be kinda useless since no one has the data from the database, can anyone offer any suggestions on where to look for a solution? I will post code and results as needed.

My appreciation for any suggestions.

Dave

*********************************

UPDATE AT 5:18 PM ET on 10/18/11....

This problem is not with Perl or my code but with how I setup PHPAdmin. I believed it to be pointing to my prod DB server but it isn't. When the Perl program points to my test DB, it works fine. My apologies for raising this issue.

Thanks for the fast attention to my question, all!

Dave

*********************************

FINAL WORD ON THIS AT Noon ET on 10/19/11....

I had made errors during setup of PHPAdmin such that when I thought I was looking at my prod db server, I was still looking at my test server. This affected everything I used PHPAdmin for, including replicating between the systems. When I issued the raw SQL call to the prod server, PHPAdmin was issuing the query to my test server while my PERL program was issuing the query against the real prod db. All is fixed now!

Again, thanks to everyone who contributed ideas and suggestions.

Dave

Comment on SOLVED: DBI SQL Returns Less than Direct SQL
Re: DBI SQL Returns Less than Direct SQL
by Corion (Pope) on Oct 17, 2011 at 21:15 UTC

    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.

      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);
      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$
      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
      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;
      (I have a PDF of this PHPAdmin result set for easier reading.) Again, any suggestions are appreciated!! Dave
        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."

      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
Re: DBI SQL Returns Less than Direct SQL
by onelesd (Pilgrim) on Oct 17, 2011 at 21:36 UTC

    You probably are using an inappropriate DBI method for your desired result. In your perl, try the following and see if you get the right number of rows:

    my $results = $dbh->selectall_arrayref($your_sql) ; print Dumper($_) foreach (@{$results}) ;
      Thanks, onelesd, for this suggestion but I don't have a subroutine 'Dumper' on my system. I'll be posting code and some snippets of trace messages later. Dave
        Oops, I left out the use statement. It's from the Data::Dumper module which you probably do have installed (lots of modules use it).
Re: DBI SQL Returns Less than Direct SQL
by GrandFather (Cardinal) on Oct 17, 2011 at 23:13 UTC

    Often it can help to crunch the code and database down to just enough to show the issue. At some point during the "crunching" process you will either have an epiphany, or you'll reduce things to a point where you can post sample code and data for us to look at.

    Note that DBD::AnyData can often be used to provide a self contained script that includes the data, or use DBD::SQLite to populate a database on the fly. If it's a MySQL problem you can use the same technique of populating the database with test data in your sample script, but that reduces the number of monks who may be able to help diagnose the issue somewhat (they'll probably need a MySQL server available).

    True laziness is hard work
Re: DBI SQL Returns Less than Direct SQL
by Util (Priest) on Oct 18, 2011 at 13:23 UTC

    Your use of $sth->rows() is buggy, and might be confusing the issue. From the DBI docs:

    For SELECT statements, it is generally not possible to know how many rows will be returned except by fetching them all. Some drivers will return the number of rows the application has fetched so far, but others may return -1 until all rows have been fetched. So use of the rows method or $DBI::rows with SELECT statements is not recommended.

      True! But, this count matches the rows returned in results arrays, whether as the result of an EXECUTE or other method.

      The documentation cautions that getting a count of rows might not work for certain drivers but doesn't explicitly say that for MySQL the results are not guaranteed. I had this code after the loop where I processed the results and the row count matches the number of rows returned in arrays (and for other search words, I do get more than 1 row but always less than shown in pure SQL via PHPAdmin and other query processes.)

      Thanks, Dave

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://932010]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (5)
As of 2014-04-21 05:06 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (490 votes), past polls