[Dbix-class] can't get last insert id on create() with mysql

[Robert Stockdale]

Hi Peter,

Several things strike me as wrong:
>
> *) Why is the primary key a composite, if the above subquery guarantees
> that
> the single column 'id' will always be unique?
>

I should have been a little more clear. The ID field is not always set using
the subquery. In certain cases a value is assigned directly. So a slightly
broader view of the code looks like this:

if ( $some_condition ) {
    $fields->{id} =3D $id
}
else {
   # code to set other fields here...
    $fields->{id} =3D \'(SELECT FIELD1 FROM (SELECT MAX(FIELD1) + 1 FIELD1
FROM TABLE1) TEMP_T)';
}

$c->model('DB::Table1')->create($fields);

Based on the code above you can now tell that the ID column is not unique by
itself.

*) Why not simply use autoincrementing columns, but instead incur the speed
> penalty of a subquery on every insert?
>

I tried this: The ID column is being incremented as expected by msql, but
the call to create() still blows up with:

    "DBIx::Class::ResultSet::create(): Can't get last insert id at ..."

I would really prefer to go this route. Any idea why this wouldn't be
working?


> *) Bonus - if you have a field 'id' and a multicolumn PK, you need to
> redefine the accessor for this particular column. Otherwise you're
> overloading the internal 'id' method[1].
>

Thanks for the tip, I didn't know this. Do you know where I can find more
info on doing this?


> If you're hellbent on using the subquery - you need to roughly do this:
>

I would really prefer to use the auto_increment approach but it wasn't
working so I figured the subquery was the next best approach because I
needed to get away from two completely separate queries to get/set the ID.
So yea, if I can get the auto_increment to work that would be ideal. Could
the id accessor issue be the underlying problem?

Thanks,
-Bob


> The reason it doesn't work the way you did it:
> 1) DBIC executes the SQL you expect when creating the row
> 2) DBIC then runs through the columns of the just created object,
> and makes sure all PK columns are defined non-ref values
> 3) It finds something odd and calls $storage->_dbh_last_insert_id,
> since a full PK condition is necessary to do any further work with
> this object (i.e. when you update/delete you need all the values to
> make sure you're working with the correct row)
> 4) Of course the storage driver does not return anything, as the
> DBD driver did not use an autoincrement, thus has no idea what the
> value of the subquery was
> 5) DBIC dies instead of leaving you with a broken row object
>
> Cheers
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20091122/780=
55e5f/attachment.htm