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

Robert Stockdale robert.stockdale at gmail.com
Tue Nov 24 22:39:32 GMT 2009


The transactional approach with the subquery is working just fine but if
anyone has thoughts on how I can get the auto-increment to work I would love
to hear them.

Thanks,
-Bob

On Sun, Nov 22, 2009 at 4:10 PM, Robert Stockdale <
robert.stockdale at gmail.com> wrote:

> 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 spe=
ed
>> 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
>>
>


-- =

Bob Stockdale
robert.stockdale at gmail.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20091124/f32=
98566/attachment.html


More information about the DBIx-Class mailing list