Re: Issue with simultaneous MySQL actions

I've changed it for the following now:

$dbh = DBI->connect("dbi:mysql:db","","") or die "Error db1";
$msubject = $fr{subject}; $msubject =~ s/\'/\\'/g;

$sth = $dbh->prepare("insert into tracker ( agent, subject, date, receiver, sent, etype ) values ( 'abc', '$msubject', '$todaydate', '$emailto', $sntcount, 'eCard' )");
$sth->execute;
$sth->finish;

$sth = $dbh->prepare("select max(id) from tracker where agent='abc'");
$sth->execute;
$newid = $sth->fetchrow_array;
$sth->finish;

$dbh->disconnect;

The 'id' is now auto incremental. However, I have to execute the max(id) query after writing the data cause I need to fetch the newest record. Do you think this will work better?

Thanks,
Ralph.

Comment on Re: Issue with simultaneous MySQL actions

Replies are listed 'Best First'.
Re: Re: Issue with simultaneous MySQL actions by LanceDeeply (Chaplain) on Jul 31, 2003 at 22:32 UTC
you should consider storing an agent's max id in another table. ( the following is MS-SQL compliant.. your flavor may require something slightly different ) you can quickly create this table like so: `select agent,max(id) into agent_id from tracker group by agent` [download] and then, before running your insert, run a transactioned query to get and set the max id of an agent `begin tran set nocount on update agent_id set ID = ID + 1 where Name = 'abc' select ID from agent_id where Name = 'abc' commit tran` [download] this should ensure you have no ID collissions HTH! Update: I just read the auto-incremental part of your post... which pretty much squashes this reply. shame on me.	[reply] [d/l] [select]
Re: Re: Issue with simultaneous MySQL actions by edoc (Chaplain) on Aug 01, 2003 at 00:28 UTC
..barely started my morning coffee, so it's possible I'm elsewhere still, but I think you want mysql_insert_id which will return the auto incremented id for your last insert. cheers, J	[reply]
Re: Re: Issue with simultaneous MySQL actions by Cine (Friar) on Aug 01, 2003 at 00:34 UTC
It's in the manpage :) mysql_insertid MySQL has the ability to choose unique key values automatically. If this happened, the new ID will be stored in this attribute. An alternative way for accessing this attribute is via $dbh->{'mysql_insertid'}. (Note we are using the $dbh in this case!) T I M T O W T D I	[reply] [d/l]