DevOps, CI/CD and Automation

ok, here goes...

Have an application that will require Oracle connectivity, it already has an abstracted data layer and currently works under MSSQL, Access, MySQL, etc..

The problem is as follows: The centralized code we use to add a record to any of the above mentionned DB platforms follows the same code base;

- Retrieve an ADO recordset containing the schema (columns) of the table you want to insert to, ensuring that it is blank ; for example: (this has been dumbed down to a ADO recordset for the sake of clairity and my own testing)

Set poCmpsRS = mo_GetRS("select id, CMPTNC_GRP_ID from cmptncs where id = -1")

then typically, an Addnew is called to create the new record:

poCmpsRS.AddNew

poCmpsRS.Fields("CMPTNC_GRP_ID").Value = 1

poCmpsRS.UpdateBatch

Now, on ALL OTHER DB platforms, the contents of .fields("ID") will contain the auto_increment value from access, mysql, mssql etc... but in Oracles case, as soon as updatebatch is called, the contents of .fields("ID") show "0" (zero), even though the contents of the database effectively show the triggered sequence value for the column.

This boils down to 2 questions:

Is there any way to get the last value issued by a sequence via an SQL call?

OR

Is there any way to get ADO to behave properly with Oracle with respect to retrieving the sequence value?

I'd HATE to have to modify our code to pre-fetch sequence values prior to issuing an Addnew to the DB, call it laziness; I don't wanna go there, MANY MANY tables.