Sql problem in Win32::ODBC

fletcher_the_dog has asked for the wisdom of the Perl Monks concerning the following question:

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]

Comment on Sql problem in Win32::ODBC Select or Download Code

Replies are listed 'Best First'.
Re: Sql problem in Win32::ODBC by monktim (Friar) on Aug 07, 2003 at 16:31 UTC
Check the ID column and see if it is defined as an integer column. It may be a char, varchar, etc. Then you'll need the quotes as the above poster mentioned. You may want to make the ID column your identity column. You can give this column that attribute which will automatically create a unique number and insert it for you. The column may actually already have this attribute and passing an ID value will cause your error. If the ID column has the identity attribute then the uniqueness is already defined and managed for you. But if you don't want ID to be an identity and still want to insure uniqueness you may want to create a unique constraint on the ID column in the dB. A unique constraint will protect the table from being updated by other client processes that don't check to see if the ID already exists before doing an INSERT, thus corrupting your table. You can also formulate your sql code to use EXISTS. Doing this you only send one message to the dB. This reduces network traffic and database server processing. It also simplifies your perl code a lot. `IF NOT EXISTS (SELECT * FROM ENTITIES WHERE ID = $MaxEntityId) INSERT INTO ENTITIES (ID,TYPE,NAME) VALUES ($MaxEntityID, '$type', '$name')` [download] But if the ID column is an identity column then all you have to do is this (assuming type and name together also uniquely identify a row (they're an alternate or candidate key)): `IF NOT EXISTS (SELECT * FROM ENTITIES WHERE TYPE = $type AND NAME = $name) INSERT INTO ENTITIES (TYPE, NAME) VALUES ('$type', '$name')` [download] Making the ID column have the identity property you can reduce your perl code to this: ################ NOTE: UNTESTED CODE use strict; use warnings; use Win32::ODBC; my $dB = new Win32::ODBC("DSN=name;UID=user;PWD=password;"); die 'Cannot connect to dB.' if !$dB; my %tests = ( COUNTRY => 'UNITED_STATES', PERSON => 'FRED', PERSON => 'JOE' ); my ($type, $name); while (($type, $name) = each %tests) { my $query = qq( IF NOT EXISTS ( SELECT * FROM ENTITIES WHERE TYPE = '$type' AND NAME = '$name' ) INSERT INTO ENTITIES (TYPE, NAME) VALUES ('$type', '$name') ); my $rc = $dB->Sql($query); die "Query failed: $query ".Win32::ODBC::Error() if $rc; } [download] Another problem can be that your script doesn't have permission to modify the table. Set the permission on the ENTITIES table to public and try your script again. If it works then you can decide what the correct table permissions should be. Good luck	[reply] [d/l] [select]
Re: Re: Sql problem in Win32::ODBC by fletcher_the_dog (Friar) on Aug 07, 2003 at 18:05 UTC
Well, it turns out it was a permissions problem. It seems to work fine now. Thanks!	[reply]
Re: Re: Re: Sql problem in Win32::ODBC by monktim (Friar) on Aug 07, 2003 at 18:33 UTC
Great! Database object permissions are highly overlooked problems when used with Perl. OBDC's error messages are painted with a pretty broad stroke so they're not easy to detect. If you print the sql code out from the script then cut and paste it to sql and it runs you can be sure the problem isn't in Perl. Chances are it is permissions on the dB object, the ODBC data source is set up incorrectly or you are connected to the wrong database. The problem can also be that the sql server is disconnected. ODBC does give you a good error message when the connection doesn't exist.	[reply]
Re: Sql problem in Win32::ODBC by dragonchild (Archbishop) on Aug 07, 2003 at 16:03 UTC
Try quoting the id. It sounds stupid, but I'm not impressed with Access, so I treat it as if it's really really dumb. Usually, that ends up working. :-) ------ We are the carpenters and bricklayers of the Information Age. The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6 Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.	[reply]
Re: Sql problem in Win32::ODBC by fletcher_the_dog (Friar) on Aug 07, 2003 at 16:15 UTC
Unfortunately, I have already tried putting the quotes around the id and adding/subtracting backslashes before the quotes. Are there any know bugs with Win32::ODBC about how it handles sql statements?	[reply]
Re: Sql problem in Win32::ODBC by vivapl (Acolyte) on Aug 07, 2003 at 16:07 UTC
I personally don't see a problem with the statement itself, however you may try to remove the backslashes you have just before each single quote. As far as I know you do not need them there. Could be part of the problem though don't quote me on this. I also noticed that this is the only statement in your code that does have backslashes Hope this helps	[reply]