| Date: | Mon, 08 Sep 2003 15:56:15 -0400 |
| From: | Randy Latimer <randy_latimer [...] apollo.emtecinc.com> |
| To: | Tony Bowden <tony [...] tmtm.com> |
| Subject: | Re: Class::DBI communicating with a database over the network |
Hi.
I know you've seen many mails since I last wrote so am including a copy
of my original below.
I have further information which I'd like to share with you below the
included message.
Show quoted text
> Hi.
>
> Class::DBI v0.93 is absolutely the best thing since sliced bread to me.
> Thanks for such a great module.
>
> I've a question pertaining to communication between an app and a MySQL
> database on separate computers.
>
> When I use 'find_or_create' to lookup a name, for example:
>
> my $log = WebLog::Client->find_or_create(name => 'Sam');
>
> when the name is not present in a table, the following error is
> displayed:
>
> Can't insert new WebLog::Client: Can't get last insert id
>
> However, when I look at the table of data the name has appeared and
> there is an id.
>
> The second time I run the script with this line, it works fine and
> returns the id as expected.
>
> Also, if I run this script on the same machine which has the MySQL
> database (engine and data), it works fine the first time.
>
> It seems that the Class::DBI code is asking for this id faster than
> the DB engine can receive the insert transaction and can process the
> transaction which would assign the id in the DB table.
>
> Is there a necessity for a lag introduction in the Class::DBI code
> to give the DB engine time to assign the id? Is this something
> you are aware of? Is this something that can/will be fixed? Or
> am I just not doing something correctly?
>
> Thanks,
I've done some more experimentation and have solved my issue with
a kludge. I wanted to let you know what I did and see if you
concur with my conclusion.
Perusing the code for package Class::DBI v0.93, I noticed in function
'_auto_increment_value' that you ask for the 'mysql_insertid'
property value from the $dbh (assumed to be the database handle) in line
483. This property actually appears (according to the MySQL & mSQL
O'Reilly book) to be only accessible through a statement handle after
an INSERT is 'execute'd.
For my kludge, refer to the '_insert_row' function in package
Class::DBI. I simply replaced line 502 which looks like this:
$data->{ $primary_columns[0] } = $self->_auto_increment_value
with:
$data->{ $primary_columns[0] } = $sth->{mysql_insertid}
since the package has knowledge of a statement handle at that point.
This works for me since I only use MySQL at this time, although I'd
like to see how you would address this issue since you understand the
inner workings of this package.
The question this brings to mind for me is the following. Why does
the 'mysql_insertid' property value request made to the database
handle work on a local machine at all? And why does, what seems like
expected action, only occur when the network/client/server model is
actually in play?
Your thoughts?
Thanks,
--
Randy Latimer ______|___________________
Systems Engineer | | || || NS | |
EMTEC, Inc. | | [ ] ||[ ]|| [ ] | |
PH (770) 441-4999 x325 |__|_____||____||_____|__|
email: randy_latimer@emtecinc.com O<=>O O<=>O
------------------------------------------------------------------------
"A man's mind, stretched by new ideas, can never go back to its original
dimensions." Oliver Wendell Holmes