Next Autoindex

[Christian Giordano]

Hi guys, which query should I do to get it?


thx to all, chr

[Bill Karwin]

Re: Next Autoindex

Christian Giordano wrote:[color=blue]
> Hi guys, which query should I do to get it?[/color]

There's a SQL function LAST_INSERT_ID( ).

http://dev.mysql.com/doc/mysql/en/information-functions.html

There's also a MySQL API call, mysql_insert_id ().

http://dev.mysql.com/doc/mysql/en/mysql-insert-id.html

Regards,
Bill K.

[Christian Giordano]

Re: Next Autoindex

> There's a SQL function LAST_INSERT_ID( ).[color=blue]
> http://dev.mysql.com/doc/mysql/en/in...functions.html[/color]

I've already tried with this, but it returns 0 and I don't know how to
tell it in which table to get the number, so I used "SHOW TABLE STATUS"
and then I look for my data :S

thx, chr

[Bill Karwin]

Re: Next Autoindex

Christian Giordano wrote:[color=blue][color=green]
>> There's a SQL function LAST_INSERT_ID( ).
>> http://dev.mysql.com/doc/mysql/en/in...functions.html[/color]
>
> I've already tried with this, but it returns 0 and I don't know how to
> tell it in which table to get the number, so I used "SHOW TABLE STATUS"
> and then I look for my data :S[/color]

"LAST_INSERT_ID () ...
The last ID that was generated is maintained in the server on a
per-connection basis. This means the value the function returns to a
given client is the most recent AUTO_INCREMENT value generated by that
client."

If you connect to the database and query for LAST_INSERT_ID before you
do any inserts or updates that would cause an id to be generated, then
yes, it would be zero. The function only returns the last id generated
_during your current client session_.

I suspect that if you're trying to get the last insert id without having
inserted anything, you're not using it for its intended purpose. The
intended purpose is to make it easier to insert rows in dependent tables
that reference a row you inserted into your primary table:

INSERT INTO myMasterTable (val1, val2) VALUES (123, 456); /* autoinc
field is not named, thus gets a new incremented value */
INSERT INTO myDependentTabl e (fKey, val3, val4) VALUES
(LAST_INSERT_ID (), 789, 'abc');

This works because the last insert id is isolated to your current client
connection. Even if someone else is working on the database
concurrently and does their own insert to myMasterTable between the two
inserts you do, you still get the correct results.

But the purpose of LAST_INSERT_ID( ) is not for querying the greatest
value in an autoincrementin g column.

Regards,
Bill K.

[Jeff North]

Re: Next Autoindex

On Thu, 3 Mar 2005 09:48:30 +0000 (UTC), in mailing.databas e.mysql
Christian Giordano <christian@urmo mlikesspam.com> wrote:
[color=blue]
>| > There's a SQL function LAST_INSERT_ID( ).
>| > http://dev.mysql.com/doc/mysql/en/in...functions.html
>|
>| I've already tried with this, but it returns 0 and I don't know how to
>| tell it in which table to get the number, so I used "SHOW TABLE STATUS"
>| and then I look for my data :S
>|
>| thx, chr[/color]

Don't close the connection to the table before calling this function
i.e.

(JScript)
db = Server.CreateOb ject('ADODB.Com mand');
db.ActiveConnec tion = MM_CONNECTION_S TRING;
db.CommandText = "INSERT INTO myTable ........";
db.Execute();

//--- get id of last value inserted into database
db.CommandText = "SELECT LAST_INSERT_ID( )";
evdID = db.Execute().Fi elds(0).Value;

db.ActiveConnec tion.Close();

---------------------------------------------------------------
jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------