LPM: DBI woes

[Rich Bowen]

Wesley wrote:
> 
> Rich,
>         When it prints every other record, does it print a blank line in
> between?  If so, then $day is empty and there's something wrong with the
> data or the way it's being fetched.  If not, I wonder if fetchrow_array
> could possibly be grabbing two days at a time, and discarding the second
> one since you just have one scalar to hold the results.  I've always
> used while ($sth->fetch) and haven't touched fetchrow_array, but I
> always thought fetchrow_array was for when you wanted to get several
> fields at once. 

Well, one or more. ->fetch is an alias to ->fetchrow_arrayref, and so
returns an array reference, which is not really what I want.
fetchrow_array returns one row of the select as an array. If you only
selected one field, it returns that one field in an array, which is what
I want. I use the technique all the time.

> Looks like you're just getting one value each time
> through the loop.  Single-stepping through the loop with a debugger
> might help here.

I might end up doing that, since this is some code in which speed is
pretty important. Trying to compensate for the ghastly slowness of the
MS Access ODBC driver.

>         So in a nutshell, I would try changing that one line to:
>         $day = $sth->fetch;

I'm confused on this one. Now $day is an array reference, right? Do you
use this in actual code? That is, is $day actually a field from the
database. Does the method do some sort of wantarray in there, and intuit
what you want?

> as long as you're sure it will always return a record and don't have to
> test for that possibility.  I would also use $dbh->prepare_cached as
> David suggested because I think the most recent DBI docs recommend it,
> though I don't think that will affect your problem one way or the
> other.  OTOH it might!  :-)

Yeah. I did not quite understand the difference between prepare and
prepare_cached. It seems that the only difference is that if you re-call
the statement handle with the same arguments, it does not actually
contact the database, but just returns the value it got last time. While
I can certainly see that there are situations where this would be
useful, this is not one of them. Now that I am aware of it, I can see
other places where I'd like to use it, though.
 
>         Another option would be to bind the column ahead of time, just for the
> sake of doing it differently.  I think this would look something like:
> 
> $sth = $dbh->prepare_cached($sql);
> $sth->bind_col(\$day);
> foreach $ID (@conflicts)
>         $sth->execute($ID);
>         $sth->fetch;
>         print "$day\n";
> }
> only with proper syntax etc.

Nope, can't do that. You have to execute before you can bind_columns.
Darn.

Thanks for the help, but I'm still befuddled.

Rich
-- 
http://www.ApacheUnleashed.com/
Lexington Perl Mongers - http://lexington.pm.org/
PGP Key - http://www.rcbowen.com/pgp.txt