Return auto increment value in Oracle SQL Insert statement?

user_3023588 0
Viewed 1531

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?

coldfusion-10 coldfusion cfml oracle-sqldeveloper oracle
1 Answers

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;
user_4326810
Related