search

kotlin-orm / ktorm

A lightweight ORM framework for Kotlin with strong-typed SQL DSL and sequence APIs.
https://www.ktorm.org
Apache License 2.0
2.15k stars 147 forks source link

java.sql.SQLException: Invalid cursor position. #399

Closed IshaanKr closed 2 years ago

IshaanKr commented 2 years ago

`val all = db.states

route("/states") {
    get {
        val result = all.map { it.export(accountType) }
        return@get call.respond(HttpStatusCode.OK, SuccessResponse(result, HttpStatusCode.OK.value))
    }

}`

I am able to get the response with endpoint ("/states") but again hitting the endpoint result in SQLException.

2022-06-16 07:44:11.077 [eventLoopGroupProxy-4-1] DEBUG org.ktorm.database - SQL: SELECT state.approved_by AS state_approved_by, state.approved_at AS state_approved_at, state.approval_state AS state_approval_state, state.approval_comments AS state_approval_comments, state.created_by AS state_created_by, state.created_at AS state_created_at, state.last_updated AS state_last_updated, state.id AS state_id, state.enabled AS state_enabled, state.description AS state_description FROM state 2022-06-16 07:44:11.077 [eventLoopGroupProxy-4-1] DEBUG org.ktorm.database - Parameters: [] 2022-06-16 07:44:11.610 [eventLoopGroupProxy-4-1] DEBUG org.ktorm.database - Results: 1 2022-06-16 07:44:11.628 [eventLoopGroupProxy-4-1] TRACE org.ktorm.database - Entity: StateEntity{approvedBy=AC1000, approvedAt=2022-06-13T01:29:02, approvalState=APPROVED, approvalComments=Approved Automatically!! Created By SuperAdmin, createdBy=AC1000, created=2022-06-13T01:29:02, lastUpdated=2022-06-13T01:29:02, id=ST1655063942452, enabled=true, description=Uttar Pradesh} 2022-06-16 07:44:20.860 [eventLoopGroupProxy-4-1] ERROR ktor.application - Unhandled: GET - /states java.sql.SQLException: Invalid cursor position. at org.ktorm.database.CachedRowSet.next(CachedRowSet.kt:291) at org.ktorm.database.JdbcExtensionsKt$iterator$1.hasNext(JdbcExtensions.kt:74) at org.ktorm.entity.EntitySequence$iterator$1.hasNext(EntitySequence.kt:149) at com.example.database.entities.StateKt$stateRouting$1$1.invokeSuspend(State.kt:173) at com.example.database.entities.StateKt$stateRouting$1$1.invoke(State.kt) at com.example.database.entities.StateKt$stateRouting$1$1.invoke(State.kt) at io.ktor.server.routing.Route$buildPipeline$1$1.invokeSuspend(Route.kt:116) at io.ktor.server.routing.Route$buildPipeline$1$1.invoke(Route.kt) at io.ktor.server.routing.Route$buildPipeline$1$1.invoke(Route.kt) at io.ktor.util.pipeline.SuspendFunctionGun.loop(SuspendFunctionGun.kt:123) at io.ktor.util.pipeline.SuspendFunctionGun.proceed(SuspendFunctionGun.kt:81) at io.ktor.util.pipeline.SuspendFunctionGun.execute$ktor_utils(SuspendFunctionGun.kt:101) at io.ktor.util.pipeline.Pipeline.execute(Pipeline.kt:77) at io.ktor.server.routing.Routing$executeResult$$inlined$execute$1.invokeSuspend(Pipeline.kt:478) at io.ktor.server.routing.Routing$executeResult$$inlined$execute$1.invoke(Pipeline.kt) at io.ktor.server.routing.Routing$executeResult$$inlined$execute$1.invoke(Pipeline.kt) at io.ktor.util.debug.ContextUtilsKt.initContextInDebugMode(ContextUtils.kt:17) at io.ktor.server.routing.Routing.executeResult(Routing.kt:174) at io.ktor.server.routing.Routing.interceptor(Routing.kt:49) at io.ktor.server.routing.Routing$Plugin$install$1.invokeSuspend(Routing.kt:124) at io.ktor.server.routing.Routing$Plugin$install$1.invoke(Routing.kt) at io.ktor.server.routing.Routing$Plugin$install$1.invoke(Routing.kt) at io.ktor.util.pipeline.SuspendFunctionGun.loop(SuspendFunctionGun.kt:123) at io.ktor.util.pipeline.SuspendFunctionGun.proceed(SuspendFunctionGun.kt:81) at io.ktor.server.engine.BaseApplicationEngineKt$installDefaultTransformationChecker$1.invokeSuspend(BaseApplicationEngine.kt:122) at io.ktor.server.engine.BaseApplicationEngineKt$installDefaultTransformationChecker$1.invoke(BaseApplicationEngine.kt) at io.ktor.server.engine.BaseApplicationEngineKt$installDefaultTransformationChecker$1.invoke(BaseApplicationEngine.kt) at io.ktor.util.pipeline.SuspendFunctionGun.loop(SuspendFunctionGun.kt:123) at io.ktor.util.pipeline.SuspendFunctionGun.proceed(SuspendFunctionGun.kt:81) at io.ktor.util.pipeline.SuspendFunctionGun.execute$ktor_utils(SuspendFunctionGun.kt:101) at io.ktor.util.pipeline.Pipeline.execute(Pipeline.kt:77) at io.ktor.server.engine.DefaultEnginePipelineKt$defaultEnginePipeline$1$invokeSuspend$$inlined$execute$1.invokeSuspend(Pipeline.kt:478) at io.ktor.server.engine.DefaultEnginePipelineKt$defaultEnginePipeline$1$invokeSuspend$$inlined$execute$1.invoke(Pipeline.kt) at io.ktor.server.engine.DefaultEnginePipelineKt$defaultEnginePipeline$1$invokeSuspend$$inlined$execute$1.invoke(Pipeline.kt) at io.ktor.util.debug.ContextUtilsKt.initContextInDebugMode(ContextUtils.kt:17) at io.ktor.server.engine.DefaultEnginePipelineKt$defaultEnginePipeline$1.invokeSuspend(DefaultEnginePipeline.kt:118) at io.ktor.server.engine.DefaultEnginePipelineKt$defaultEnginePipeline$1.invoke(DefaultEnginePipeline.kt) at io.ktor.server.engine.DefaultEnginePipelineKt$defaultEnginePipeline$1.invoke(DefaultEnginePipeline.kt) at io.ktor.util.pipeline.SuspendFunctionGun.loop(SuspendFunctionGun.kt:123) at io.ktor.util.pipeline.SuspendFunctionGun.proceed(SuspendFunctionGun.kt:81) at io.ktor.util.pipeline.SuspendFunctionGun.execute$ktor_utils(SuspendFunctionGun.kt:101) at io.ktor.util.pipeline.Pipeline.execute(Pipeline.kt:77) at io.ktor.server.netty.NettyApplicationCallHandler$handleRequest$1$invokeSuspend$$inlined$execute$1.invokeSuspend(Pipeline.kt:478) at io.ktor.server.netty.NettyApplicationCallHandler$handleRequest$1$invokeSuspend$$inlined$execute$1.invoke(Pipeline.kt) at io.ktor.server.netty.NettyApplicationCallHandler$handleRequest$1$invokeSuspend$$inlined$execute$1.invoke(Pipeline.kt) at io.ktor.util.debug.ContextUtilsKt.initContextInDebugMode(ContextUtils.kt:17) at io.ktor.server.netty.NettyApplicationCallHandler$handleRequest$1.invokeSuspend(NettyApplicationCallHandler.kt:119) at io.ktor.server.netty.NettyApplicationCallHandler$handleRequest$1.invoke(NettyApplicationCallHandler.kt) at io.ktor.server.netty.NettyApplicationCallHandler$handleRequest$1.invoke(NettyApplicationCallHandler.kt) at kotlinx.coroutines.intrinsics.UndispatchedKt.startCoroutineUndispatched(Undispatched.kt:55) at kotlinx.coroutines.CoroutineStart.invoke(CoroutineStart.kt:112) at kotlinx.coroutines.AbstractCoroutine.start(AbstractCoroutine.kt:126) at kotlinx.coroutines.BuildersKt__Builders_commonKt.launch(Builders.common.kt:56) at kotlinx.coroutines.BuildersKt.launch(Unknown Source) at io.ktor.server.netty.NettyApplicationCallHandler.handleRequest(NettyApplicationCallHandler.kt:37) at io.ktor.server.netty.NettyApplicationCallHandler.channelRead(NettyApplicationCallHandler.kt:29) at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) at io.netty.channel.AbstractChannelHandlerContext.access$600(AbstractChannelHandlerContext.java:61) at io.netty.channel.AbstractChannelHandlerContext$7.run(AbstractChannelHandlerContext.java:370) at io.netty.util.concurrent.AbstractEventExecutor.safeExecute(AbstractEventExecutor.java:164) at io.netty.util.concurrent.SingleThreadEventExecutor.runAllTasks(SingleThreadEventExecutor.java:469) at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:503) at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:986) at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74) at io.ktor.server.netty.EventLoopGroupProxy$Companion.create$lambda-1$lambda-0(NettyApplicationEngine.kt:260) at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30) at java.base/java.lang.Thread.run(Thread.java:833)

I am following KTORM official docs. What I am doing wrong?

Full file `package com.example.database.entities

import com.example. import com.example.database.DatabaseManager import io.ktor.server.application. import io.ktor.server.request. import io.ktor.server.routing. import org.ktorm.database.Database import org.ktorm.dsl.eq import org.ktorm.entity. import java.time.LocalDateTime import com.example.AccountType.SUPERADMIN import com.example.AccountType.USER import com.example.AccountType.ADMIN import com.example.database.DatabaseManager.db import io.ktor.http. import io.ktor.server.response. import org.ktorm.schema.

val Database.states get() = this.sequenceOf(States)

private const val STATE_ID_PREFIX = "ST" private fun createNewStateID() = STATE_ID_PREFIX + System.currentTimeMillis()

fun Route.stateRouting() {

val all = db.states

route("/states") {
    get {
        val result = all.map { it.export(accountType) }
        return@get call.respond(HttpStatusCode.OK, SuccessResponse(result, HttpStatusCode.OK.value))
    }

    post {
        proceedOnlyIfAdmin()
        val payload = call.receive<CreateStateRequest>()

        if (all.filter { it.description eq payload.name }.map { it.export(accountType) }.isEmpty()
                .not()
        ) sendAlreadyExist()

        getEntityToAdd(currentAccount, payload).let {
            if (all.add(it) > 0) sendCreated(it.export(accountType))
            else sendDBError()
        }
    }

// patch("/{id}") { // proceedIfAdmin() // val payload = call.receive() // } } }

private data class PatchStateRequest( val enabled: Boolean? )

private fun getEntityToAdd(currentAccount: AccountEntity, payload: CreateStateRequest) = when (currentAccount.accountType) { SUPERADMIN -> { StateEntity {}.apply {

            //BasicEntity
            id = createNewStateID()
            enabled = true
            description = payload.name

            //Traceable Entity
            created = LocalDateTime.now()
            createdBy = currentAccount.id
            lastUpdated = LocalDateTime.now()

            //Approvable Entity
            approvalState = ApprovalState.APPROVED
            approvalComments = AUTO_APPROVE_COMMENTS
            approvedAt = LocalDateTime.now()
            approvedBy = currentAccount.id
        }
    }
    ADMIN -> StateEntity {}.apply {

        //BasicEntity
        id = createNewStateID()
        enabled = false
        description = payload.name

        //Traceable Entity
        created = LocalDateTime.now()
        createdBy = currentAccount.id
        lastUpdated = LocalDateTime.now()

        //Approvable Entity
        approvalState = ApprovalState.PENDING

    }
    else -> throw RuntimeException()
}

private class CreateStateRequest( val name: String )

object States : Table("state") { val approvedBy = varchar("approved_by").bindTo { it.approvedBy } val approvedAt = datetime("approved_at").bindTo { it.approvedAt } val approvalState = enum("approval_state").bindTo { it.approvalState } val approvalComments = varchar("approval_comments").bindTo { it.approvalComments } val createdBy = varchar("created_by").bindTo { it.createdBy } val createdAt = datetime("created_at").bindTo { it.created } var lastUpdated = datetime("last_updated").bindTo { it.lastUpdated } val id = varchar("id").primaryKey().bindTo { it.id } var enabled = boolean("enabled").bindTo { it.enabled } val description = varchar("description").bindTo { it.description }

}

interface StateEntity : Entity { companion object : Entity.Factory()

var id: String
var enabled: Boolean
var description: String?
var createdBy: String?
var created: LocalDateTime?
var lastUpdated: LocalDateTime?
var approvedBy: String?
var approvedAt: LocalDateTime?
var approvalState: ApprovalState?
var approvalComments: String?

fun export(accountType: AccountType) = when (accountType) {
    SUPERADMIN, ADMIN -> StateResponse().also {
        //Approvable Response
        it.approvalState = approvalState
        it.approvedAt = approvedAt
        it.approvedBy = approvedBy

        //Traceable Response
        it.created = created
        it.createdBy = createdBy
        it.lastUpdated = lastUpdated

        //Base Response
        it.id = id
        it.enabled = enabled
        it.description = description
    }
    USER -> if (enabled) StateResponse().also {
        //Base Response
        it.id = id
        it.description = description
    } else null
    else -> null
}

}

data class StateResponse( var approvedBy: String? = null, var approvedAt: LocalDateTime? = null, var approvalState: ApprovalState? = null, var createdBy: String? = null, var created: LocalDateTime? = null, var lastUpdated: LocalDateTime? = null, var id: String? = null, var description: String? = null, var enabled: Boolean? = null, )`

IshaanKr commented 2 years ago

Getting response when restarted the application

Screenshot 2022-06-16 at 7 50 11 AM
IshaanKr commented 2 years ago

java.sql.SQLException: Invalid cursor position. at org.ktorm.database.CachedRowSet.next(CachedRowSet.kt:291) at org.ktorm.database.JdbcExtensionsKt$iterator$1.hasNext(JdbcExtensions.kt:74) at org.ktorm.entity.EntitySequence$iterator$1.hasNext(EntitySequence.kt:149) at com.example.database.entities.StateKt$stateRouting$1$1.invokeSuspend(State.kt:173)

override fun next(): Boolean { if (_cursor >= -1 && _cursor < _values.size) { return ++_cursor < _values.size } else { throw SQLException("Invalid cursor position.") } }

When hitting the enpoint again, the cursor value is -1

vincentlauvlwj commented 2 years ago

DO NOT reuse entity sequence objects, change your code like this: 

// REMOVE the below line
// val all = db.states

route("/states") {
    get {
        val result = db.states.map { it.export(accountType) }
        return@get call.respond(HttpStatusCode.OK, SuccessResponse(result, HttpStatusCode.OK.value))
    }

    post {
        proceedOnlyIfAdmin()
        val payload = call.receive<CreateStateRequest>()

        if (db.states.filter { it.description eq payload.name }.map { it.export(accountType) }.isEmpty()
                .not()
        ) sendAlreadyExist()

        getEntityToAdd(currentAccount, payload).let {
            if (db.states.add(it) > 0) sendCreated(it.export(accountType))
            else sendDBError()
        }
    }
}
IshaanKr commented 2 years ago

@vincentlauvlwj No more exception. Thankyou so much. I was stuck from past 3 days. Kudos!!