Re:mysql_insert_id and transactions

[Heikki Tuuri <Heikki.Tuuri@xxxxxxxxxxxxxxxx>]

Hi!

Ok, the problem the user had probably was calling the wrong Perl function.

From the MySQL manual:

"
insertid 
If you use the AUTO_INCREMENT feature of MySQL, the new auto-incremented
values will be stored here. Example: 
$new_id = $sth->{insertid};
As an alternative, you can use $dbh->{'mysql_insertid'}. 
"

Regards,

Heikki
http://www.innodb.com
--
Order commercial MySQL/InnoDB support at https://order.mysql.com/

At 01:12 PM 12/3/01 +0200, you wrote:
>Hi Jason and Heikki,
>
>Heikki Tuuri writes:
> > Jason,
> > 
> > SELECT LAST_INSERT_ID() seemed to work from the mysql client. That is a
> > workaround. I forward this message to Sinisa and Venu, so that they can
> > check the Perl interface. 
> > 
> > >From the manual page of mysql_insert_id() I found the following comment:
> > 
> > "
> > Charles Merriam: This may only work on most recent versions of MySQL; and
> > may not work even then. The work around is to do a SELECT LAST_INSERT_ID()
> > after your insert. I'm not sure about the performance penalty;
> > www.truegift.com is not performance driven. Good luck!
> > "
>
>I wasn't aware of such writing. Heikki, can you please forward the
>exact URL to me so that I can check this; I couldn't find the page you
>were referring to? Thanks.
>
>mysql_insertid in Perl should always work if it is used right after
>the insert statement. It is faster than using "SELECT LAST_INSERT_ID()", 
>because in the latter case an extra query is sent to the server. When
>one uses Perl's mysql_insertid, Perl can check the auto_increment
>value from the client-server protocol and doesn't have to do the extra
>query.
>
><cut>
> > >I'm tyring to do a transaction in mysql/innodb in perl with the following 
> > >basic flow
> > >eval{
> > >        Insert my first record
> > >        get the insert_id
> > >                (using $sth->{mysql_insert_id})
> > >        now perform other inserts that need that key
> > >        commit;};
> > >if($@){
> > >        rollback;        and other logic}
> > >the only problem is the {mysql_insert_id} doesn't give me anything back.  
> > >NADA...
> > >Is this a bug only in transactions or innodb? since I know the function is 
> > >working in autocommit / MyIsam tables.
> > >Do I have to do the select last_insert_id ? Because that IS working (and 
> > >consequently what I am doing now).using .43 on linux RH7.1  compiled from
> > source
> > >-- Jayce^
>
>Jason,
>
>can you please check that you are calling reference 'mysql_insertid'
>and NOT 'mysql_insert_id' ? Sometimes Perl may ignore the underscores,
>if it can't find the reference otherwise (that's what I've heard, but
>I'm not sure), but the real function call is without the latter
>underscore. For me it only works if I use '$dbh->{mysql_insertid}'
>
>If that wasn't the cause, we probably need to see an exact piece of
>your Perl code.
>
>Regards,
>
>- Jani
>
>For technical support contracts, visit https://order.mysql.com/
>   __  ___     ___ ____  __
>  /  |/  /_ __/ __/ __ \/ /    Mr. Jani Tolonen <jani@xxxxxxxxx>
> / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Full-Time Developer
>/_/  /_/\_, /___/\___\_\___/   Helsinki, Finland
>       <___/   www.mysql.com
>



---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <mysql-thread92911@xxxxxxxxxxxxxxx>
To unsubscribe, e-mail <mysql-unsubscribe-treed=ultraviolet.org@xxxxxxxxxxxxxxx>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php