org.h2.jdbc.JdbcSQLException: Column "id" not found

[icholy]

I'm always getting this exception when passing IdStrategy.Generated to insert.

Exception in thread "main" org.h2.jdbc.JdbcSQLException: Column "id" not found [42122-196]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
	at org.h2.message.DbException.get(DbException.java:179)
	at org.h2.message.DbException.get(DbException.java:155)
	at org.h2.jdbc.JdbcResultSet.getColumnIndex(JdbcResultSet.java:3180)
	at org.h2.jdbc.JdbcResultSet.get(JdbcResultSet.java:3246)
	at org.h2.jdbc.JdbcResultSet.getInt(JdbcResultSet.java:350)
	at com.github.andrewoma.kwery.core.Row.int(Row.kt:47)
	at com.github.andrewoma.kwery.mapper.intConverter$1.invoke(Converters.kt:80)
	at com.github.andrewoma.kwery.mapper.intConverter$1.invoke(Converters.kt:80)
	at com.github.andrewoma.kwery.mapper.Table$rowMapper$1$1.of(Table.kt:206)
	at com.company.thing.ThingTable.create(EventServer.kt:28)
	at com.company.thing.ThingTable.create(EventServer.kt:23)
	at com.github.andrewoma.kwery.mapper.Table$rowMapper$1.invoke(Table.kt:204)
	at com.github.andrewoma.kwery.mapper.Table$rowMapper$1.invoke(Table.kt:39)
	at com.github.andrewoma.kwery.mapper.AbstractDao$insert$1$1.invoke(AbstractDao.kt:289)
	at com.github.andrewoma.kwery.mapper.AbstractDao$insert$1$1.invoke(AbstractDao.kt:32)
	at com.github.andrewoma.kwery.core.DefaultSession$insert$1.invoke(DefaultSession.kt:142)
	at com.github.andrewoma.kwery.core.DefaultSession$insert$1.invoke(DefaultSession.kt:46)
	at com.github.andrewoma.kwery.core.DefaultSession.withPreparedStatement(DefaultSession.kt:252)
	at com.github.andrewoma.kwery.core.DefaultSession.insert(DefaultSession.kt:135)
	at com.github.andrewoma.kwery.mapper.AbstractDao$insert$1.invoke(AbstractDao.kt:288)
	at com.github.andrewoma.kwery.mapper.AbstractDao.withTransaction(AbstractDao.kt:100)
	at com.github.andrewoma.kwery.mapper.AbstractDao.insert(AbstractDao.kt:277)
	at com.company.thing.main(Main.kt:48)

Process finished with exit code 1

package com.company.thing

import com.github.andrewoma.kwery.core.DefaultSession
import com.github.andrewoma.kwery.core.Session
import com.github.andrewoma.kwery.core.dialect.HsqlDialect
import com.github.andrewoma.kwery.mapper.AbstractDao
import com.github.andrewoma.kwery.mapper.IdStrategy
import com.github.andrewoma.kwery.mapper.Table
import com.github.andrewoma.kwery.mapper.Value
import java.sql.DriverManager

class Thing(
        var id: Int,
        var name: String
)

object ThingTable: Table<Thing, Int>("thing") {
    val Id   by col(Thing::id, id = true)
    val Name by col(Thing::name)

    override fun create(value: Value<Thing>): Thing {
        return Thing(value of Id, value of Name)
    }
    override fun idColumns(id: Int) = setOf(Id of id)
}

class ThingDao(session: Session): AbstractDao<Thing, Int>(session, ThingTable, Thing::id)

fun main(args: Array<String>) {

    val conn = DriverManager.getConnection("jdbc:h2:mem:test")
    val session = DefaultSession(conn, HsqlDialect())

    session.update("""
        create table thing (
            id   integer auto_increment not null primary key,
            name character varying(255) not null,
        )
    """)

    val dao = ThingDao(session)
    dao.insert(Thing(0, "foo"), IdStrategy.Generated)
}

[andrewoma]

Hi, you're using a HsqlDialect with H2, not Hsql.

It seems H2 doesn't return named generated keys.

Kwery doesn't support H2 at the moment.

If possible use Hsql - it seems quite comparable to H2.

Cheers,
Andrew

[icholy]

@andrewoma /facepalm thanks