Multiple Statements in a query

[paladin.rithe@gmail.com]

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]

Re: Multiple Statements in a query

On Fri, 02 Mar 2007 11:28:02 -0800, paladin.rithe@g mail.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]

Re: Multiple Statements in a query

..oO(Ivan Marsh)

>On Fri, 02 Mar 2007 11:28:02 -0800, paladin.rithe@g mail.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]

Re: Multiple Statements in a query

Ivan Marsh wrote:

On Fri, 02 Mar 2007 11:28:02 -0800, paladin.rithe@g mail.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.
jstucklex@attgl obal.net
=============== ===

[Dikkie Dik]

Re: Multiple Statements in a query

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]

Re: Multiple Statements in a query

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]

Re: Multiple Statements in a query

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_inse rt_id(), which is connection specific.



--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[paladin.rithe@gmail.com]

Re: Multiple Statements in a query

On Mar 3, 1:07 pm, Dikkie Dik <nos...@nospam. orgwrote:

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.