bulrush has asked for the wisdom of the Perl Monks concerning the following question:

  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]

Replies are listed 'Best First'.
Re: SQLite select wont' return records
by Corion (Patriarch) on Oct 16, 2014 at 11:53 UTC

    Are you aware that Perl and DBI both supply ample error checking methods that could help you simplify your code vastly?

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

    [download]

    can be replaced by simply

    use DBI;

    [download]

    Also, you don't need to load the DBD module for the database. DBI will do that for you.

    If you want to check programmer usage of your subroutines, see die and/or Carp::croak for how to replace

    if (($cat) and (len($cat)==0))
    {
    $s="$procname ERROR: Category was not provided. Cannot update data
+base.";
    writeerr($s);
    exit 1;
    }

    [download]

    by them.

    Also, you should change your connect command for DBI to be

    my $dbh= DBI->connect($dsn, $user, $password, { RaiseError => 1, Print
+Error => 0 });

    [download]

    That way, DBI will automatically notify you of all database errors and stop the program.

    As your program runs, it seems to me that the database table is left empty. Maybe consider re-checking that the rows actually get filled.

    For printing the results of a SELECT statement, I wrote DBIx::RunSQL::format_results, which outputs the results in table form. Maybe you can adapt the code from the routine to your needs.

[reply]
[d/l]
[select]
      I can't use die because all errors need to be logged to a file. My writeerr() routine usually logs errors to a file but I simplified it for this post to only do a print.

      Also, the table was not created with a username or password. So why would I need them? This is a table stored in a file in the same dir as the Perl program.

      And I thought the docs said that RaiseError was on by default.

      Ok I added the RaiseError and PrintError options where you suggested. I got no errors printed and the same thing happens: In doaddsqlite() no records are selected so dupe records are added to the table. dbish still shows there are records in the table. I double checked.

[reply]

        Either reopen STDERR to the file or use File::Tee to duplicate the output to a file if you want errors to (also) go to a file.

        If you don't need a username, don't pass one in. Also note that SQLite has no concept of permissions at all.

[reply]
        I can't use die because all errors need to be logged to a file.

        Actually, no. Think of die as though it were an exception throw and use eval to catch errors:

        eval {
    ...
    return 1;
} or do {
    my $err = $@;
    WriteErr("$err");
};

        [download]
        Perl is the programming world's equivalent of English
[reply]
[d/l]
Re: SQLite select wont' return records
by roboticus (Chancellor) on Oct 16, 2014 at 12:04 UTC

    bulrush:

    Based on your description, I'd guess that when you're running the script:

    • you're not accessing the same SQLite database that you're using when you're using dbish, or
    • your select criteria has an error causing nothing to be selected.

    Your script is overly complicated, you repeat yourself a good bit, so you should perhaps try a simple experiment without all the extraneous stuff. Something like:

    use warnings;
use strict;
use DBI;
use Data::Dumper;
my $histfn = >>>put a path here for your temporary/test database<<<
my $dbsql=DBI->connect("dbi:SQLite:dbname=$histfn", { RaiseError=>1 })
+; 

$dbsql->do(q{create table foo (id int, msg text)});
$dbsql->do(q{insert into foo VALUES (1,'hello')});
my $ra = $dbsql->selectall_arrayref('select * from foo');
print Dumper($ra);

    [download]

    Run it, and see if it is able to run successfully and print records. If so, then make a copy of your program and comment out chunks of it until it's nearly as simple as this one, and get that working. Then uncomment bits here and there until it breaks again. That last bit you uncommented will have a flaw in it.

    The way I comment chunks out is to insert pod comments like this:

    use warnings;
use strict;

=h1 Does perl even work on this box?

use DBI;
use Data::Dumper;
my $histfn = >>>put a path here for your temporary/test database<<<
my $dbsql=DBI->connect("dbi:SQLite:dbname=$histfn", { RaiseError=>1 })
+; 

$dbsql->do(q{create table foo (id int, msg text)});
$dbsql->do(q{insert into foo VALUES (1,'hello')});
my $ra = $dbsql->selectall_arrayref('select * from foo');
print Dumper($ra);

=cut

print "hello, world!\n";

    [download]

    So as you uncomment, you simply move your =h1 and =cut lines accordingly.

    Update: Made the correction reported by bulrush.

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

[reply]
[d/l]
[select]
      Thanks. Well, your code works on my machine except you have to change your INSERT line to this: 
      $dbsql->do(q{insert into foo VALUES (1,'hello')});

      [download]
      And why don't I have to do a prepare? I know that $dbsql->selectall_arrayref() is a valid method but other examples did basically this: 
      $stsql= $dbsql->prepare($ssql);
$rvsql=$stsql->execute();

      [download]
      Which format is required in which situations?
[reply]
[d/l]
[select]

        bulrush:

        The $DBH->do(...) method automatically combines the prepare and execute into a single operation. It's quick and dirty and handy for quick tests and oddball operations that you do only once in your program (such as table creation). For actual data manipulations, I generally prefer to use prepare/execute, but since this was a quick test to verify your installation, I didn't worry about it.

        So to answer your question: You can do it either way, there aren't requirements as such. BUT the prepare/execute format for data manipulation offers some advantages that easily outweigh the slightly larger coding effort: You can get a lot of safety by using placeholders, you can get better performance if your database compiles statements for reuse. Consider this:

        print "Please enter the last name:\n";
my $user_data = <>;
chomp $user_data;

$DBH->do("select * from people where last_name='$user_data'");

        [download]

        So if someone enters a last name like:

        Jones'; drop table people; --

        [download]

        Then your application won't work very well any more. So if you need to use data provided by a user (via input, a web application or whatever), you need to be careful that your data doesn't cause a serious problem. DBI covers you by offering placeholders: Rather than you having to worry about the quoting rules for the database code, you just use a ? and let DBI do the quoting for you:

        print "Please enter the last name:\n";
my $user_data = <>;
chomp $user_data;

my $STH = $DBH->prepare("select * from people where last_name=?");
$STH->execute($user_data);

        [download]

        Now if the user enters that last name, you'll simply get no data (unless you happen to have such a strangely-named person in your database).

        Now on to the performance aspects: when you run an SQL statement, the database can spend a significant amount of time determining out what it's going to do, which indexes it needs to reference and/or update. Some databases will precompute all that information for an SQL statement, and then use that same plan to execute multiple SQL statements. That can save a *lot* of time in some cases. For example, what if your application was reading last names from a file instead of prompting from the user:

        print "Please enter the file name:\n";
my $user_data = <>;
open my $FH, '<', $user_data;
while (my $last_name = <$FH>) {
    chomp $last_name;
    my $STH = $DBH->prepare("select * from people where last_name=?");
    $STH->execute($last_name);
}

        [download]

        If you have a thousand names in your file, then it will figure out how to do that statement a thousand times, and then do it a thousand times. But by moving one statement, you can get a performance boost:

        print "Please enter the file name:\n";
my $user_data = <>;
open my $FH, '<', $user_data;
my $STH = $DBH->prepare("select * from people where last_name=?");
while (my $last_name = <$FH>) {
    chomp $last_name;
    $STH->execute($last_name);
}

        [download]

        Now the database need only figure out how to do the statement once, and then just do it for a thousand times. So you get safety and a possible performance boost. Note: not all databases will give you a performance boost, but you still get the safety. But if you use placeholders, then if you switch to a database that does precompilation of statements, you'll get a free boost.

        Since you don't create a thousand tables at once, and the database couldn't benefit from precompilation in that case anyway, using the do method is just fine.

        Update: Fixed broken code tag.

        ...roboticus

        When your only tool is a hammer, all problems look like your thumb.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom