1. Perl 5.18 on Ubuntu 14.04LTS.
  2. dbish 11.95.
  3. DBI 1.63.
  4. SQLite 3.8.4.1
  5. This is the first time I've use SQLite, but I have 5 other programs which use DBD::Pg for a Postgresql db just fine.
  6. I'm using Perl global variables $dbsql and $stsql for the SQLite connection.
  7. The subroutine doaddsqlite() is supposed to add the records.
  8. The directory containing the test.db file and the Perl script has 0777 permissions. The file that holds the data is test.db. The tablename inside the file is 'histdata'. See the CREATE command for the table structure.
  9. PROBLEM: SELECT returns '0E0' instead of any records. This happens first inside doaddsqlite() when it tries to see if the record already exists in the table.
  10. Also my autoincrement serial field called 'ID' always contains 'undef'. Am I defining it wrong?

I'm having problems doing a SELECT on a file created by SQLite. Creating the table in Perl works, and inserting records returns no error. Using dbish I can see the records in the file, but in my Perl program, whenever I do a SELECT I get a result of '0E0' which indicates the command was successful, and no records were returned.

Could it be a permissions problem? The file that is created (by Perl) has permissions of: rw-r--r--.

Does SQLite create a temp file inside test.db so it needs more permissions?

Thank you! Perl code follows. I've tried to simplify it somewhat.

EDIT: I've tried several suggestions, but my code does not return an error nor does it return any records. I guess I'm stuck for now. 

#!/usr/bin/perl

########## Pragmas ##########
use warnings;
use strict;
my($s);

$s="=" x 60;
print "$s\n";

$s="Data::Dumper";
eval("use $s;");
if ($@)
    {
    die "$0: ERROR: $s not installed.\n";
    }

$s="DBI";
eval("use $s;");
if ($@)
    {
    die "$0: ERROR: $s not installed.\n";
    }
$s="DBD::Pg";
eval("use $s;");
if ($@)
    {
    die "$0: ERROR: $s not installed.\n";
    }
$s="DBD::SQLite";
eval("use $s;");
if ($@)
    {
    die "$0: ERROR: $s not installed.\n";
    }

my ($USERNAME)=$ENV{USER};

# For time use $s=localtime();
########## Variables ##########
our(@lin);
my($DEBUGDETAIL,$MODVER);
my($filename,$SEP);
my($pcnt);
my(@a,@b,@k,@l,$d,$i,$j,$k,$lin,$t,$z);
my($page);
my($errfile,$fn,$histfn,$histtablename,$sql);
my($day,$mo,$hr,$min,$year,$sec);

# Database stuff
my($dbserver,$dbuser,$dbpw,$dbname,$dsn);
# SQLite handles
my($dbsql,$stsql,$rvsql,$ssql);

my(@errors,@row);

$SEP=chr(9); # chr(9)=tab.
$DEBUGDETAIL=0; # 1=show every record.

my $SUBVER=1; # Update this.
my $VER="ver 2014-1016.".sprintf("%03d",$SUBVER);


########## Begin main ##########

print "\n"; # print blank line to separate warning messages from our m
+essages.
if (!(defined($ARGV[0])))
    {
    print "$VER Usage: \nperl $0 ok ";
    $k='  '; # Prefix spaces
    $s="\n";
    $s.=$k."Use 'ok' to run program.\n";
    $s.="\n";

    print "\n";
    exit;
    }

# Connect to DBD::SQLite.
$histfn='test.db';
$histtablename='histdata';
if (-e $histfn)
    {
    $dbsql=DBI->connect("dbi:SQLite:dbname=$histfn",undef, undef, unde
+f);
    if (! $dbsql)
        {
        $s="ERROR opening SQLite $histfn. Perhaps the file is new: $!"
+;
        writeerr($s);
        exit 1;
        }
    }
else { # Create table
    $dbsql=DBI->connect("dbi:SQLite:dbname=$histfn",undef, undef, unde
+f); # Make new table
    if (! $dbsql)
        {
        $s="ERROR opening SQLite $histfn. Perhaps the file is new: $!"
+;
        writeerr($s);
        exit 1;
        }
    $ssql = <<ENDSQL;
CREATE TABLE $histtablename (
ID serial PRIMARY KEY,
Mydate date,
Category text,
Subcat text,
Myvalue numeric DEFAULT 0,
Myformat text DEFAULT ''
)
;
ENDSQL

    $stsql= $dbsql->prepare($ssql);
    if (! $stsql)
        {
        $s="ERROR: prepping $histfn failed at dbsql->prepare(). ";
        $s.="\nSQL=$ssql";
        writeerr($s);
        exit 1;
        }
    $rvsql=$stsql->execute();
    if ($rvsql<0)
        {
        $s="ERROR: rvsql less than zero: $DBI::errstr";
        writeerr($s);
        exit 1;
        }
    elsif ($rvsql==0)
        {
        $s="SQLite: table $histtablename created in $histfn.";
        writeerr($s);
        }
    }

$s="SQLite version: ".$dbsql->{sqlite_version};
writeerr($s);

####################
# Now insert record.
doaddsqlite('2014-09-01','OPEN','SUBCAT',1,'main');
doaddsqlite('2014-09-02','OPENPR','SUBCAT',2,'main');

####################
# Now do select.
listhist('2014-09-01');

mainexit:

$stsql->finish();
$dbsql->disconnect();

exit; # Main program
######################################################################
+####
######################################################################
+#####
######################################################################
+#####
# Add record to sqlite db. First check if record exists. If it does,
# show err msg and do not add data.
# These fields required: $indate, $cat, $subcat, $myvalue.
# Optional parameters: $estmtrid, $dept.
# In: $indate: report date
#     $cat: category
#     $subcat: subcategory
#     $myvalue: could be a count, dollar amt, or a number.
#     $calledby: subroutine that called doaddsqlite().
# Out:
sub doaddsqlite
{my($indate,$cat,$subcat,$myvalue,$calledby)=@_;
my(@a,@b,$i,$j,$procname,$s,$t,$ssql);

$procname="doaddsqlite";

if (($indate) and (len($indate)==0))
    {
    $s="$procname ERROR: Date was not provided. Cannot update database
+.";
    writeerr($s);
    exit 1;
    }
if (($cat) and (len($cat)==0))
    {
    $s="$procname ERROR: Category was not provided. Cannot update data
+base.";
    writeerr($s);
    exit 1;
    }
if (($subcat) and (len($subcat)==0))
    {
    $s="$procname ERROR: Subcategory was not provided. Cannot update d
+atabase.";
    writeerr($s);
    exit 1;
    }
if (($myvalue) and (len($myvalue)==0))
    {
    $s="$procname ERROR: A value was not provided. Cannot update datab
+ase.";
    writeerr($s);
    exit 1;
    }
$cat=uc($cat);
$subcat=uc($subcat);

##########################################
# First make sure this data record does not exist.
$ssql='';
$ssql="SELECT Mydate, Category, Subcat";
$ssql.=" FROM $histtablename";
$ssql.=" WHERE";
$ssql.=" (Mydate = '$indate')";
$ssql.=" AND (Category = '$cat')";
$ssql.=" AND (Subcat = '$subcat')";
$ssql.=";";

$stsql= $dbsql->prepare($ssql);
if (! $stsql)
    {
    $s="$procname: Checking for existing record, dbsql->prepare failed
+. SQL=$ssql";
    $s.=" Called by: $calledby";
    writeerr($s);
    exit 1;
    }
$rvsql=$stsql->execute();
if ($rvsql<0)
    {
    $s="$procname ERROR: rvsql less than zero: $DBI::errstr";
    if (len($calledby)>0)
        {
        $s.=" Called by: $calledby";
        }
    writeerr($s);
    exit 1;
    }
elsif (($rvsql>=1) )
    {
    $s="$procname ERROR: This record exists. Could not overwrite data.
+";
    if (len($calledby)>0)
        {
        $s.=" Called by: $calledby";
        }
    writeerr($s);
    $stsql->finish();
    return;
    }

##########################################
# Now insert data.
$ssql="INSERT INTO $histtablename (Mydate, Category, Subcat ";
$ssql.=", Myvalue) VALUES ";
$ssql.="('$indate', '$cat', '$subcat'";
$ssql.=", $myvalue)";
$ssql.=";";

$stsql= $dbsql->prepare($ssql);
if (! $stsql)
    {
    $s="$procname: INSERT data, dbsql->prepare failed. SQL=$ssql";
    $s.=" Called by: $calledby";
    writeerr($s);
    exit 1;
    }
$rvsql=$stsql->execute();
if ($rvsql<0)
    {
    $s="$procname ERROR: rvsql less than zero: $DBI::errstr";
    writeerr($s);
    exit 1;
    }
else
    {
    $s="$procname: Data added to history.  $indate, $cat, $subcat, $my
+value";
    writeerr($s);
    }


return; # doaddsqlite
}
######################################################################
+#####
# In: indate (not used)
# Out:
# List all records in estimatehist.db. Run with -listhist
sub listhist
{my($indate)=@_;
my(@a,@b,$i,$j,$procname,$s,$t);
my($colspace,$rvsql,$thdr,@fields,@fmtarr,@row);

$procname="listhist";
$colspace='  ';

##########################################
# Select data and show all records.

@fields=('Mydate','Category','Subcat','Myvalue');
@fmtarr=('%-10s','%-10s','%-10s','%8s');
$thdr="\n";
for ($j=0; $j<=$#fields; $j++)
    {
    $thdr.=sprintf($fmtarr[$j],$fields[$j]).$colspace;
    } # for j

# ORDER BY Mydate, Category, Subcat

$sql = <<ENDSQL;
SELECT *
FROM $histtablename
;
ENDSQL

$stsql=$dbsql->prepare($sql);
if (! $stsql)
    {
    $s="$procname ERROR: Could not prepare sql. SQL=$sql";
    writeerr($s);
    exit 1;
    }
$rvsql=$stsql->execute(); # Always returns 0E0.
$DEBUGDETAIL=0;
if ($DEBUGDETAIL==1)
    {
    writeerr($thdr);
    }
if ($rvsql<0)
    {
    $s="$procname ERROR: $DBI::errstr";
    writeerr($s);
    }
elsif ($rvsql==0)
    {
    $s="$procname: No rows returned.";
    writeerr($s);
    }
else {
    $s="$procname: $rvsql rows found.";
    writeerr($s);
    while(my @row = $stsql->fetchrow_array())
        {
        @row=convundefarr(\@row); # Convert undefs to blank.
        for ($j=0; $j<=$#fields; $j++)
            {
            $s.=sprintf($fmtarr[$j],trim($row[$j])).$colspace;
            } # for j
        writeerr($s);
        } # while
    }


return; # listhist
}
######################################################################
+#####
# writeerr() normally writes errors to a file but for this example it
# prints to a screen.
sub writeerr
{my ($s)=@_;

print "$s\n";

return;
}
######################################################################
+#####
sub len
{my($l)=@_;

return length($l);
}
######################################################################
+#####
######################################################################
+#####
######################################################################
+#####

[download]

In reply to SQLite select won't return SELECTed records by bulrush

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.