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;
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;
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>
Any insight as to why?