title: Get newly inserted record ID in CFScript
body: <p>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.</p>
  
  <pre><code>        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;
  </code></pre>
  
  <p>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?</p>
  
  <p>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.</p>