#!/usr/bin/env perl

# SQLite in Perl - DBD and DBI - create, insert, select, drop

# Example from first principles

use DBI;

# Next statement is ONLY one to change for other databases
# (you will need to load the right DBD and perhaps give host, user
# name and password data too)

$db_handle = DBI -> connect('DBI:SQLite:demodatabase.sqlite');

print "\nDemo ... run with \"populate\" parameter to create and load a database\n";
print " run without a parameter to show database content\n";
print " run with \"clear\" parameter to delete a database\n\n";

##########################################################

if ($ARGV[0] =~ /^pop/i){

print "Creating table\n";
%demodata = ( "Swansea" => "Paddington",
        "Weymouth" => "Waterloo",
        "Brighton" => "Victoria",
        "Hastings" => "Charing_Cross",
        "Margate" => "Victoria");

$db_handle -> do("CREATE TABLE arrivals (afar CHAR(20), near CHAR(20))");

foreach (keys %demodata){
   $db_handle -> do("INSERT INTO arrivals VALUES( \"$_\", \"$demodata{$_}\" )");
}

#########################################################

} elsif ($ARGV[0] =~ /^cle/i) {

print "Dropping table\n";
$db_handle -> do ("DROP TABLE arrivals");

#########################################################

} else {

print "Displaying table content\n";
$getkey = $db_handle -> prepare("SELECT * FROM arrivals");
$getkey -> execute;
while (@row = $getkey->fetchrow) {
        print "@row\n";
        }

}

##########################################################

print "Program Completed\n";

__END__

Example of running that:

munchkin:pl grahamellis$ perl D1.pl populate

Demo ... run with "populate" parameter to create and load a database
         run without a parameter to show database content
         run with "clear" parameter to delete a database

Creating table
Program Completed
munchkin:pl grahamellis$ perl D1.pl

Demo ... run with "populate" parameter to create and load a database
         run without a parameter to show database content
         run with "clear" parameter to delete a database

Displaying table content
Swansea Paddington
Margate Victoria
Hastings Charing_Cross
Brighton Victoria
Weymouth Waterloo
Program Completed
munchkin:pl grahamellis$ perl D1.pl clear

Demo ... run with "populate" parameter to create and load a database
         run without a parameter to show database content
         run with "clear" parameter to delete a database

Dropping table
Program Completed
munchkin:pl grahamellis$ perl D1.pl

Demo ... run with "populate" parameter to create and load a database
         run without a parameter to show database content
         run with "clear" parameter to delete a database

Displaying table content
DBD::SQLite::db prepare failed: no such table: arrivals at D1.pl line 50.
Can't call method "execute" on an undefined value at D1.pl line 51.
munchkin:pl grahamellis$