Get newly inserted record ID in CFScript

Question

I have some code that inserts a record into the log along with the request information. Once the request is sent and a response is sent back I update the record with the response info. Is there a way to get the ID of the newly inserted record so I can reference that and update it once I receive the response? I know using CF tags you can use SET NO COUNT but it doesn't seem to work in CFScript. Seems like nothing is returned in an INSERT statement.

        query = new query();
        query.name = "queryResult";
        query.setDataSource('dsn');
        sql = "

        INSERT INTO paymentlogs (
            type, name, address1, address2, country, provstate, city, pocode, cclastfour, expirymonth, expiryyear, cardtype, requestxml, ipaddress, clid, subscriptionid, total, createdat, createdby, updatedat, updatedby
        )
        VALUES
        (
            :type, :name, :address1, :address2, :country, :provstate, :city, :pocode, :cclastfour, :expirymonth, :expiryyear, :cardtype, :requestxml, :ipaddress, :clid, :subscriptionid, :total, :now, :username, :now, :username
        )

        ";
        query.setSQL(sql);

        query.addParam(name="clid", cfsqltype="cf_sql_varchar", value="#formValues.clid#");
        query.addParam(name="type", cfsqltype="cf_sql_varchar", value="#arguments.type#");
        query.addParam(name="name", cfsqltype="cf_sql_varchar", value="#formValues.ccname#");
        query.addParam(name="address1", cfsqltype="cf_sql_varchar", value="#formValues.ccaddress1#");
        query.addParam(name="address2", cfsqltype="cf_sql_varchar", value="#formValues.ccaddress2#");
        query.addParam(name="country", cfsqltype="cf_sql_varchar", value="#formValues.cccountry#");
        query.addParam(name="provstate", cfsqltype="cf_sql_varchar", value="#formValues.ccprovstate#");
        query.addParam(name="city", cfsqltype="cf_sql_varchar", value="#formValues.cccity#");
        query.addParam(name="pocode", cfsqltype="cf_sql_varchar", value="#formValues.ccpocode#");
        query.addParam(name="cclastfour", cfsqltype="cf_sql_varchar", value="#Right(formValues.ccnumber, 4)#");
        query.addParam(name="expirymonth", cfsqltype="cf_sql_varchar", value="#formValues.ccexpirymonth#");
        query.addParam(name="expiryyear", cfsqltype="cf_sql_varchar", value="#formValues.ccexpiryyear#");
        query.addParam(name="cardtype", cfsqltype="cf_sql_varchar", value="#getCardType(formValues.cctype)#");
        query.addParam(name="requestxml", cfsqltype="cf_sql_varchar", value="#soapBody#");
        query.addParam(name="ipaddress", cfsqltype="cf_sql_varchar", value="#CGI.REMOTE_ADDR#");
        query.addParam(name="subscriptionid", cfsqltype="cf_sql_varchar", value="#formValues.subscriptionid#");
        query.addParam(name="total", cfsqltype="cf_sql_float", value="#formValues.grandTotalAmount#");
        query.addParam(name="username", cfsqltype="cf_sql_varchar", value="#formValues.username#");
        query.addParam(name="now", cfsqltype="cf_sql_timestamp", value="#now()#");
        result = query.execute().getResult();
        writedump(result);abort;

I've searched Google and couldn't find any way to do it in CFScript. I don't want to have to query the table for the last row because that is not very reliable. Is there a way to get the newly inserted record ID after executing the INSERT query?

I'm using mySQL. The strange thing is when I dump out the "result" variable above, CF complains that result is undefined. When I check the table, I can see that the script did execute and the record was inserted.

*RE: using CF tags you can use SET NO COUNT but it doesn't seem to work in CFScript* `SET NOCOUNT` is a SQL Server specific command. [There is no MySQL equivalent](http://stackoverflow.com/questions/3386217/is-there-an-equivalent-to-sql-servers-set-nocount-in-mysql). That is why it does not work for you. Having said that, usually `SET NOCOUNT` is only needed for inserts when the query is doing more than just a single `INSERT`. — Leigh, Aug 12 '14 at 20:05

score 13 · Accepted Answer · answered Aug 12 '14 at 15:56

13

It should be located under getPrefix().generatedkey

genKey = result.getPrefix().generatedkey;

answered Aug 12 '14 at 15:56

Matt Busche

14,216
5
36
61

I thought I could do something like that but when I dump out the result variable like in my code sample, I get an error saying that result is undefined. So I can't access anything in the result. The query does, however, execute correctly and I can see the inserted row in the table. – Guest Aug 12 '14 at 16:03
Matt is correct. The methods are just poorly named. IMO they should align with the cfquery tag attributes. They don't. `getResult()` is equates to `` and `getPrefix()` to ``. – Leigh Aug 12 '14 at 20:45

score 6 · Answer 2 · answered Aug 12 '14 at 18:15

6

Matt's answer is correct and the meta data is located in the Prefix attribute. His example assumes that the result variable is the return of. result = query.execute(); not result = query.execute().getResult(); like in your code.

From the query cfc documentation: "Prefix: Equivalent to the result attribute for the cfquery tag."

answered Aug 12 '14 at 18:15

Twillen

1,458
15
22

my vote put your reputation over 100. Congratulations, and you owe me a beer. – Dan Bracuk Aug 12 '14 at 18:42

score 1 · Answer 3 · edited Aug 13 '14 at 00:49

1

Or alternately you could use the Scope_Identity() MS SQL Server function:

sql = "

    INSERT INTO paymentlogs (
        type, name, address1, address2, country, provstate, city, pocode, cclastfour, expirymonth, expiryyear, cardtype, requestxml, ipaddress, clid, subscriptionid, total, createdat, createdby, updatedat, updatedby
    )
    VALUES
    (
        :type, :name, :address1, :address2, :country, :provstate, :city, :pocode, :cclastfour, :expirymonth, :expiryyear, :cardtype, :requestxml, :ipaddress, :clid, :subscriptionid, :total, :now, :username, :now, :username
    )

    select Scope_Identity() as [ID]

    ";

Then result.ID should contain the generated ID.

edited Aug 13 '14 at 00:49

Leigh

28,765
10
55
103

answered Aug 12 '14 at 16:11

Jedihomer Townend

400
1
9

There is nothing in the question that specifies the RDBMS. – Dan Bracuk Aug 12 '14 at 16:12
Sorry, forgot to mention I'm using mySQL. But the strange thing is I can't even dump the result because CF complains it is undefined. – Guest Aug 12 '14 at 16:19
1

Sorry, I assumed MS SQL. With regards to result not existing, it probably wont be defined as there is no return from the query. Usually to perform an update or insert when nothing is returned you can just result = query.execute(); Try dumping that. – Jedihomer Townend Aug 12 '14 at 16:27
Thanks Jedihomer, that worked. The reason why I wasn't getting anything in my result variable was because I set it to result = query.execute().getResult(); instead of just result = query.execute(). That's why I coudln't use the getPrefix() function. Please post another answer so I can mark it as the answer. Thanks! – Guest Aug 12 '14 at 16:42
Also see: http://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide – James A Mohler Aug 12 '14 at 23:24

Get newly inserted record ID in CFScript

3 Answers3

Linked