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

In reply to Re: Sql problem in Win32::ODBC by monktim
in thread 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.