GetLastAutoID function added to aps_inc

[Den_CarpeTerra]

I had need for this function and thought others might need it as well. Perhaps add it to the NWNX distribution?

[dguntner]

What exactly is an AutoID that you're getting the last one of?

--Dave

[Den_CarpeTerra]

When you set up a table, sometimes you want to have an index field which is set to auto-increment. Then when you add a record, you might need to grab this index field back and do something with it. That's what this function does, determine the last index number assigned.

There are other, more direct, ways of going about this depending on what version of mySQL you are running, but this works 100% of the time.

[NoMercy]

I don't fully understand it, what would you give in sField, the field which is the auto-incremented number?

[Manuel]

Yeah, this script is a bit confusing. The sField couldn't be the autoincrement field since it would have been populated. Where is the NULL value being queried for? A better approach might be to do a:

[Den_CarpeTerra]

The sField is the auto-increment field, so if your table looks like this:

CREATE TABLE `my_table` (
`serialID` int(10) unsigned NOT NULL auto_ '0',
`name` varchar(32) NOT NULL default '',
`item` blob NOT NULL,
PRIMARY KEY (`serialID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Then you would use in your script:

int serialID = GetLastAutoID("my_table", "serialID");

>The sField couldn't be the autoincrement field since it would have been populated.

Your suggestion to use MAX, while it also works, requires a lot more database work than is needed. And just because you don't understand it doesn't mean it won't work.

[Den_CarpeTerra]

I should also say that I tried to use the last_insert_id() to return the auto-increment id as it is assigned, but couldn't get it to work with NWNX. This would be the best and most direct way to get this information, but failing that, the solution I posted above works.

[Kosmous]

you lost me there with this SQL statement:

string SQL = "SELECT "+sField+" FROM "+sTable+" WHERE "+sField+" IS NULL";

why would using the where statement: "WHERE Fieldname IS NULL" retrieve the last inserted auto-incremented row? Ive had your problem and i know why last_insert_id doesnt work (since it only works if you DONT end your current session with mysql between inserting the last row and querying its ID). However, i dont quite understand how this query obtains the last inserted id.

What ive used is pretty simple (dunno if its efficient) and works pretty well: "Select " +sField+ " From " +sTable+ " ORDER BY " +sField+ " DESC LIMIT 1"

probably not the best way to do it but its pretty easy to see how this works. It simply orders the rows by sField in reverse (last to first) then limits the number of rows taken to 1. Since mysql always adds + 1 to the last/highest id (even if the last id was previously deleted) even if there are skipped index numbers when using auto-increment, this should always work perfectly (unless another id was inserted between ur last insert statement and the select query).

[JeroenB]

If you have a field in a table that is auto-increasing, lets name it id, then I would use the following query to fetch the latest id:

[Manuel]

I'd have to test your SQL statement myself but even if it works, I wouldn't use it. The reason why I say it's confusing (not that it wouldn't work) is because I DO understand it. My understanding is that if you do an INSERT, the auto-increment field gets (wait for it) ... incremented. Therefore a value should exist in that column. The schema definition itself calls for NOT NULL on that column. Doing a SELECT on that column WHERE the value IS NULL should return no results. If this isn't the case, then I'd consider it a bug.

Doing a SELECT MAX will not cause much overhead in the case where the ID field is an INDEX or KEY as you have wisely done.
_________________
I only know enough to be dangerous.

[JeroenB]

An auto-increment field is never empty, as it must be created as a key item (not null). The RDBMS will always insert an unique value in the field, only in special cases an id will be reused (when it is not used in the db anymore, but this is rarely the case.

http://dev.mysql.com/doc/mysql/en/example-auto-increment.html

[Grinning Fool]

For mysql: select LAST_INSERT_ID()

[Gryphyn]

Sorry to rain on everyones parade, but all these ONLY work if you can guarantee the order that each row iss inserted into the table, and the order of each check for the 'LastID'. Since each NWNX call is it's own transaction, this is not possible.

It's possible that, say, two/three (or more) inserts occur prior to NWNX processing the 'Last ID' retrieval.

The only way to make sure you get the ID assigned to your insert is to have a secondary unique field (with an Index for performance)

CREATE TABLE my_table (
serialID int(10) unsigned NOT NULL auto_increment,
name varchar(32) NOT NULL default '',
data <text|blob> default NULL,
PRIMARY KEY (serialID)
UNIQUE (name)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO my_table SET name = '<value>';
SELECT SerialID FROM my_table WHERE name = '<value>';

This ensures that you get the SerialID related to `<value>`, not just the last row entered.

As to actually getting the LastID Kosmous had the right of it
SELECT SerialID FROM my_table ORDER BY SerialID DESC LIMIT 1;
which also lets you find the NEXT SerialID to be added...
SELECT SerialID+1 FROM my_table ORDER BY SerialID DESC LIMIT 1;

Cheers
Gryphyn

[Ed.] Redirected back here. (so an update)
Things have changed a little with NWNX4.
You can now execute multiple sql statements. This means that
SELECT LAST_INSERT_ID() can be part of the same transaction and WILL return the value that you expect if it's part of the same SQLExecDirect() statement as the insert (eg "INSERT stuff; SELECT LAST_INSERT_ID()")