following => preceding of course... happens when you cut-paste late in the evening.

---

Umbraco Core Team Developer | Working on vNext | LinkedIn

Yes that's true, and the AUTO_INCREMENT attribute is exactly what I used for the table declaration. Further, there's the mysql_insert_id() function which then could replace @@IDENTITY, but it doesn't seem to work the same way. (Or does it?)

Someone gave me a slightly better idea:

"Let's have the logic backwards. You want to perform the insert first, and THEN get the MAX(id) which will provide the ID of the just inserted row."

Might be a little less tricky. Or is there any better?

---

Umbraco Core Team Developer | Working on vNext | LinkedIn

There is also a parameter called LAST_INSERT_ID() that seems to be the same thing as @@IDENTITY.



/Thomas

---

Web Developer at Kärnhuset - http://www.karnhuset.net - Stockholm, Sweden

Yes, I believe there could (in theory) be differences.
I have never worked in MySQL, but this definition of LAST_INSERT_ID() caugth my attention:

For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client.

and:
Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed.

If I interpret this correctly, it is possible for another user (using another connection) to go in and add a new record and your execution of LAST_INSERT_ID() wouldn't return the ID of that user's recently added record but rather the ID of the last record that YOU (your connection) added to the database. Do you all agree?

A SELECT MAX(ID) on the other hand would return the highest ID-number regardless of which connection is used to query the database.

This means you should use LAST_INSERT_ID() if you want to get the ID of the record that you just inserted. If you want the ID of the record that anyone just inserted you should use SELECT MAX(ID).

But I don't know if any of this really matters since you are only going to use the database together with the webserver (IIS) and I think that the webserver is considered a single user and using a single connection to the database(?)

---

Web Developer at Kärnhuset - http://www.karnhuset.net - Stockholm, Sweden

I wouldn't lock SqlHelper globally, at least not frequently. This is going to be an issue with crouded sites where DB updates will be made through the front end. I'm not familiar with the source enough to point at a specific issue, but from my experience with DBs this is not the best way to approach this. I think you should consider the approach for each case, preventing the lock as much as possible. So when you only need to find the ID of the record you inserted, just call LAST_INSERT_ID(). For other cases when you need the next ID value available use SELECT MAX(ID), but I don't see why this is needed since this field has AUTO_INCREMENT set?

Itamar.