Multiple Statements in a query

[paladin.rithe]

I have 2 statements that I'd like to string together in a query, but
according to everything I'm seeing, it's not possible.
What I'm doing is an insert of a row, where the primary key is an auto
increment. I want to retrieve that by using LAST_INSERT_ID(). I
tried sending another query after the first (once I knew that it the
data was what I need) but it didn't return anything. It seems that
that function can't be used in separate calls or something. Either
that or there's a setting that I need to check. Any ideas?

[Ivan Marsh]

On Fri, 02 Mar 2007 11:28:02 -0800, pa***********@gmail.com wrote:

I have 2 statements that I'd like to string together in a query, but
according to everything I'm seeing, it's not possible.
What I'm doing is an insert of a row, where the primary key is an auto
increment. I want to retrieve that by using LAST_INSERT_ID(). I
tried sending another query after the first (once I knew that it the
data was what I need) but it didn't return anything. It seems that
that function can't be used in separate calls or something. Either
that or there's a setting that I need to check. Any ideas?

If you're trying to return the last inserted row on a table with an auto
increment field all you have to do is query the maximum value in that
field.

[Michael Fesser]

..oO(Ivan Marsh)

>On Fri, 02 Mar 2007 11:28:02 -0800, pa***********@gmail.com wrote:

>I have 2 statements that I'd like to string together in a query, but
according to everything I'm seeing, it's not possible.
What I'm doing is an insert of a row, where the primary key is an auto
increment. I want to retrieve that by using LAST_INSERT_ID(). I
tried sending another query after the first (once I knew that it the
data was what I need) but it didn't return anything. It seems that
that function can't be used in separate calls or something. Either
that or there's a setting that I need to check. Any ideas?

Neither nor, there's an error in your second query. Please post some
code and the used query.

>If you're trying to return the last inserted row on a table with an auto
increment field all you have to do is query the maximum value in that
field.

Wrong! Never do it that way, never! Google for "race condition" and you
will know why. The correct way to do it is to call LAST_INSERT_ID(),
either natively in a query or through a higher API function like
mysql_insert_id().

Micha

[Jerry Stuckle]

Ivan Marsh wrote:

On Fri, 02 Mar 2007 11:28:02 -0800, pa***********@gmail.com wrote:

>I have 2 statements that I'd like to string together in a query, but
according to everything I'm seeing, it's not possible.
What I'm doing is an insert of a row, where the primary key is an auto
increment. I want to retrieve that by using LAST_INSERT_ID(). I
tried sending another query after the first (once I knew that it the
data was what I need) but it didn't return anything. It seems that
that function can't be used in separate calls or something. Either
that or there's a setting that I need to check. Any ideas?

If you're trying to return the last inserted row on a table with an auto
increment field all you have to do is query the maximum value in that
field.

And if you have two people insert at the same time one is going to get
the wrong value.

A terrible way to do it!

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Dikkie Dik]

I have 2 statements that I'd like to string together in a query, but

according to everything I'm seeing, it's not possible.
What I'm doing is an insert of a row, where the primary key is an auto
increment. I want to retrieve that by using LAST_INSERT_ID(). I
tried sending another query after the first (once I knew that it the
data was what I need) but it didn't return anything. It seems that
that function can't be used in separate calls or something. Either
that or there's a setting that I need to check. Any ideas?

It is already suggested that you post the queries themselves. But it
could be something else: If you issue the queries in separate
connections, they will not work together. A connection in MySQL (I
assume you are using MySQL) is like a session: variables only exist
within that session and the result of LAST_INSERT_ID is also not visible
to other connections.

So you do issue the queries in one connection, I hope?

Best regards

[Sanders Kaufman]

Jerry Stuckle wrote:

And if you have two people insert at the same time one is going to get
the wrong value.

A terrible way to do it!

That has changed now, hasn't it?
My understanding now is that it will return the last value
created *on that particular connection*.

p.s. I'm still working on that join tutorial you gave me. Thx.

[Jerry Stuckle]

Sanders Kaufman wrote:

Jerry Stuckle wrote:

>And if you have two people insert at the same time one is going to get
the wrong value.

A terrible way to do it!

That has changed now, hasn't it?
My understanding now is that it will return the last value created *on
that particular connection*.

p.s. I'm still working on that join tutorial you gave me. Thx.

No,

SELECT MAX(id) FROM mytable;

always returns the maximum value, no matter who inserted it.
You're thinking about mysql_last_insert_id(), which is connection specific.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[paladin.rithe]

On Mar 3, 1:07 pm, Dikkie Dik <[e-mail]:

I have 2 statements that I'd like to string together in a query, but
according to everything I'm seeing, it's not possible.
What I'm doing is an insert of a row, where the primary key is an auto
increment. I want to retrieve that by using LAST_INSERT_ID(). I
tried sending another query after the first (once I knew that it the
data was what I need) but it didn't return anything. It seems that
that function can't be used in separate calls or something. Either
that or there's a setting that I need to check. Any ideas?

It is already suggested that you post the queries themselves. But it
could be something else: If you issue the queries in separate
connections, they will not work together. A connection in MySQL (I
assume you are using MySQL) is like a session: variables only exist
within that session and the result of LAST_INSERT_ID is also not visible
to other connections.

So you do issue the queries in one connection, I hope?

Best regards

I actually solved it a different way. I was using a unique value to
create the table as it was, so I just used that instead.

I was using "SELECT LAST_INSERT_ID();" as my second query. The first
just being a general insert. I'm positive there was nothing wrong
with my queries because I put them in the console, and it worked
fine. I think there might be some issues with my db wrapper, but
that's a separate issue I think.