I ran your program and saw the selected rows. Please double check your result.
On Wed Oct 22 02:20:41 2014, croberts@gilsongraphics.com wrote:
Show quoted text> Sorry I wasn't clear. Even if SELECT returns '0E0', even when I do fetch, I
> get no records.
>
> I have removed some code below so the program will try to fetch records even
> when SQLite returns '0E0'. Here is the new code.
> Chuck
>
> ### BEGIN CODE ###
> use warnings;
> use strict;
>
> use DBI;
> use Data::Dumper;
> my($dbsql,$stsql,$rvsql,$sql,$i,$j,$s,@a,@b);
> my($invlistfn,$invlisttable,$procname);
>
> $invlistfn = 'testsm.db';
> $invlisttable='invlist';
> $procname='main';
> unlink($invlistfn);
>
> if (! -e $invlistfn)
> {
> $dbsql=DBI->connect("dbi:SQLite:dbname=$invlistfn", '', '', {
> RaiseError=>1 });
>
> $sql="create table $invlisttable (mykey text PRIMARY KEY, myparts
> text)";
> $sql.=';';
> $dbsql->do($sql) or die "ERROR in do: ".$dbsql->errstr;
> if ($dbsql->errstr)
> {
> writeerr($dbsql->errstr);
> exit 1;
> }
> $dbsql->disconnect;
> }
> $dbsql=DBI->connect("dbi:SQLite:dbname=$invlistfn", '', '', {
> RaiseError=>1 });
> print "DB filename: ".$dbsql->sqlite_db_filename."\n";
> #print $dbsql->{errstr}."\n";
>
> insertrec('c4','hello');
> insertrec('c5','rec 2');
> insertrec('c6','rec 3');
>
> # Uncomment these lines (and comment listhist2() below) and
> # it should work. Select does not work
> # inside a subroutine.
> $sql="SELECT mykey, myparts FROM $invlisttable ";
> $sql.="ORDER BY mykey";
> $sql.=";";
> print "$sql\n"; # DEBUG
> $stsql=$dbsql->prepare($sql);
> $rvsql=$stsql->execute(); # Always returns 0E0.
> if ($rvsql<0)
^^ This is a wrong assumption. The return value of execute should be true or false. (Don't compare with a number.)
Show quoted text> {
> $s="$procname ERROR: $DBI::errstr";
> print "$s\n";
> }
> # elsif ($rvsql==0)
> # {
> # $s="$procname: No rows returned. rvsql=".$rvsql;
> # print"$s\n";
> # }
> else {
> $s="$procname: $rvsql rows found.";
^^ This is also a wrong assumption. $rvsql is not a number of selected rows. It only tells if execute returns error or not.
Show quoted text> print "$s\n";
> while(my @row = $stsql->fetchrow_array())
> {
> $s=join(',',@row);
> print "$s\n";
^^ See the output of this line.
Show quoted text> } # while
> }
>
> #listhist2();
>
> print "End program $0.\n";
> exit; # Main pgm.
> ###########################################################################
> # In:
> # Out:
> sub insertrec
> {my($f1,$f2)=@_;
> my(@a,@b,$i,$j,$procname,$s,$t);
>
> $procname="insertrec";
>
> $sql="insert into $invlisttable (mykey, myparts) VALUES ('$f1','$f2')";
> $sql.=";";
> #$dbsql->do($sql);
> $stsql=$dbsql->prepare($sql);
> $rvsql=$stsql->execute();
> $s="$procname: rv=$rvsql";
> print "$s\n";
>
> return; # insertrec
> }
> ###########################################################################
> # In: indate (not used)
> # Out:
> # List all records in estimatehist.db. Run with -listhist
> sub listhist2
> {my($indate)=@_;
> my(@a,@b,$i,$j,$procname,$s,$t);
> my($colspace,$stsql,$rvsql,$thdr,@fields,@fmtarr,@row);
>
> $procname="listhist2";
> print "\n$procname ********************\n";
>
> $colspace=' ';
> ##########################################
> # Select data and show all records.
>
> $sql="SELECT \* FROM $invlisttable";
> $sql.=";";
> print "$sql\n"; # DEBUG
> $stsql=$dbsql->prepare($sql);
> $rvsql=$stsql->execute(); # Always returns 0E0.
> if ($rvsql<0)
> {
> $s="$procname ERROR: $DBI::errstr";
> print "$s\n";
> }
> elsif ($rvsql==0)
> {
> $s="$procname: No rows returned. RV=".$rvsql;
> print"$s\n";
> }
> else {
> $s="$procname: $rvsql rows found.";
> print "$s\n";
> while(my @row = $stsql->fetchrow_array())
> {
> $s=join(',',@row);
> print "$s\n";
> } # while
> }
>
> return; # listhist2
> }
> ###########################################################################
> # In:
> # Out:
> sub xx
> {my($l)=@_;
> my(@a,@b,$i,$j,$procname,$s,$t);
>
> $procname="";
>
>
> return; #
> }
> ### END CODE ###
>
> -----Original Message-----
> From: Kenichi Ishigaki via RT [mailto:bug-DBD-SQLite@rt.cpan.org]
> Sent: Tuesday, October 21, 2014 1:15 PM
> To: croberts@gilsongraphics.com
> Subject: [rt.cpan.org #99614] SELECT always returns '0E0'
>
> <URL:
https://rt.cpan.org/Ticket/Display.html?id=99614 >
>
> This is not a bug. Refer to the DBI manual (
>
https://metacpan.org/pod/DBI#execute ). What's wrong is your assumption that
> "execute" should return a number of selected rows. You just need to see if
> the return value of "execute" is true or not. (If you're new to DBI, "0E0"
> is a true value, unless you evaluate it as a number.)
>
> HTH.
>
> On Wed Oct 22 01:35:31 2014, croberts@gilsongraphics.com wrote:
> > Specs:
> >
> > OS: linux, Perl Debug flags: 1855, Perl ver: 5.018002 Linux ubuntucomp
> > 3.13.0-32-generic #57-Ubuntu SMP Tue Jul 15 03:51:08 UTC
> > 2014 x86_64 x86_64 x86_64 GNU/Linux
> > Log::Rolling version: 1.02
> > Mail::Sender version: 0.8.23
> > MIME::Parser version: 5.505
> > Net::POP3 version: 2.29
> > DBI version: 1.63
> > SQLite version via $dbh->{sqlite_version}: 3.8.4.1
> > DBD::SQLite::VERSION=1.42
> > DB file: /home/chuck/perl/gilson/dataserv/invlist.db
> > DBD::Pg version: 3.3.0
> >
> > I have a test program where I create a new table, insert some records,
> > then select them. SELECT via SQLite always returns '0E0' which
> > indicates the select was successful but returned no records. How can
> > it return no records when I'm asking for all records?
> >
> > I've read about 20 pages from Google search and really haven't found a
> > solution to my problem. I looked through the changes for SQLite since
> > 1.42 and none seem to address this issue.
> >
> >
> > Thank you.
> >
> > Sample program below.
> >
> > use warnings;
> > use strict;
> >
> > use DBI;
> > use Data::Dumper;
> > my($dbsql,$stsql,$rvsql,$sql,$i,$j,$s,@a,@b);
> > my($invlistfn,$invlisttable,$procname);
> >
> > $invlistfn = 'testsm.db';
> > $invlisttable='invlist';
> > $procname='main';
> > unlink($invlistfn);
> >
> > if (! -e $invlistfn)
> > {
> > $dbsql=DBI->connect("dbi:SQLite:dbname=$invlistfn", '', '', {
> > RaiseError=>1 });
> >
> > $sql="create table $invlisttable (mykey text PRIMARY KEY, myparts
> > text)";
> > $sql.=';';
> > $dbsql->do($sql) or die "ERROR in do: ".$dbsql->errstr;
> > if ($dbsql->errstr)
> > {
> > writeerr($dbsql->errstr);
> > exit 1;
> > }
> > $dbsql->disconnect;
> > }
> > $dbsql=DBI->connect("dbi:SQLite:dbname=$invlistfn", '', '', {
> > RaiseError=>1 }); print "DB filename:
> > ".$dbsql->sqlite_db_filename."\n";
> > #print $dbsql->{errstr}."\n";
> >
> > insertrec('c4','hello');
> > insertrec('c5','rec 2');
> > insertrec('c6','rec 3');
> >
> > # Uncomment these lines (and comment listhist2() below) and # it
> > should work. Select does not work # inside a subroutine.
> > $sql="SELECT mykey, myparts FROM $invlisttable "; $sql.="ORDER BY
> > mykey"; $sql.=";"; print "$sql\n"; # DEBUG
> > $stsql=$dbsql->prepare($sql); sleep 5; $rvsql=$stsql->execute(); #
> > Always returns 0E0.
> > sleep 5;
> > if ($rvsql<0)
> > {
> > $s="$procname ERROR: $DBI::errstr";
> > print "$s\n";
> > }
> > else {
> > $s="$procname: $rvsql rows found.";
> > print "$s\n";
> > while(my @row = $stsql->fetchrow_array())
> > {
> > $s=join(',',@row);
> > print "$s\n";
> > } # while
> > }
> >
> > #listhist2();
> >
> > print "End program $0.\n";
> > exit; # Main pgm.
> > ######################################################################
> > #####
> > # In:
> > # Out:
> > sub insertrec
> > {my($f1,$f2)=@_;
> > my(@a,@b,$i,$j,$procname,$s,$t);
> >
> > $procname="insertrec";
> >
> > $sql="insert into $invlisttable (mykey, myparts) VALUES
> > ('$f1','$f2')"; $sql.=";"; #$dbsql->do($sql);
> > $stsql=$dbsql->prepare($sql); $rvsql=$stsql->execute();
> > $s="$procname: rv=$rvsql";
> > print "$s\n";
> >
> > return; # insertrec
> > }
> > ######################################################################
> > #####
> > # In: indate (not used)
> > # Out:
> > # List all records in estimatehist.db. Run with -listhist sub
> > listhist2 {my($indate)=@_; my(@a,@b,$i,$j,$procname,$s,$t);
> > my($colspace,$stsql,$rvsql,$thdr,@fields,@fmtarr,@row);
> >
> > $procname="listhist2";
> > print "\n$procname ********************\n";
> >
> > $colspace=' ';
> > ##########################################
> > # Select data and show all records.
> >
> > $sql="SELECT \* FROM $invlisttable";
> > $sql.=";";
> > print "$sql\n"; # DEBUG
> > $stsql=$dbsql->prepare($sql);
> > $rvsql=$stsql->execute(); # Always returns 0E0.
> > if ($rvsql<0)
> > {
> > $s="$procname ERROR: $DBI::errstr";
> > print "$s\n";
> > }
> > elsif ($rvsql==0)
> > {
> > $s="$procname: No rows returned. RV=".$rvsql;
> > print"$s\n";
> > }
> > else {
> > $s="$procname: $rvsql rows found.";
> > print "$s\n";
> > while(my @row = $stsql->fetchrow_array())
> > {
> > $s=join(',',@row);
> > print "$s\n";
> > } # while
> > }
> >
> > return; # listhist2
> > }
> > ######################################################################
> > #####
> > # In:
> > # Out:
> > sub xx
> > {my($l)=@_;
> > my(@a,@b,$i,$j,$procname,$s,$t);
> >
> > $procname="";
> >
> >
> > return; #
> > }
> > ### end program ###
