Multiple statement handles, placeholders, and autoincrement

am 29.06.2002 01:50:07 von David Adam

--------------A678E3DC47F20BEACF1E3A29
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hi, I'm using perl 5.6.1, Mysql 3.23.49, and Redhat Linux 7.2,
converting a flat-field database of pathological design into a
relational version. I need to insert into several tables, over and
over, and would thus like to use multiple statement handles ($stha,
$sthb, etc.) so that I can create each statement with placeholders and
avoid some overhead.
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.
Sometimes it seems to work, but my program hangs with an uninitialized
value for my retrieved Sample ID sooner or later (usually pretty soon).
So I have a QUESTION:
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? 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?
What I appear to need is just the opposite of INSERT DELAYED -- some
sort of SELECT DELAYED that waits for the previous insert to complete
before executing.
Any insights will be much appreciated!

--
David Adam
Quaternary Geologist
Lake County, California

--------------A678E3DC47F20BEACF1E3A29--

Re: Multiple statement handles, placeholders, and autoincrement

am 29.06.2002 02:23:10 von Jeremy Zawodny

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. :-)

Jeremy
--
Jeremy D. Zawodny,
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936

MySQL 3.23.51: up 30 days, processed 651,798,999 queries (251/sec. avg)

------------------------------------------------------------ ---------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail msql-mysql-modules-thread1838@lists.mysql.com

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail msql-mysql-modules-unsubscribe@lists.mysql.com instead.

Re: Multiple statement handles, placeholders, and autoincrement

am 29.06.2002 02:23:10 von Jeremy Zawodny

Re: Multiple statement handles, placeholders, and autoincrement

am 29.06.2002 03:58:07 von David Adam

--------------ED5DCE215CECCB54C62E58A1
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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! ]

--------------ED5DCE215CECCB54C62E58A1--

Re: Multiple statement handles, placeholders, and autoincrement

am 29.06.2002 03:58:07 von David Adam

Re: Multiple statement handles, placeholders, and autoincrement

am 29.06.2002 09:50:49 von Jeremy Zawodny

On Fri, Jun 28, 2002 at 06:58:07PM -0700, David Adam wrote:
>
> 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.

That's correct. It's more efficient to use prepared statements. DBI
does less work that way.

> 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?

Not at all.

> That was why I was using multiple connections.

You can use a single connection for many prepared queries.

my $statement1 = $dbh->prepare(...);
my $statement2 = $dbh->prepare(...);

while (...)
{
$statement1->execute(...);
$statement2->execute(...);
}

That will work just fine.

Jeremy
--
Jeremy D. Zawodny,
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936

MySQL 3.23.51: up 30 days, processed 656,836,113 queries (250/sec. avg)

------------------------------------------------------------ ---------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail msql-mysql-modules-thread1840@lists.mysql.com

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail msql-mysql-modules-unsubscribe@lists.mysql.com instead.

Re: Multiple statement handles, placeholders, and autoincrement

am 29.06.2002 09:50:49 von Jeremy Zawodny