[Catalyst] How to insert from form to MySQL with autoincrement primary key?

[Carl Franks]

On 09/12/2007, Martin Ellison <m.e at acm.org> wrote:
> Sorrry, this is another newbie question.
>
> I am using HTML::Widget and DBIx::Class to do simple CRUD on some MySQL
> tables. I have defined an auto-increment primary key on one of my tables,
> and I want to use an HTML form to populate and create a new object. I then
> want to retrieve the newly-generated key.
>
> Sup[pose the SQL has:
>
> CREATE TABLE mytable (
>         mytable_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
>        title VARCHAR(64) NOT NULL,
>         date VARCHAR(10) NOT NULL,
>        PRIMARY KEY (mytable_id);
>
> Having read the examples, I thought that all I had to do is:
>
>             my $obj =
> $c->model(q{MyAppDB::MyTable})->new({});
>              $obj->populate_from_widget($result);
>             my $newid = $obj->mytable_id;
>
> but I am finding that the generated SQL is in fact setting the key field to
> zero, and then the last line of the above code is getting an undefined value
> back.

I can't remember for sure how populate_from_widget() works, but I
suspect that you've a field in your form corresponding to the table's
'id' column. If that's empty, populate_from_formfu() may be trying to
blank that column (""), and mysql's converting it to a zero.
You can see that exact SQL that's being issued, by setting the
environment variable DBIC_TRACE=1
If this is new development, HTML::Widget is deprecated in favour of
HTML::FormFu.
If you decide to look at HTML::FormFu, take note that
DBIx::Class::HTML::FormFu is about to be replaced by
HTML::FormFu::Model::DBIC, which is still in svn, and will be released
to cpan at some point this month.

Cheers,
Carl