4 messages in com.mysql.lists.perlRe: Multiple statement handles, place...| From | Sent On | Attachments |
|---|---|---|
| David Adam | 28 Jun 2002 16:49 | |
| Jeremy Zawodny | 28 Jun 2002 17:22 | |
| David Adam | 28 Jun 2002 18:57 | |
| Jeremy Zawodny | 29 Jun 2002 00:50 |
| Subject: | Re: Multiple statement handles, placeholders, and autoincrement |
|---|---|
| From: | David Adam (dav...@quercus.org) |
| Date: | 06/28/2002 06:57:45 PM |
| List: | com.mysql.lists.perl |
Jeremy Zawodny wrote:
On Fri, Jun 28, 2002 at 04:50:07PM -0700, David Adam wrote:
One of the tasks involves inserting an autoincrement Sample ID field into one table, and then getting the assigned Sample ID back immediately so I can use it in a subsequent insert into another table. I couldn't get LAST_INSERT_ID to work for me (probably because I was asking for the answer on a different statement handle than the one used to insert), so I tried a query asking for the sample ID I had just inserted, using a secondary key.
Actually, the last insert id is per-thread (or connection). So you should be able to either do this:
$insert_id = $dbh->{'mysql_insertid'}
or run a SELECT query like:
SELECT LAST_INSERT_ID();
and check the result.
Can a short query #2 on connection B (the retrieval) get ahead of a longer query #2 on connection A (the autoincrement insertion), so that I can't get the autoincremented value back reliably?
Why use 2 connections? If you use a single connection (aka, database handle) the problem shouldn't be there
If so, is there a tidy way around the problem, or do I have to resort to using one statement handle, and then recreating each query each time I need it?
You could use prepared statements, as described in the DBI documentation. :-)
Well, that is where I am confused. My take on the DBI docs
was that the big advantage of preparing statements is that you can do it
once and then use them over and over. But if you have to use a different query
to get back the autoincrement value, don't you then have to prepare the first
query all over again before you can use it again? That was why I was
using multiple connections.
[ And thank you for the speedy reply! ]