Ugg.. Long, painful day and night. Hope I'm not missing something stupid...

I've got a simple, four-column SQLite table. What's got me all bent out of shape is that it looks like fetchrow_hashref is behaving differently depending on how many rows (1 or >1) my executed $sth is holding. If there are multiple rows, a following UPDATE query that I'm building with a hash member won't 'prepare.' I know that prolly doesn't make any sense, but ...

I've done my best to bang together a working example of the problem. It's not exactly easy on the eyes, but I've taken the liberty of setting $dbh->trace(1) so you can see what I see. (I did some serious ripping-out-of-stuff to make the size managable.)

Thank you Monks. Code follows...

Apr 17 07:45 -- corrected a typo

Apr 19 04:45 Answer: As it turns out, there's a locking problem here. The first SELECT is still open and being accessed by the fetchrow_hashref(). But, the UPDATE statement was blowing up whenever it tried to execute and would have 'hit'. If I massaged the SQL so that the WHERE would have caused a write not to happen all was well.

So, to fix this, I used fetchall_hashref() and loaded the whole result set to hash. Then, undefined the $sth and moved on -- worked like a dream.

This is *not* how I read the FAQ. Also, it doesn't make sense to me why a read-only SELECT would lock the DB in the first place, but that's what's happening.

Emails sent to SQLite.org and to the DBD::SQLite author. 

#!/usr/bin/perl

use DBI;
use DBD::SQLite;
use strict;
use warnings;

my $dbname = 'upman.db';
my $dbh;

open_db();
process_files(1);
process_files();
exit;

sub process_files {

    my $limit = shift;
    my $sql = "SELECT * FROM files";
    if ($limit){$sql = $sql . " limit $limit";}
    
    my $sth = $dbh->prepare($sql)   
            or die $dbh->errstr;
    $sth->execute()                 
            or die $dbh->errstr;

    while(my $row = $sth->fetchrow_hashref()){
        my $sql = "UPDATE files SET uploadrc='x' "
                . "WHERE locname='" . $row->{locname} . "'";
        my $sth = $dbh->prepare($sql) 
                or die $dbh->errstr;
        $sth->execute()             
                or die $dbh->errstr;
    }
}



sub open_db{
    $dbh = DBI->connect("dbi:SQLite:dbname=$dbname") or die $DBI::errs
+tr;
    while(<DATA>){
        $dbh->do($_) or die $dbh->errstr;
    }
    $dbh->trace(1);
}

__DATA__
DROP TABLE files;
CREATE TABLE files (locname primary key, size, uploadrc, rmtname);
INSERT INTO files VALUES('dat/19980820/ACCT3445',1451,NULL,'D980820.XA
+CCT3445');
INSERT INTO files VALUES('dat/19980820/ACCT3470',5961,NULL,'D980820.XA
+CCT3470');
INSERT INTO files VALUES('dat/19980820/ACH0232',37723,NULL,'D980820.XA
+CH0232');
INSERT INTO files VALUES('dat/19980820/ACH4498',5612,NULL,'D980820.XAC
+H4498ACH');
INSERT INTO files VALUES('dat/19980821/TD7605G',181755,NULL,'D980821.X
+TD7605GL');
INSERT INTO files VALUES('dat/19980821/TD7695ReportLegend',3389,NULL,'
+D980821.XTD7695');
CREATE UNIQUE INDEX rmtname ON files(rmtname);

[download]

In reply to Fetchrow_hashref behaves differently when >1 row in $sth? by pboin

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.