Result from insert

[riluve]

Sorry, this may be rudamentry, but I am not sure quite how to pull it off.

I want to insert a record and read the index value created in a single query.

It is not sufficient to insert a record with one query then read the last record with another query as a different user running the same application has a chance of inserting another record before the 2nd query (to find info on the latest record) has a chance to run.

Is it at all clear what I need to do and the concerns I have?

 

[Kozusnik]

From the mysql.org documentation...

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

You can generate sequences without calling LAST_INSERT_ID(), but the utility of using the function this way is that the ID value is maintained in the server as the last automatically generated value. It is multi-user safe because multiple clients can issue the UPDATE statement and get their own sequence value with the SELECT statement (or mysql_insert_id()), without affecting or being affected by other clients that generate their own sequence values.

Mark

 

[riluve]

Hummm - well thnx for your reply Kozusnik - at first it seems like this may be viable solution (though I haven't actually tried it yet) in fact it may not be sufficient.

The problem I see is that individual users will not be connecting with their own clients. Rather, users will be logged in to a ClodFusion server which will be (or might be) a single client to MySQL.

Thus, user collision may still be possible when using more than one query.

I will ask around the ColdFusion forum though to ensure my assumption is correct (that ColdFusion will maintain only a single connection for all users to MySQL).

Anyone know either the technical details involved OR a way to find an automatically genereted index without doing a 2nd query?

 

[Kozusnik]

I use mysql_insert_id(), and it's a PHP app with only one user id accessing the database. It's webbased, so there are multiple users.

It's worth a glance to you.

Good luck,
Mark