Quickies, by Andrea Olivato

Perl and Sqlite3 quick start example guide

1. Set up your sqlite database file

sqlite3 demo.db
SQLite version 3.6.13
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
create table datas (
        id integer,
        name text,
        date text
);

2. Execute simple operations with perl

#!/usr/bin/perl

# Import needed Modules
use DBI;
use strict;

# Connection to DB file created before
my $dbh = DBI->connect("dbi:SQLite:dbname=demo.db","","",$dbargs);

# Two simple insert Queries 
# I use ->do as I don't need to do anything with returned obj
$dbh->do("insert into `datas` (id, name,date) values (1,'something','1986-06-07')");
$dbh->do("insert into `datas` (id, name,date) values (2,'otherthing','2009-03-02')");

# A simple select statement
my $query = "SELECT * FROM datas ORDER BY id DESC";
my $query_handle = $dbh->prepare($query);
$query_handle->execute();

# Instead of simply fetching rows I prefer, for each one,
# to assign values to predefinite values.
# Bind columns is a wonderful function!
$query_handle->bind_columns(\my($id, $name, $date));
while($query_handle->fetch()) {
   print "$id, $name, $date \n"
}

# Close connection
$query_handle->finish;
undef($dbh);