Perl or DBI error?

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

I seek insight from the monastery in determining if the problem described below is perl or DBI or something else altogether.

I have two perl files (sel_all.pl, mod_schema.pl) and an empty SQLite database (leavebal.db). I use mod_schema.pl to create and then display tables in the database.

        
use strict;    
use warnings;
use 5.18.2;
use DBI;

#my $data_source = "/Documents/Perl/leavebal.db";
my $dbh = DBI->connect('dbi:SQLite:dbname=leavebal.db', undef, undef, 
                {RaiseError =>1, AutoCommit =>0}) or die $DBI::errstr;

my $sth = $dbh->prepare('SELECT name FROM sqlite_master where type="ta
+ble"');
$sth -> execute();

my @data;
say "Current tables are:";
while (@data = $sth->fetchrow_array())
    {
        print "\n";    #$data1[0] $data1[1] $data1[2] $data1[3]";
        say @data;
    }

$sth->finish;

 my $sql = <<'END SQL';
    CREATE TABLE Users(
        UsersID    integer primary key,
        UsersFirstName varchar(100),
        UsersLastName varchar(100),
        UsersEmail varchar(100) unique not null,
        UsersPWD varchar (20),
        UsersAccrualRate integer,
        UsersDate datetime DEFAULT Current_Timestamp
        );
END SQL

$dbh->do($sql);

my$sql1 = <<'End_SQL';
    
    Create TABLE LeaveBal(
        LeaveBalID integer primary key,
        UsersID integer,
        Vac integer,
        Sick integer,
        HolCred integer,
        P10 integer,
        PersHol integer,
        Excess integer,
        PDD integer,
        ITO integer
        LeaveBalDate datetime DEFAULT Current_Timestamp,
        FOREIGN KEY (UsersID) References Users(UsersID)
    );
End_SQL
    
$dbh->do($sql1);
                
my $sql2 = <<'EndSQL';    
    Create TABLE LeaveUse(
        LVUseID integer primary key,
        LeaveBalID integer,
        Amount integer,
        DayUsed varchar(20),
        MonthUsed varchar(20),
        YearUsed varchar(5),
        LeaveNotes varchar(255),
        LeaveUseDate datetime DEFAULT Current_Timestamp,
        FOREIGN KEY (LeaveBalID) References LeaveBal(LeaveBalID)
    );    
EndSQL

$dbh->do($sql2);
                    
my $sth2 = $dbh->prepare('SELECT name FROM sqlite_master where type="t
+able"');
$sth2 -> execute();

my @data2;
say "Current tables are:";
while (@data2 = $sth2->fetchrow_array())
    {
        print "\n";    
        say @data2;
    }

$sth2->finish;

$dbh->disconnect;
[download]

After running mod_schema.pl I independently varify the changes with sel_all.pl. It selects and displays all tables.

use strict;    
use warnings;
use 5.18.2;
use DBI;

#my $data_source = "/Documents/Perl/leavebal.db";
my $dbh = DBI->connect('dbi:SQLite:dbname=leavebal.db', undef, undef, 
                {RaiseError =>1, AutoCommit =>0}) or die $DBI::errstr;

my $sth1 = $dbh->prepare('SELECT name FROM sqlite_master where type="t
+able"');
$sth1 -> execute();

my @data;
say "Current tables are:";
while (@data = $sth1->fetchrow_array())
    {
        print "\n";
        say @data;
    }
    
$sth1->finish;

$dbh->disconnect;
[download]

After running these in sequence (mod_schema.pl first, sel_all.pl second) I expect to see the same results (e.g. tables) from each. However, I don't get the same results: sel_all.pl returns no tables.

 
E:\strawberry-perl-5.18.2.1-64bit-portable>perl /Documents/Perl/mod_sc
+hema.pl
Current tables are:
Current tables are:

Users

LeaveBal

LeaveUse

E:\strawberry-perl-5.18.2.1-64bit-portable>perl /Documents/Perl/sel_al
+l.pl
Current tables are:
E:\strawberry-perl-5.18.2.1-64bit-portable>
[download]

Any insight as to why?

Comment on Perl or DBI error? Select or Download Code

Replies are listed 'Best First'.
Re: Perl or DBI error? by erix (Prior) on May 30, 2014 at 17:41 UTC
AutoCommit => 0, so if DDL `[1]` is transactional in SQLite (of which I'm not sure) then the CREATE TABLEs would need a COMMIT ($dbh->commit). (If not COMMITted the CREATEs would be ROLLBACKed. at disconnect()) `[1]`http://en.wikipedia.org/wiki/Data_Definition_Language	[reply] [d/l] [select]
Re^2: Perl or DBI error? by RedElk (Hermit) on May 30, 2014 at 18:23 UTC
D'oh! Thanks erix, that did the trick.	[reply]
Re: Perl or DBI error? by InfiniteSilence (Curate) on May 30, 2014 at 18:11 UTC
To add to what erix said, if you use the sqlite command line interface you would have seen that there were no tables created: `> sqlite leavebal.db SQLite version 2.8.17 Enter ".help" for instructions sqlite> .tables sqlite>` [download] Nothing. To verify: `sqlite> create table foo (alpha varchar(10) primary key); sqlite> .tables foo` [download] Celebrate Intellectual Diversity	[reply] [d/l] [select]
Re: Perl or DBI error? by locked_user sundialsvc4 (Abbot) on May 31, 2014 at 01:39 UTC
And (worth repeating here because it was for me so painfully discovered ...) when using SQLite, you should wrap everything in a transaction with explicit commits. If a transaction is not in progess, SQLite is designed not to use “lazy writes.” In fact it will physically verify every single write, drastically increasing the I/O activity and bringing everything to its knees. This is expressly by-design behavior but perhaps it is not intuitive. When transactions are used, the sucker flies. I would use them even for SELECTs, although I might be gun-shy on this point.
Re^2: Perl or DBI error? by RedElk (Hermit) on May 31, 2014 at 02:08 UTC
Ok, thanks for the tip. I'll keep that in mind.	[reply]