Return auto increment value in Oracle SQL Insert statement?

Question

I have Insert statement that should return Auto Increment column value. Here is example of my insert:

myQuery = new query();
myQuery.name = "insertRec";
myQuery.setDatasource("db");
mySQL = "
    INSERT INTO myTable (
        First, Last, Email, ActionDate 
    )VALUES(
        :first, :last, :email, :actiondt
    )
";

myQuery.setSQL(mySQL);
myQuery.addParam(name="first", cfsqltype="cf_sql_varchar", value="#trim(form.first)#", maxlength="50");
myQuery.addParam(name="last", cfsqltype="cf_sql_varchar", value="#trim(form.last)#", maxlength="50");
myQuery.addParam(name="email", cfsqltype="cf_sql_varchar", value="#trim(form.email)#", maxlength="320");
myQuery.addParam(name="actiondt", cfsqltype="cf_sql_timestamp", value="#now()#");
myQuery.execute().getResult();
//result = myQuery.execute().getResult(); This line is commented because I was getting error message that test variable is not defined. Not sure why since test is declared and equals to myQuery.execute().getResult();

After I run this code record will appear in the table. Result set looks like this:

RecordID  First  Last       Email        ActionDt
   7      John   Woss   jwoss@gmail.com  16-NOV-18

As you can see RecordID (auto increments) is there. I would like to get that value once myQuery is completed. How to achieve that?

create a local variable, number or integer, and change your insert to INSERT INTO myTable (...) values (...) RETURNING RecordID into local_variable; — thatjeffsmith, Nov 16 '18 at 15:46
Use `myQuery.getPrefix().generatedkey`. That should give you the inserted ID from your query. — Shawn, Nov 16 '18 at 20:12
Just a quick code review: Rather than passing `now()` as a parameter, just have the database do it. Remove the `addParam()` and replace `:actiondt` with `sysdate()`. The primary difference will be that `now()` will return the time from the CF server and `sysdate` will be the time of the db server. If db time is ok, then using `sysdate` will use much less resources and won't have a potential for blocking. — Shawn, Nov 16 '18 at 21:11

Sebastian Zartner · Answer 1 · 2018-11-19T07:51:06.467

As outlined in https://stackoverflow.com/a/34894454/432681, you can retrieve the auto-incremented ID via a SELECT on the related sequence.

So, your query should look something like this:

mySQL = "
    INSERT INTO myTable (
        First, Last, Email, ActionDate 
    )VALUES(
        :first, :last, :email, :actiondt
    );
    SELECT "sequenceForMyTable".currval from dual;
";

How to get the name of the sequence is also described in the linked answer above.

There's a second way outlined in https://stackoverflow.com/a/25268870/432681 and also mentioned in the CFML documentation, which says that you may use the getPrefix() function to be able to access the generated key. Note that in this case result is the object returned by the execute() method, not the getResult() (because the query doesn't return a result set). So for your example this is how you can get the ID:

result = myQuery.execute();
newID = result.getPrefix().generatedKey;

That would make another hit on the database. Not a full connection, but still another query. I believe the needed information would be returned to CF Server with the query object. — Shawn, Nov 16 '18 at 21:05
This is how it works in Oracle. The INSERT statement itself doesn't return the ID. Though I've found another solution using ColdFusion internal function, so I've added that to my answer — Sebastian Zartner, Nov 18 '18 at 11:10

Return auto increment value in Oracle SQL Insert statement?

1 Answers1