Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Perl or DBI error?

by RedElk (Hermit)
on May 30, 2014 at 17:14 UTC ( #1088036=perlquestion: print w/ replies, xml ) Need Help??
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;

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?

Comment on Perl or DBI error?
Select or Download Code
Re: Perl or DBI error?
by erix (Vicar) 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

      D'oh!

      Thanks erix, that did the trick.

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>

    Nothing. To verify:

    sqlite> create table foo (alpha varchar(10) primary key); sqlite> .tables foo

    Celebrate Intellectual Diversity

Re: Perl or DBI error?
by sundialsvc4 (Monsignor) 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.

      Ok, thanks for the tip. I'll keep that in mind.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1088036]
Front-paged by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (5)
As of 2014-08-22 03:09 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (145 votes), past polls