Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
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 rifling through the Monastery: (6)
As of 2014-09-02 01:07 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite cookbook is:










    Results (18 votes), past polls