use strict;
use Carp;
use Win32::ODBC;
my $db = new Win32::ODBC("SomeAccessDatabase");
my %IDcache;
my $MaxEntityID = GetMaxID("ENTITIES");

my @tests = (
	     [COUNTRY=>"UNITED_STATES"],
	     [PERSON=>"FRED"],
	     [PERSON=>"JOE"]
	    );

foreach my $test (@tests) {
  my $id = GetEntityID($test->[0],$test->[1]);
  if (!$id) {
    AddEntity($test->[0],$test->[1]);
  }
}

##</code><code>##

sub AddEntity{
  my($type,$name) = @_;
  $MaxEntityID++;
  my $sql =
  "INSERT INTO ENTITIES (ID,TYPE,NAME) VALUES ($MaxEntityID,\'$type\',\'$name\')";
  DoSQL($sql) or die "\n";
  $IDcache{uc("$type\:\:$name")}=$MaxEntityID;
  return $MaxEntityID;
}


sub GetEntityID{
  my ($type,$name)=@_;
  my $sql = "SELECT * FROM ENTITIES WHERE TYPE='$type' and NAME='$name'";
  my $id = $IDcache{uc("$type\:\:$name")} || 0;

  if ($id) {
    print "$type\:$name ID = $id (retrieved from cache)\n";
    return $id;
  }

  DoSQL($sql) or die "\n";
  if ($db->FetchRow) {
    $id = $db->Data("ID");
    $IDcache{uc("$type\:\:$name")} = $id;
    print "$type\:$name ID = $id\n";
    return $id;
  }
  else {
    print "$type\:$name is a new entity!\n";
    return 0;
  }
}


sub GetMaxID{
  my $type = shift;
  my $sql = "SELECT ID FROM [$type]";
  DoSQL($sql) or die "\n";
  my $maxid=0;
  while ($db->FetchRow) {
    my $id=$db->Data("ID");
    if ($id>$maxid) {
      $maxid=$id;
    }
  }
  print "Max $type ID: $maxid\n";
  return $maxid;
}

sub DoSQL{
  my $sql = shift;
  my $error = $db->Sql($sql);
  if ($error) {
    carp "SQL error $error in:\n\"$sql\"\n\n";
    return 0;
  }
  return 1;
}

##</code><code>##

Max ENTITIES ID: 516
COUNTRY:UNITED_STATES ID = 24
PERSON:FRED is a new entity!
SQL error 1 in:
"INSERT INTO ENTITIES (ID,TYPE,NAME) VALUES (517,'PERSON','FRED')"

        main::DoSQL('INSERT INTO ENTITIES (ID,TYPE,NAME) VALUES (517,\'PERSON\',
\'FRE...') called at C:\DBtest.pl line 26
        main::AddEntity('PERSON', 'FRED') called at C:\DBtest.pl line 16