#!/usr/bin/perl -w
use strict;
use DBI;

# this is just silly code to show how one might encode the
# the columns with values we want to match on, of course you
# might want fancier things like ranges, etc so consider it
# just an example
# so the 1st pattern should match rows where 10 is the 
# 1st data column and 20 in the second

my @patterns = ([10,20,undef,undef],
		[undef,undef,'green',undef]);

my $dbname = 'testdb';
unlink($dbname); # this is for testing so make sure we start 
                 # with clean file every time
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbname","","",
		       );

my $table = $dbh->do(<<EOF
CREATE TABLE record (
record_id int(10) PRIMARY KEY,
datum1   int,
datum2   int,
datum3   varchar(64),
datum4   double(10,3)
                    );  
KEY i_d1 (datum1);
KEY i_d2 (datum2);
KEY i_d3 (datum3);
KEY i_d4 (datum4);
EOF
	      );
my $stmt = $dbh->prepare("INSERT INTO record VALUES (?,?,?,?,?)");
# data is from the stuff at the bottom, just for
# example so the script can be selfcontained
while(<DATA>){
    chomp;
    my ($id,@datum) = split(/,/,$_);
    $stmt->execute($id,@datum);
}


for my $pat ( @patterns ) {
    my ($i,$str,@vals) = (1);
    for my $p ( @$pat ) {
	if( defined $p ) {
	    $str .= ' AND ' if defined $str;
	    $str .= 'datum'.$i.'=?';
	    push @vals, $p;
	}
	$i++;
    }
    if( $str && @vals ) { # skip empty pattern rows
	my $query = $dbh->prepare("SELECT * FROM record WHERE $str");
	$query->execute(@vals);
	while( my $r = $query->fetchrow_arrayref ) {
	    print join(",",@$r),"\n"; 
	}
    }
    print "--\n"; # some sort of pattern search delimiter
}

# this is just embedding example data so this can be a 
# self contained example.
__DATA__
1,20,40,blue,800.0
2,10,20,yellow,612.23
3,1,17,green,9.89
4,77,50,red,5.102
5,8,33,orange,34.21
6,10,20,silver,998.8