Get table ID after insert with ColdFusion and MySQL

Question

My normal process for inserting into one table and then getting the ID back so that I can insert into another table is like this in MSSQL:

DECLARE @transactionKey uniqueidentifier
SET @transactionKey = NEWID()

INSERT INTO transactions(transactionKey, transactionDate, transactionAmount)
    VALUES(@transactionKey, '#transactionDate#', '#transactionAmount#')

DECLARE @transactionID int
SELECT @transactionID = transactionID
    FROM transactions
    WHERE transactionKey = @transactionKey


INSERT INTO transactionItems(transactionID, itemID, itemAmount)
    VALUES(@transactionID, '#itemID#', '#itemAmount#')


SELECT @transactionID as transactionID

My question is 2 parts. First, is this the best way to do this? I read that there is a chance that the GUID changes on me and I end up with a invalid GUID in the second table. I am assuming the chances of that are very slim and I have been doing this for years on various projects and have never had a problem.

The second part of my question is does something like this work in MySQL? I am starting to work on a new project using MySQL and I am not exactly sure the best way to do this. I have normally only worked on MSSQL in the past.

I am using CF9 and MySQL on this new project.

Any help on this would be great.

Thanks in advance.

Solution 1

Part 1: I would personally not batch multiple statements within a single query to reduce the risk of SQL injection. This is a setting within your datasource on the ColdFusion administrator. Executing a stored procedure, which might be what you are doing(?), is another story, but, you should rephrase your question to "Get primary key after insert with mySQL Stored Procedure" if that is your intention.

Part 2: ColdFusion, like many things, makes getting the primary key for a newly inserted record very easy--even if you are using auto-increment keys, GUIDs or something like Oracle's ROWNUM. This will work on any almost every database supported by Adobe ColdFusion including MSSQL or MySQL. The only exception is the version of the databse--for example, MySQL 3 will not support this; however, MySQL 4+ will.

<cfquery result="result">
  INSERT INTO myTable (
      title
  ) VALUES (
    <cfqueryparam value="Nice feature!" cfsqltype="cf_sql_varchar">
  )
</cfquery>

<--- get the primary key of the inserted record --->
<cfset NewPrimaryKey = result.generatedkey>

As of CF9+, you can access the new ID (for any database) using the generic key name:

result.GENERATEDKEY    // All databases

For CF8, different databases will have different keys within the results value. Here is a simple table to help I copied from the cfquery documentation.

result.identitycol    // MSSQL
result.rowid          // Oracle
result.sys_identity   // Sybase
result.serial_col     // Informix
result.generated_key  // MySQL

If you have any questions you can see a pretty dump as follows:

<cfdump var="#result#" />

Solution 2

Here is a quick solution for MSSQL. It uses the SCOPE_IDENTITY() function that returns the ID of the last row inserted in the previous insert statement.

http://msdn.microsoft.com/en-us/library/ms190315.aspx

<cfquery>
    DECLARE @iNewGeneratedID INT

    INSERT INTO transactions
        (
            transactionDate,
            transactionAmount
        )
    VALUES
       (
            <cfqueryparam value="#transactionDate#" type="cf_sql_date">,
            <cfqueryparam value="#transactionAmount#" type="cf_sql_integer">
       )

    SET @iNewGeneratedID = SCOPE_IDENTITY()

    INSERT INTO transactionItems
        (
            transactionID,
            itemID,
            itemAmount
        )
    VALUES
        (
            @iNewGeneratedID,
           <cfqueryparam value="#itemID#" type="cf_sql_integer">,
           <cfqueryparam value="#itemAmount#" type="cf_sql_integer">
        )

    SELECT @iNewGeneratedID AS iNewGeneratedID
</cfquery>

Solution 3

As per the answer of @aaron-greenlee, The result variable of INSERT queries contains a key-value pair that is the automatically generated ID of the inserted row; this is available only for databases that support this feature.

And the below is possible way to return the inserted record for all databases.

<cfquery result="result">
   INSERT INTO myTable (
      title
   )
   OUTPUT INSERTED.*
   VALUES (
   <cfqueryparam value="Nice feature!" cfsqltype="cf_sql_varchar">
   )
</cfquery>

You will get the inserted record details in result.

Hope this helps. Thanks.

Comments

[Geek20]

+1 for the "result" attribute, using cfqueryparam, and splitting up the queries into more granular calls.

[Sequenzia]

@aaron Thanks! I don't know why I I have been doing it the other way for so long. This is a lot easier.

[Sequenzia]

thanks! I like this way also better than the other way I was doing it. This will be good for stored procedures.

[Yisroel]

I dont know why, but i've never been able to get result.generatedKey to work for me. using result.generated_key works fine.

[Aaron Greenlee]

Depending on the database you are using, the primary key is named differences. Guess the driver authors were 'almost' on the same convention.

[jfrobishow]

Something to look out for, I'm not sure if it applies only to MSSQL or not but the value is NOT part of result if you have any t-sql comments in the query (e.g. --this is a comment.)

[Shashi Penumarthy]

For MSSQL it's result.identitycol (with a T) not identifycol (with an F). SO won't let me edit and fix just one character.

[user1431633]

For MSSQL it's not identifycol... it's identitycol