How to insert into two tables and return two PKs f...

# exposed

[Manas Marthi]

How to insert into two tables and return two PKs from transaction. See my sample code. I am getting return statement not allowed error.

suspend fun insertIntoTwoTables(x: Int, y: Int) {
    Database.dbQuery{
    transaction{
        var a = Numbers.insert { it[Numbers.num] = x }
        val aId = a.resultedValues?.get(Numbers.id) ?.let { it.toLong() }

        val b = Strings.insert { 
            it[Strings.str] = y.toString() 
            it[Strings.numId] = aId
        }
        val bId = b.resultedValues?.get(Strings.id) ?.let { it.toLong() }

        return Pair(aId, bId)
    }
    }
}

[AdamW]

You can’t have an unqualified return inside a non-inline lambda. The outer function has a return type of Unit so this couldn’t work anyway.

No need to open another transaction inside your dbQuery either.

fun thing(): Int = transaction {
 10
} // ok

fun thing() {
 transaction {
  return 10
 }
} // error

fun thing(): Int {
 transaction {
  10
 } // error
}

fun thing(): Int {
 return transaction {
  10
 }
} // ok

[Manas Marthi]

thank you. I guess newSuspendedTransaction call is taking care of opening the transaction. Isn't it? Is it possible to call a stored procedure using Exposed?

[AdamW]

Not sure if there is DSL for it, but you can always use exec to run arbitrary SQL 👍 And yes, it opens a transaction

[Manas Marthi]

that's good to know. could you please give a sample of using exec to call a stored procedure or run a update query or select query?