Clever Geek Handbook

Get newly inserted record id in CFScript

I have some code that inserts a log entry along with the request information. As soon as the request is submitted and the response is sent back, I update the record with the response information. Is there a way to get the ID of the recently inserted record so that I can refer to it and update it once I get a response? I know using CF tags you can use SET NO COUNT, but it doesn't work in CFScript. It seems like nothing is being returned in the INSERT statements.

        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 searched google and couldn't find a way to do this in CFScript. I don't want to query the table for the last row because it is not very reliable. Is there a way to get the newly inserted record ID after executing an INSERT query?

I am using mySQL. The weird thing is, when I dump the variable "result" above, CF complains that the result is undefined. When I check the table I can see that the script has executed and the record has been inserted.

+3


source to share


3 answers


It should be located under getPrefix().generatedkey 



genKey = result.getPrefix().generatedkey;
+12


source


Matt's answer is correct and the metadata is in the Prefix attribute. His example assumes the result variable is a return. result = query.execute();

not result = query.execute().getResult();

as in your code.



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

+5


source


Or alternately you can 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 it result.ID

should contain the generated id.

0


source






More articles:


All Articles