Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

Working with DBI and DBD::SQLite2

by TStanley (Canon)
on Oct 29, 2004 at 20:08 UTC ( #403892=perlquestion: print w/ replies, xml ) Need Help??
TStanley has asked for the wisdom of the Perl Monks concerning the following question:

I wanted a simple database to track the dates that our servers were last rebooted, so I decided to use the DBD::SQLite2 module. Well, I am having a problem at the moment, and need some advice.This is running on a Windows NT 4 workstation (ActiveState Perl 5.8.4 Build 810)
The code:
#!perl -w use strict; use DBI; my $D=DBI->connect("DBI:SQLite2:dbname=Store.db","",""); $D->do("CREATE TABLE ISP(Store INTEGER,Date CHAR[9])"); $D->do("CREATE TABLE UnityPrimary(Store INTEGER,Date CHAR[9])"); $D->do("CREATE TABLE UnitySecondary(Store INTEGER,Date CHAR[9])"); open(CSV,"reboots.csv")||die"Can't open: $!\n"; while(<CSV>){ chomp; my($store,$isp,$pri,$sec)=split /,/; my $sta=$dbh->prepare("INSERT INTO ISP(Store,Date) VALUES($store,$is +p)"); my $stb=$dbh->prepare("INSERT INTO UnityPrimary(Store,Date) VALUES($ +store,$pri)"); my $stc=$dbh->prepare("INSERT INTO UnitySecondary(Store,Date) VALUES +($store,$sec)"); $sta->execute; $stb->execute; $stc->execute; } close CSV; my $sth=$dbh->prepare("SELECT * FROM UnityPrimary WHERE Store=1"); $s->execute; while(my@row=$s->fetchrow_array){ print "@row\n"; } $D->disconnect;
The data file:
1,18-Oct-04,29-Oct-04,29-Oct-04 2,18-Oct-04,26-Oct-04,26-Oct-04 3,21-Oct-04,26-Oct-04,11-Aug-04 4,20-Oct-04,11-Jun-04,27-Oct-04 5,18-Oct-04,3-Sep-04,24-May-04 6,17-Oct-04,15-Oct-04,15-Oct-04

The error messages:
DBD::SQLite2: :st execute failed: no such column: Oct(1) at dbdimp.c l +ine 412 at C:\WINNT\Profiles\toms\Desktop\createdb.pl line 26, <CSV> +line 1. DBD::SQLite2: :st execute failed: no such column: Oct(1) at dbdimp.c l +ine 412 at C:\WINNT\Profiles\toms\Desktop\createdb.pl line 27, <CSV> +line 1. DBD::SQLite2: :st execute failed: no such column: Oct(1) at dbdimp.c l +ine 412 at C:\WINNT\Profiles\toms\Desktop\createdb.pl line 28, <CSV> +line 1.
The above three error messages repeat themselves for each line in the .csv file. What exactly is causing this?

TStanley
--------
The only thing necessary for the triumph of evil is for good men to do nothing -- Edmund Burke

Comment on Working with DBI and DBD::SQLite2
Select or Download Code
Re: Working with DBI and DBD::SQLite2
by mifflin (Curate) on Oct 29, 2004 at 20:26 UTC

    Are you sure your tables are getting created?

    I don't see you using RaiseError or testing for errors after your do or prepare statements

Re: Working with DBI and DBD::SQLite2
by gmax (Abbot) on Oct 29, 2004 at 20:31 UTC

    You have a quoting problem. Use placeholders!

    my $sta=$dbh->prepare("INSERT INTO ISP(Store,Date) VALUES(?, ?)"); my $stb=$dbh->prepare("INSERT INTO UnityPrimary(Store,Date) VALUES(? +, ?)"); my $stc=$dbh->prepare("INSERT INTO UnitySecondary(Store,Date) VALUES +(? , ?)"); $sta->execute($store,$isp); $stb->execute($store,$pri); $stc->execute($store,$sec);

    BTW, some error checking would not be out of order either.

     _  _ _  _  
    (_|| | |(_|><
     _|   
    
      as supplement: this is definitely the problem, use always placeholders! some values have to be quoted so the db knows where the value ends. also you you can speed up the inserts by reuseing your statements and performing them all in one transaction.
      $dbh->do('BEGIN TRANSACTION'); my $sta=$dbh->prepare("INSERT INTO ISP(Store,Date) VALUES(?, ?)"); my $stb=$dbh->prepare("INSERT INTO UnityPrimary(Store,Date) VALUES(?, +?)"); my $stc=$dbh->prepare("INSERT INTO UnitySecondary(Store,Date) VALUES(? + , ?)"); while (<CSV>){ chomp; my($store,$isp,$pri,$sec)=split /,/; $sta->execute($store,$isp); $stb->execute($store,$pri); $stc->execute($store,$sec); } $dbh->('END TRANSACTION');
      oh yes, and definements like INTEGER or CHAR[9] mean nothing to SQLite2. the only column define which is usefull is PRIMARY KEY INTEGER for self generating unique keys.

      update: just for your information, DBD::SQLite $VERSION >= 1 is actualy SQLite 3, DBD::SQLite2 was made to update the SQLite 2 path. just in case you want to benefit from the new features in v3 but didn't know how weird the modules are named.
Re: Working with DBI and DBD::SQLite2
by jZed (Prior) on Oct 29, 2004 at 20:53 UTC
    Is CHAR[9] valid in a SQLite CREATE statement? The more normal way is CHAR(9). I second and third the recommendations to turn RaiseError on and to use placeholders. Not that it matters in this case, but are you aware that DBD::SQLite2 is (counter-intuitively) *older* than DBD::SQLite? DBD::SQLite2 is really only for backwards compatibility with the now superceded SQLite version 2.x while DBD::SQLite is for the new SQLite version 3.x.
Re: Working with DBI and DBD::SQLite2
by JamesNC (Chaplain) on Oct 30, 2004 at 15:01 UTC
    1. $dbh is undefined, I think you need to either change $D to $dbh or vice versa.
    2. $s is undefined, I think you mean $sth

    Here is your stuff working and some modifications I made to show you some oddities ( that SQLite docs claim are not bugs but features...) about SQLite that I noticed right away. I don't like this rdbms because it really doesn't care what stuff you feed it. I defined an integer, but it doesn't complain when I send it something else. And you don't get an exception raised when you try to create a table with a bogus data type, which makes debuging difficult.

    I would also note that attributes like NULL and NOT NULL which are required when creating tables in most all rdbms's is ignored by SQLite. Also, a DATE datatype which would benefit from right away is absent in SQLite ( you should convert all your dates to a linear value if you want to use this tool and compare inside of SQLite? )
    I personally don't think I will ever use this tool. There are too many free fully featured rdbms's out there.
    JamesNC
    #!perl -w use strict; use DBI; my $drop_tables = 1; my $create_tables = 1; my $dbh=DBI->connect("dbi:SQLite2:dbname=Store.db","","", {RaiseError= +> 1}) or die "$!"; if( $drop_tables == 1 ){ #NO SUPPORT for IF EXISTS $dbh->do("DROP TABLE ISP"); $dbh->do("DROP TABLE UnityPrimary"); $dbh->do("DROP TABLE UnitySecondary"); } if( $create_tables == 1){ $dbh->do("CREATE TABLE ISP(Store INTEGER,Date CHAR[1] NOT NULL )") or +warn "$!"; #Define my own $dbh->do("CREATE TABLE UnityPrimary(Store INTEGER,Date CHAR[9] NOT NUL +L)"); $dbh->do("CREATE TABLE UnitySecondary(Store INTEGER ,Date CHAR[9] NOT +NULL )"); } #open(CSV,"reboots.csv")||die"Can't open: $!\n"; while(<DATA>){ chomp; my($store,$isp,$pri,$sec)=split /,/; my $sta=$dbh->prepare("INSERT INTO ISP(Store,Date) VALUES(?,?)"); my $stb=$dbh->prepare("INSERT INTO UnityPrimary(Store,Date) VALUES(? +,?)"); my $stc=$dbh->prepare("INSERT INTO UnitySecondary(Store,Date) VALUES +(?,?)"); $sta->execute( $store, $isp ); $stb->execute( $store, $pri ); $stc->execute( $store, $sec ); } #close CSV; my $sth=$dbh->prepare("SELECT * FROM UnityPrimary WHERE Store > 1"); $sth->execute; while(my@row=$sth->fetchrow_array){ print "@row\n"; } $dbh->disconnect; __DATA__ 1,18-Oct-04,29-Oct-04,29-Oct-04 notanInt,18-Oct-04,26-Oct-04,26-Oct-04 3,21-Oct-04,26-Oct-04,11-Aug-04 4,20-Oct-04,11-Jun-04,27-Oct-04 5,18-Oct-04,3-Sep-04,24-May-04 6,17-Oct-04,15-Oct-04,15-Oct-04
    OUTPUT: notanInt 26-Oct-04 3 26-Oct-04 4 11-Jun-04 5 3-Sep-04 6 15-Oct-04
      I cut and pasted from my test script. Looks like I forgot to rename a few variables. :)

      TStanley
      --------
      The only thing necessary for the triumph of evil is for good men to do nothing -- Edmund Burke
Re: Working with DBI and DBD::SQLite2
by TStanley (Canon) on Nov 01, 2004 at 17:45 UTC
    I rewrote the code, using DBD::SQLite and some error checking. It is now working fine. My thanks to all who helped.

    TStanley
    --------
    The only thing necessary for the triumph of evil is for good men to do nothing -- Edmund Burke

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (3)
As of 2014-09-20 03:43 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (152 votes), past polls