I'm new to ktor and I'm trying to insert a row in a mysql table with Ktor Exposed. The table is defined as below:
object Posts : Table("wpv1_posts") {
val id: Column<Int> = integer("ID").uniqueIndex()
val author: Column<Int> = integer("post_author")
val title = varchar("post_title", 50)
val date: Column<DateTime> = datetime("post_date")
val dateGMT: Column<DateTime> = datetime("post_date_gmt")
val content = text("post_content")
val excerpt = text("post_excerpt")
val guid = text("guid")
val postType = text("post_type")
val toPing = text("to_ping")
val pinged = text("pinged")
val postContentFiltered = text("post_content_filtered")
val postMimeType = text("post_mime_type")
val postStatus = text("post_status")
val postParent = integer("post_parent")
}
I'm able to insert the row with an insert statement but I can't retrieve the inserted value, I get an error:
com.babacomarket.backend.model.database.Posts.ID is not in record set
override fun createSubscription(subscription: SubscriptionRequest): Int {
return transaction {
val currentDate = DateTime()
val currentDateUtc = currentDate.withZone(DateTimeZone.UTC)
val id = Posts.insert {
it[title] = "Subscription – " + DateTime.now()
.toString(DateTimeFormat.forPattern(POST_TITLE_DATE_FORMATTER))
it[author] = 1
it[date] = currentDate
it[dateGMT] = currentDateUtc
it[postStatus] = "wc-active"
it[postType] = "shop_subscription"
it[content] = ""
it[excerpt] = ""
it[pinged] = ""
it[toPing] = ""
it[postContentFiltered] = ""
}get Posts.id
return@transaction id
}
}
In the end I solved by editing the id
column as an entity
column and by defining my table as an IdTable<Int>
:
object Posts : IdTable<Int>("wpv1_posts") {
override val id: Column<EntityID<Int>> = integer("ID").entityId()
/// other columns, truncated for brevity
}
The insert becomes:
override fun createSubscription(metas: List<Meta>): Int {
try {
return transaction {
val currentDate = DateTime()
val currentDateUtc = currentDate.withZone(DateTimeZone.UTC)
val id = Posts.insert {
it[title] = "title"
} get Posts.id
transaction { // update the row just created
Posts.update({ Posts.id eq id }) {
it[guid] = getGuid(id.value)
}
}
return@transaction id.value
}
} catch (e: Exception) {
return -1
}
}