fetchrow_array does not yield the same results as the sql query by itself does.

gator_shawn has asked for the wisdom of the Perl Monks concerning the following question:

We have a script that builds a dynamic query to our database. Then it prepares and executes the query as follows:

my $sth = $dbh->prepare("$Select_Statement");
$sth->execute();
[download]

At this point, I print out the sql statement so I can run it myself directly through another DBI interface. SO that output may look like this:

select severity,class,status,date_event,hostname,msg,administrator,sou
+rce,sub_source,rep.server_hndl,rep.event_hndl,rep.date_reception from
+ tec_t_evt_rep rep where rep.date_reception > 1157568225 and rep.date
+_reception < 1157568525  and (  hostname like '%aixjcsa1%' )
[download]

Now, my printing of the sql statement notwithstanding, the program proceeds to step through the results of the sql statement as follows:

while (@data = $sth->fetchrow_array) {
   bleh blah bleh
}
[download]

There are no conditions inside the code block to drop or next on any records. In fact, I'd added a line at the very first line in the while statement to print @data for my troubleshooting processes.
Okay, so the net result here is that if I run the sql_statement in my own DBI script I get records that do not return in the fetchrow_array loop.
I can only assume that the fetchrow is dropping them for some reason.
This process was working until a few weeks ago and we can't determine any reasonable correlation between this changing and other changes we've made.
Any ideas?

Comment on fetchrow_array does not yield the same results as the sql query by itself does. Select or Download Code

Replies are listed 'Best First'.
Re: fetchrow_array does not yield the same results as the sql query by itself does. by graff (Chancellor) on Sep 06, 2006 at 19:21 UTC
According to the DBI man page: If there are no more rows or if an error occurs, then "fetchrow_array" returns an empty list. You should check "$sth->err" afterwards (or use the "RaiseError" attribute) to discover if the empty list returned was due to an error. So, have you tried that? Apart from that, if there are "other changes we've made", maybe it's sufficient that you don't see any "reasonable correlation" to your troubles, but maybe someone else might see something you missed... We won't know unless you tell us a little more about those changes (and any possibly relevant differences between this failing script and your separate DBI script).	[reply]
Re: fetchrow_array does not yield the same results as the sql query by itself does. by imp (Priest) on Sep 06, 2006 at 19:20 UTC
I can't think of any reasons for missing rows offhand. Try fetching all of the data and dumping it, to confirm that DBI is really not providing it: `my $data = $dbh->selectall_arrayref($sql); use Data::Dumper; print STDERR Dumper($data);` [download] If that still fails it is possible that some of the characters being copied + pasted from the debug output to your other interface are not being included (or included correctly).. but the query you reported wouldn't make that my first guess.	[reply] [d/l]
Re: fetchrow_array does not yield the same results as the sql query by itself does. by perrin (Chancellor) on Sep 06, 2006 at 19:48 UTC
Are you using a database with transactions? If so, are you sure all the data you expect to see in that loop was committed when the transaction it runs in was started?	[reply]
Re^2: fetchrow_array does not yield the same results as the sql query by itself does. by Hue-Bond (Priest) on Sep 06, 2006 at 19:54 UTC
But the point of transactions is to perform some work atomically, isn't it? On `commit`, the DB engine arranges to show the results of all queries as if they were executed at one instant in time. So there's no way of seeing the results of part of a transaction. Or am I wrong? -- David Serrano	[reply]
Re^3: fetchrow_array does not yield the same results as the sql query by itself does. by perrin (Chancellor) on Sep 06, 2006 at 20:03 UTC
Isolation levels are more complex than that. Depending on how you have things set, you may not be able to see data that was committed by other processes after the start of a transaction. For more on how this works, see this. It's from the MySQL docs, but PostgreSQL and Oracle work this way too. UPDATE: corrected link	[reply]
Re: fetchrow_array does not yield the same results as the sql query by itself does. by TrekNoid (Pilgrim) on Sep 06, 2006 at 19:58 UTC
Is it possible that your default date format through your DBI connection is different than your default date format through the other script? TrekNoid	[reply]