What is the correct way to return results of a query?

[ndchorley]

Hi,

I'd like to abstract away the database access into a function, for example:

object Users: Table() {
    val id = integer("id").primaryKey()
    val name = varchar("name", 50)
}

data class User(val id: Int, val name: String)

fun getUsers(): List<User> {
    Database.connect("jdbc:sqlite:users.db", "org.sqlite.JDBC")
    return Users.selectAll().map { User(it[Users.id], it[Users.name]) }
}

This doesn't work - it throws java.lang.IllegalStateException: No transaction in context. It seems that you have to wrap everything inside transaction:

fun getUsers(): List<User> {
    Database.connect("jdbc:sqlite:users.db", "org.sqlite.JDBC")
    TransactionManager.manager.defaultIsolationLevel = Connection.TRANSACTION_SERIALIZABLE

    transaction {
        return Users.selectAll().map { User(it[Users.id], it[Users.name]) }
    }
}

In this case, the code won't compile, complaining that "return not allowed here". Since the only operation here is a read, I'm not sure why one needs to use a transaction anyway.

So, then, how do I return the list of my value objects after processing the rows from the query?

Thanks!

[Darych]

I'm doing the same thing. As the simplest solution, I can suggest you, to store your results in temporary List and return this instance of List. Hope this will help!

fun getUsers(): List<User> {
    Database.connect("jdbc:sqlite:users.db", "org.sqlite.JDBC")
    TransactionManager.manager.defaultIsolationLevel = Connection.TRANSACTION_SERIALIZABLE

    res = mutableListOf<User>()
    transaction {
        res = Users.selectAll().map { User(it[Users.id], it[Users.name]) }
    }
    return res
} 

[ndchorley]

Yeah, I'd considered that, but it's a bit weird to have to create a list you're not going to use (granted, it won't use much memory, but still...).

[Tapac]

If it's not the only place where you want to use such approach (note that you may call connect once per application run), then I advice to introduce helper function:

fun connectAndExec(statement: Transaction.() -> T) : T {
   Database.connect("jdbc:sqlite:users.db", "org.sqlite.JDBC")
   return transaction(Connection.TRANSACTION_SERIALIZABLE, 3, statement)
}

then your code will look like

fun getUsers(): List<User> = connectAndExec {
    Users.selectAll().map { User(it[Users.id], it[Users.name]) }
}

[ndchorley]

Thanks, @Tapac.

[lamba92]

I'm having a similar error No transaction in context. but I don't get where i am wrong:

val cameras = serializableTransaction(Settings.localDatabase) {
    Camera.find { CameraTable.configured eq false }
}
call.respond(cameras.map { it.toMap() })

fun <T> serializableTransaction(db: Database? = null, function: Transaction.() -> T)
        = transaction(Connection.TRANSACTION_SERIALIZABLE, 3, db,function)

While here works like a charm:

stuff.filter { (_, info) ->
                serializableTransaction(Settings.localDatabase) {
                    Camera.all().none {
                        it.hwId == info.hardwareId ||
                            it.manufacturer == info.manufacturer ||
                            it.model == info.model ||
                            it.serialNumber == info.serialNumber
                    }
                }

[Tapac]

If you have any text or blob fields in your table most of jdbc drivers them wont load them into memory until you ask for it. So you need to make toMap within a transaction block.

val cameras = serializableTransaction(Settings.localDatabase) {
    Camera.find { CameraTable.configured eq false }.map { it.toMap() }
}
call.respond(cameras)

[lamba92]

That was it, thanks!! 😄