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]);
  }
}

[download]
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;
}

[download]
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

[download]

In reply to Sql problem in Win32::ODBC by fletcher_the_dog

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.