Re: MySQL <-> Oracle8

On Mon, Jun 05, 2000 at 11:31:35AM -0700, Kris Dahl wrote: > on 6/5/00 10:48 AM, Ryan Adams at radams@fcs.uga.edu wrote: > > > you've probably already thought of this but what about > > > > "select max(my_auto_increment_field) from mytable"? > > That only works if you haven't deleted a record and the auto-increment > hasn't filled the new vacancy. As I understand it (and testing supports this), the auto_increment attribute does not fill in vacancies, but rather adds 1 to the current maximum value of the auto_increment field. From the documentation: "An integer column may have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an AUTO_INCREMENT column, the column is set to value+1, where value is the largest value for the column currently in the table." In which case, after locking tables to prevent inserts between your insert query and your id query, the correct insert id would be returned by the max() function. i.e. mysql_query ("lock tables mytable write"); mysql_query ("insert into mytable (fields) values (values)"); $result = mysql_query ("select max(my_auto_increment_field) as insert_id from mytable"); mysql_query ("unlock tables mytable"); Any thoughts? Ryan