0

I've seen similar questions asked here many times but none pertaining to ColdFusion. Based on some of the answers, this is the code I'm trying to run:

<cfquery name="getTest" datasource="POW" result="result">
    INSERT INTO myStuff (Stuff)
    VALUES ('My test 3')
</cfquery>
<cfdump var="#result#" >

and this is what I get:

struct

CACHED  false

EXECUTIONTIME   47

RECORDCOUNT 1

SQL INSERT INTO myStuff (Stuff) VALUES ('My test 3')

If I switch to result = result.GeneratedKey, I get a new column headed Generated Key but with no values inside.

I'd appreciate any help anyone can give.

SOS
  • 6,430
  • 2
  • 11
  • 29
Ross from Brooklin
  • 293
  • 1
  • 5
  • 18

1 Answers1

1

Unfortunately you've run into one of the downsides of using an unsupported desktop database like MS Access. It's going to lack some of the more advanced features. In part, due to limitations of MS Access itself, but also due to Adobe dropping support it. A better path going forward is upgrading to a database that is more robust and designed for web app concurrency, like SQL Server. If that's not an option for now you're stuck with less ideal solutions...

Don't use SELECT MAX(ID)

As you already guessed, one thing that's not an option is using select max(ID). It's not thread safe so you're NOT guaranteed to get back the ID of the record you just inserted. Bottom line, don't use it.

Possible Option

Before the CF 8 introduced the result attribute, a typical approach was to append use a dbms specific function to return the newly generated ID after the insert. For example, with SQL Server:

 <cfquery name="qInsert" ....>
    INSERT INTO SomeTable (....)
    VALUES (...)
    SELECT SCOPE_IDENTITY() AS GeneratedIDValue
 </cfquery>

IIRC Access has something similar: @@IDENTITY. AFAIK, it's specific to the current database connection so it should be thread safe. Unfortunately Access doesn't support executing multiple statements in the same cfquery. At least not the last time I checked. So you'll probably need to use two cfquery's. You must wrap the two queries in a transaction to ensure they both use the same database connection/session:

 <cftransaction>
    <cfquery ....>
       INSERT INTO SomeTable (....)
       VALUES (...)
       SELECT SCOPE_IDENTITY() AS GeneratedIDValue
    </cfquery>
    <cfquery name="qInsert" ....>
        SELECT @@IDENTITY AS GeneratedIDValue
    </cfquery>
 </cftransaction>
 

 <cfoutput>
 Generated ID: #qInsert.GeneratedIDValue#
 </cfoutput>
SOS
  • 6,430
  • 2
  • 11
  • 29