Hello, I'm using the insertAndGetId function to re...

# exposed

[Francesco Di Sciascio]

Hello, I'm using the insertAndGetId function to recover the ID of an entity inserted into my table. The problem is that the table is defined inside an Oracle Schema and it uses a sequence to create the ID of the new entity. So, the insertAndGetId returns always the -1 value that I pass as parameter of the function, as follows:

val idRecordInserted = MyTable.insertAndGetId {
    it[id] = -1
    it[code] = myDataPassedInBodyOfRequest.code
...
}

Notice that I cannot remove the ID parameter because I will obtain an exception 'MyTableObject.id is not in record set', where 'MyTableObject' is the object of type IdTableLong defined to map the table as follows:

object MyTableObject: IdTable<Long>("my_table_name"){
    override val id = long("id").entityId()
    val code= varchar("code", 255)
...
}

Is there a way to automatically recover the ID of the inserted record, or do I need to read the sequence current value inside an exec() fun invocation?
Thanks all

[Luis Arcos]

I think it is because you are missing autoIncrement

[Luis Arcos]

override val id = long("ID").autoIncrement().entityId()

[Luis Arcos]

Then you can remove it[id] = -1

[Francesco Di Sciascio]

it does not work @Luis Arcos
but the main issue is about retrieving the ID after the insertAndGetId

[Luis Arcos]

I think you are just missing the value. Here is code we have:

internal fun insertAppointment(fields: AppointmentFields): AppointmentId {
    return AppointmentTable.insertAndGetId {
        it[advisorId] = fields.advisorId
        it[studentId] = fields.studentId
        it[start] = fields.appointmentTimeFrame.start
        it[end] = fields.appointmentTimeFrame.endInclusive
        it[institutionId] = fields.institutionId.value
        it[departmentId] = fields.departmentId.value
        it[appointmentTypeId] = fields.appointmentTypeId
        it[createdAt] = fields.createdAt
        it[createdBy] = fields.createdBy
        it[appointmentReason] = fields.appointmentReason
        it[location] = fields.meetingLocation.value
        it[meetingMode] = fields.meetingLocation.type
        it[appointmentRequestId] = fields.appointmentRequestId?.value
    }.value
}

[Francesco Di Sciascio]

this is from Exposed docs:

val id = StarWarsFilmsIntIdTable.insertAndGetId {
    it[sequelId] = MOVIE_SEQUEL_ID
    it[name] = "The Force Awakens"
    it[director] = "J.J. Abrams"
}

It seems that value could be retrieved in this way, like I am doing without success.
The point is that the ID is created by sequence in database, and not by Exposed.
So, the question is: is there a way to retrieve that value without querying the database to get the current value of the sequence?

[Luis Arcos]

we use sequences too and that's how we retrieve the value

[Francesco Di Sciascio]

where and how do you set the sequence name?
I'm using your code but I have an exception "Sequence name does not exists", it takes a name constructed using table name defined in IdTable definition, adding a suffix, like this:
my_table_name_id_seq
but I need to change it, like
seq_my_table_name

[Francesco Di Sciascio]

found!

override val id = long("id").autoIncrement("sequence_name_ob_database").entityId()

Thanks for your help @Luis Arcos, I appreciate!