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

# create the data base handle
my $dbh = DBI->connect('DBI:RAM:', {RaiseError=>1});

# load the table in memory
 $dbh->func({
    table_name  => 'members',                 # table name
    col_names   => 'id, title, description',  # column names
    data_type   => 'PIPE',                    # pipe separated columns
    data_source => [<DATA>],                  # it could also be a file
 }, 'import' );

# 2 ways to get the new id
print "last id: ", get_last_id( $dbh), "\n";
print "first available id: ", get_first_available_id( $dbh), "\n";

# insert a new record
my $new_id= get_last_id( $dbh) + 1;
$dbh->do( qq[ INSERT INTO members (id, title, description) 
              VALUES ($new_id, 'New Title', 'New Description')]);

# output the result
$dbh->func( { data_source => 'SELECT * FROM members',
              data_type   => 'PIPE',
              data_target => 'toto.txt', 
            },
            'export');

# DBD::RAM does not support the MAX function so we have to do it ourselves
# just get the first id from the list of id's sorted in descending order
sub get_last_id
  { my( $dbh)= @_;
    return $dbh->selectcol_arrayref(qq[ SELECT id FROM members ORDER BY id DESC ])->[0];
  }


sub get_first_available_id
  { my( $dbh)= @_;
    # get the list of id's sorted by ascending order
    my $ids=  $dbh->selectcol_arrayref(qq[ SELECT id FROM members ORDER BY id ASC ]);
    # go through the list until there is a hole
    my $id= 1; 
    $id++ while( $id == shift @$ids);
    return $id;
  }

__DATA__
1|Title 1|Description 1
2|Title 2|Description 2
4|Title 3|Description 3