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

I have a Perl Script that processes a form input and adds the result to a mySQL database. The problem I'm having is that I would like to autoincrement the primary key. I can't seem to get this to cooperate. Can anyone provide any insight? At this point, this only works once, and set's the variable idnum (which is the primary key) to 1. If I delete the entry, then add again from the form, it will create a new entry with idnum = 1, but if there is anything in the database under idnum = 1, it errors out. I would love to have a database of more than one entry. Any help would be greatly appreciated.

Thanks, Digitus

Replies are listed 'Best First'.
Re: Perl/DBI autoincrement
by antirice (Priest) on Jul 18, 2003 at 05:23 UTC

    Try this within mysql or fire up DBI and do the following:

    alter table TABLE_IN_QUESTION modify FIELD_NAME int unsigned not null 
+auto_increment;

    [download]

    When inserting, just don't list that field. i.e.

    # our table
CREATE TABLE example (
id int unsigned not null primary key auto_increment,
num int,
str varchar(50)
);

# our insert statement
insert into example (num,str) values(5,"hello");

# test and see what we get
select * from example;

+----+------+-------+
| id | num  | str   |
+----+------+-------+
|  1 |    5 | hello |
+----+------+-------+
1 row in set (0.00 sec)

    [download]

    If you need to get the value the primary key field was set to during insertion, you can call select last_insert_id() as a lone statement and it will return the value.

    Hope this helps.

    antirice    
    The first rule of Perl club is - use Perl
    The     ith rule of Perl club is - follow rule i - 1 for i > 1

[reply]
[d/l]
[select]
Re: Perl/DBI autoincrement
by BUU (Prior) on Jul 18, 2003 at 04:57 UTC
    When you create the table you should select 'auto-increment' as an attribute of a column. I have no idea how you do this manually, I just use premade tools. Regardless, once you have a column set up to auto-increment, you simply insert new records and leave the auto-incrementing field blank.
[reply]
Re: Perl/DBI autoincrement
by abarilla (Initiate) on Jul 18, 2003 at 12:47 UTC
    Also, if you're using DBI you can get the id of the last inserted record by calling: 
       $new_id = $sth->{mysql_insertid};

    [download]
    Keep in mind that retrieving the auto increment id needs to be the first thing you do after inserting the record. Also either exclude the primary key from the insert statement or pass NULL as its value.

    Here's the MySQL documentation page: http://www.mysql.com/doc/en/Perl_DBI_Class.html

[reply]
[d/l]
      You guys are beautiful. I used a mix of just altering the table to make the primary key increment, and the fetch statement offered here. We're up and running. Thanks for the help!!

      Digitus
[reply]
Re: Perl/DBI autoincrement
by Anonymous Monk on Jul 18, 2003 at 15:48 UTC

    Just in case anybody super-searches this node, the Oracle equivalent here is:

    INSERT INTO ATABLE (
        FIELD1
        )
SELECT  SEQ_NAME.NEXTVAL;

    [download]

    Then to retrieve the sequence value just entered:

    SELECT  SEQ_NAME.CURRVAL
FROM    DUAL

    [download]
[reply]
[d/l]
[select]
      Alternatively, 
      INSERT INTO <TABLE> ( foo, bar)
VALUES ( sequence_name.nextval,  'value_for_bar');

      [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom