Working with DBI and DBD::SQLite2

[TStanley]

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?

[gmax]

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.

[Taulmarill]

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.

[jZed]

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.

[JamesNC]

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

[TStanley]

I cut and pasted from my test script. Looks like I forgot to rename a few variables. :)

[mifflin]

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

[TStanley]

I rewrote the code, using DBD::SQLite and some error checking. It is now working fine. My thanks to all who helped.