[Dbix-class] SQLite problem (cannot update in iterator loop)

[Brandon Black]

On 1/19/06, Matt S Trout <dbix-class at trout.me.uk> wrote:
> On Thu, Jan 19, 2006 at 01:00:20PM -0500, Christopher H. Laco wrote:
> > Dan Sully wrote:
> > > * Daisuke Murase shaped the electrons to say...
> > >
> > >> This script shows following error:
> > >>
> > >>    DBD::SQLite::st execute failed: database table is locked(1) at
> > >>    dbdimp.c line 398 at
> > >>    /usr/local/share/perl/5.8.4/DBIx/Class/Storage/DBI.pm line 187.
> > >>    Use of uninitialized value in numeric eq (==) at
> > >>    /usr/local/share/perl/5.8.4/DBIx/Class/Row.pm line 116.  Can't
> > >>    update DBIC::Test=HASH(0x85a8c3c): row not found at dbic_test.pl
> > >>    line 20
> > >
> > > This is an unfortunate limitation with DBD::SQLite. Not SQLite itself,
> > > which
> > > can handle multiple readers and one writer.
> > >
> > > -D
> >
> >
> > There is some DBIC-ness in this problem as well.
> >
> > When I converted Handel to a DBIC branch, I had the same problem as well
> > with iterators (foreach items->next). But, if I changed them to use an
> > array instead (@items = ->items; foreach item @items)), all worked well.
>
> Yep. That's because a DBIx::Class resultset is actually a resultset against
> the database. So when you call ->next it fetches a record and inflates it.
>
> That means that in DBD::SQLite you've got an open reader against the table,
> which locks it so it can't be altered, thus giving you inconsistent data.
>
> It doesn't show up on Class::DBI because Class::DBI fetches all data from
> the sth into an arrayref, instantiates the Iterator with that, and throws
> the sth away. This, as you can imagine, works really well for iterating over
> thousands of results ...
>

So the DBD::SQLite (but not SQLite itself) deficiency referred to
above is that you cannot loop over a SELECT with $sth->fetchrow_xxx
and do a seperate $dbh->do('anything') inside the loop?

-- Brandon