Posts Tagged ‘MySQL’

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.