Gossamer Forum
Home : General : Perl Programming :

MySQL's last_insert_id and mod_perl .. How does it work??

Quote Reply
MySQL's last_insert_id and mod_perl .. How does it work??
Hi all,

I am using mod_perl and MySQL w/DBI and am concerned if I enter a record into a table and then go back to get the auto_increment field with a last_insert_id() DBI command that I may get the actual last_insert_id of a client which may be hitting my script at the same time. DBI keeps the connection open when used with mod_perl so if i understand the mysql last_insert_id command, I may get someone else's last_insert_id back. Does anybody know for sure how this works? The MySQL documentation doesn't go into this.

Last edited by:

xev: Nov 29, 2001, 8:03 PM
Quote Reply
Re: [xev] MySQL's last_insert_id and mod_perl .. How does it work?? In reply to
Hi,

I use this command to get last insert id. I think it work fine in mod_perl too.

$sth->{insert_id}

In the newer DBI, should use this command

$sth->{mysql_insert_id}

Cheers,


Cheers,

Dat

Programming and creating plugins and templates
Blog
Quote Reply
Re: [xev] MySQL's last_insert_id and mod_perl .. How does it work?? In reply to
Hi,

As long as you get it from the same $sth, it's going to be thread safe (i.e. doesn't matter if another process inserts before you call insert_id, you still get the one you inserted).

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] MySQL's last_insert_id and mod_perl .. How does it work?? In reply to
Thanks.

The thing I'm confused about is where do you get $sth? I do my insert statements with $dbh ($dbh->do), which is what the connection is tied to.

I just read that insert_id is a statement handle attribute and mysql_insertid is a database handle attribute. Do I have to use it as a statement handle attribute?

mod_perl seems to make things so much more complicated.
Quote Reply
Re: [xev] MySQL's last_insert_id and mod_perl .. How does it work?? In reply to
Hi,

You should use statement handle instead.

Cheers,


Cheers,

Dat

Programming and creating plugins and templates
Blog
Post deleted by Wil In reply to
Quote Reply
Re: [xev] MySQL's last_insert_id and mod_perl .. How does it work?? In reply to
Hm. I can't seem to get $res_id = $sth->mysql_insert_id(); to work. Must be an old version of DBI or something. Here's the error:

Can't locate object method "mysql_insert_id" via package "DBI::st" at $file, $line.


- wil

Last edited by:

Wil: Nov 30, 2001, 3:29 AM
Quote Reply
Re: [Wil] MySQL's last_insert_id and mod_perl .. How does it work?? In reply to
Probably because it isn't a method Crazy

It is only supported with the newest versions of mysql

http://mysql.widexs.nl/...mysql_insert_id.html

Looking inside DBI.pm tells you most of the things you can do:

@driver_names = DBI->available_drivers;
@data_sources = DBI->data_sources($driver_name);

$dbh = DBI->connect($data_source, $username, $auth, \%attr);

$rv = $dbh->do($statement);
$rv = $dbh->do($statement, \%attr);
$rv = $dbh->do($statement, \%attr, @bind_values);

$ary_ref = $dbh->selectall_arrayref($statement);
@row_ary = $dbh->selectrow_array($statement);
$ary_ref = $dbh->selectcol_arrayref($statement);

$sth = $dbh->prepare($statement);
$sth = $dbh->prepare_cached($statement);

$rv = $sth->bind_param($p_num, $bind_value);
$rv = $sth->bind_param($p_num, $bind_value, $bind_type);
$rv = $sth->bind_param($p_num, $bind_value, \%attr);

$rv = $sth->execute;
$rv = $sth->execute(@bind_values);

$rc = $sth->bind_col($col_num, \$col_variable);
$rc = $sth->bind_columns(@list_of_refs_to_vars_to_bind);

@row_ary = $sth->fetchrow_array;
$ary_ref = $sth->fetchrow_arrayref;
$hash_ref = $sth->fetchrow_hashref;

$ary_ref = $sth->fetchall_arrayref;

$rv = $sth->rows;

$rc = $dbh->commit;
$rc = $dbh->rollback;

$sql = $dbh->quote($string);

$rc = $h->err;
$str = $h->errstr;
$rv = $h->state;

$rc = $dbh->disconnect;

Last edited by:

PaulW: Nov 30, 2001, 4:31 AM
Quote Reply
Re: [PaulW] MySQL's last_insert_id and mod_perl .. How does it work?? In reply to
Yes. Looking at the documentation lists all things you can do which are common to all database drivers.

It does not specificaly list other nuggets that are reserved for specific drivers.

Therefore, I'm going to have a look in my 'Programming the Perl DBI' for the answer.

- wil
Quote Reply
Re: [Wil] MySQL's last_insert_id and mod_perl .. How does it work?? In reply to
Guess so. Shouldn't you have looked there first ?Tongue

Last edited by:

PaulW: Nov 30, 2001, 4:37 AM
Quote Reply
Re: [PaulW] MySQL's last_insert_id and mod_perl .. How does it work?? In reply to
Yes. Except my books are at home and I'm in work :-\.

- wil
Quote Reply
Re: [Wil] MySQL's last_insert_id and mod_perl .. How does it work?? In reply to
Hi,

It's an attribute of the statement handle, not a method. If you are making this portable you should use:

my $last_insert_id = $sth->{mysql_insertid} || $sth->{insertid};

As older DBD::mysql's used insertid, but newer ones will throw a warning if you use that.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] MySQL's last_insert_id and mod_perl .. How does it work?? In reply to
I thought it was, thanks. I got an error because I was calling insert_id instead of insertid.

Cheers

- wil