For each database engine we have different ways to retrieve the last inserted id, let’s say that we have the following table:
Product(productID, name, description)
And we have the following query:
<cfquery name=”test” datasource=”my_data”>
insert into Product (name, description) values (‘someProd’, ‘description O fThe Prod’)
</cfquery>
now for each engine we can get the last ID in the following way:
MSSQL: <cfoutput>#test.IDENTITYCOL#</cfoutput>
Oracle: <cfoutput>#test.ROWID#</cfoutput>
Sybase:<cfoutput>#test.SYB_IDENTITY#</cfoutput>
Informix:<cfoutput>#test.SERIAL_COL#</cfoutput>
MySQL: <cfoutput>#test.GENERATED_KEY#</cfoutput>
I guess that this is because each engine uses different methods and the internal database variables are different from engine to engine.