I'm opening an sqlite database with 
    my $dsn = "dbi:SQLite:$dbname";
    $dbh = DBI->connect(
        $dsn, undef, undef,
        {   PrintError                 => 0,
            RaiseError                 => 1,
            AutoCommit                 => 1,
            sqlite_see_if_its_a_number => 1,
            sqlite_unicode             => 1
        }
        )
        or die("Cannot connect to the database: $DBI::errstr ($DBI::er
+r) \n");
    my $self = { dbh => $dbh };
    die "dbh undef " unless ($dbh);  
    # print "Dbman_sqlite->new\n";
     $self->{dbh}->trace(1, *STDOUT);

[download]

Then I prepare some statements

    my $sql  = "INSERT INTO authors (name, firstname) VALUES (?, ?);";
    $self->{st_insert_authors} = $self->{dbh}->prepare($sql);
    $sql= "UPDATE papers set year=? WHERE rero_id=?";
    $self->{st_update_papers} = $self->{dbh}->prepare($sql);
    $sql = "SELECT authorid FROM authors WHERE name=? AND firstname=?"
+;
    $self->{st_select_authors} = $self->{dbh}->prepare($sql);
  ...

[download]
I use the last statement with 
    $self->{st_select_authors}->execute($name, $firstname) or die  $se
+lf->{st_select_authors}->errstr;
     my $val_ar = $self->{st_select_authors}->fetchall_arrayref( {} );
     my $foundid;
     die Dumper $val_ar;
   #gives an empty array ref

[download]
The problem is that the name + firstname value are in my table... I tried to test if $self->{st_select_authors}->{Active} gives an indication but it seems to be false, even in the case where a select statement works correctly.

The trace is

:\docs\OA\entrees_liste>build_rerodb.pl -f out_181106.txt
    DBI::db=HASH(0x2bc47c4) trace level set to 0x0/1 (DBI @ 0x0/0) in 
+DBI 1.636-ithread (pid 7692)
    <- prepare('INSERT INTO authors (name, firstname) VALUES (?, ?);')
+= ( DBI::st=HASH(0x2bc4d4c) ) [1 items] at Collabs_db.pm line 40
    <- prepare('UPDATE papers set year=? WHERE rero_id=?')= ( DBI::st=
+HASH(0x2bc6294) ) [1 items] at Collabs_db.pm line 42
    <- prepare('SELECT authorid FROM authors WHERE name=? AND firstnam
+e=?')= ( DBI::st=HASH(0x2bc645c) ) [1 items] at Collabs_db.pm line 44
    <- prepare('DELETE FROM papers WHERE rero_id=?')= ( DBI::st=HASH(0
+x2bc65f4) ) [1 items] at Collabs_db.pm line 46
    <- prepare('INSERT INTO papers (rero_id, doi, year) VALUES (?,?, ?
+)')= ( DBI::st=HASH(0x2bc678c) ) [1 items] at Collabs_db.pm line 48
    <- prepare('SELECT rero_id FROM papers WHERE rero_id=?')= ( DBI::s
+t=HASH(0x2bc6924) ) [1 items] at Collabs_db.pm line 50
    <- prepare('DELETE FROM writtenby WHERE rero_id=?')= ( DBI::st=HAS
+H(0x2bc6abc) ) [1 items] at Collabs_db.pm line 52
    <- prepare('SELECT authorid FROM writtenby WHERE rero_id=?')= ( DB
+I::st=HASH(0x2bc6c54) ) [1 items] at Collabs_db.pm line 54
    <- begin_work= ( 1 ) [1 items] at Collabs_db.pm line 294
    <- execute("11350")= ( '0E0' ) [1 items] at Collabs_db.pm line 70
    <- fetchall_arrayref(HASH(0x2aedef4))= ( [ HASH(0x2bbed1c) ] ) [1 
+items] row1 at Collabs_db.pm line 71
    <- execute(undef, 11350)= ( 1 ) [1 items] at Collabs_db.pm line 76
Use of uninitialized value $year in concatenation (.) or string at Col
+labs_db.pm line 77, <$FH> line 1.
    <- execute(11350)= ( '0E0' ) [1 items] at Collabs_db.pm line 85
    <- fetchall_arrayref(HASH(0x2587f34))= ( [ HASH(0x2bba87c) ] ) [1 
+items] row1 at Collabs_db.pm line 86
    <- execute("Normand", " Bruce")= ( '0E0' ) [1 items] at Collabs_db
+.pm line 99
    <- fetchall_arrayref(HASH(0x2505b94))= ( [ ] ) [1 items] at Collab
+s_db.pm line 100
$VAR1 = [];

[download]

Thanks for any help !

François

In reply to sqlite: select statement not working by frazap

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.