Hi,
I am using Win32::ODBC to access a MS Access database. In it I have a table of "ENTITIES" or things that have a "TYPE" and a "ID". The "ID" is a unique non-zero number for each entity. The original table was imported from some csv files and now I am working on a script that checks to see if an entity already exists in the table and if it doesn't, the script adds the entity in and gives it a unique id. I am able access the table just fine, but when I try to insert new stuff into the table I always get an sql error. If I go into Access and use the exact same sql statement it works fine. Has anyone else had problems like this? How do I get around it?
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]);
}
}
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;
}
OUTPUT:
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