MySql BatchInsert returns BigInteger

[coolemza]

BatchInsert on MySql db returns list of BigInteger as list of insert id's, even when primary key id is integer, h2:mem:test, returns list of integer in this case

[Tapac]

Could you provide sample to test that case? Because I use Exposed with MySQL and never faced such behavior.

[xJoeWoo]

I met this issue today as well. Here was my trace to this issue:

1. In batchInsert() method, result uses generatedKey from BatchInsertStatement
   

   Exposed/src/main/kotlin/org/jetbrains/exposed/sql/Queries.kt

   Line 91 in 7569ad8

   result += statement.generatedKey.orEmpty()

2. generatedKey references to resultedValues from InsertStatement
   

   Exposed/src/main/kotlin/org/jetbrains/exposed/sql/statements/BatchInsertStatement.kt

   Lines 17 to 18 in 75bd474

   	override val generatedKey: List<Map<Column<*>, Any>>?
   	get() = resultedValues

3. resultedValues is assigned by processResults()
   

   Exposed/src/main/kotlin/org/jetbrains/exposed/sql/statements/InsertStatement.kt

   Line 104 in 864e4fb

   resultedValues = processResults(rs, this)

4. processResults() is reading results from java.sql.ResultSet by getObject(), which will return a BigInteger object ignoring INT or LONG type of column, then return BigInteger object to batchInsert() directly
   

   Exposed/src/main/kotlin/org/jetbrains/exposed/sql/statements/InsertStatement.kt

   Line 34 in 864e4fb

   autoGeneratedKeys.add(hashMapOf(firstAutoIncColumn to rs.getObject(colIndx ?: 1)))

---

Not like insertAndGetId() or insertIgnoreAndGetId() method, the generated result of batchInsert() from ResultSet is not processed by a column's valueFromDB()

Exposed/src/main/kotlin/org/jetbrains/exposed/sql/Queries.kt

Lines 101 to 105 in 85f2da2

	fun <Key:Comparable<Key>, T: IdTable<Key>> T.insertIgnoreAndGetId(body: T.(UpdateBuilder<*>)->Unit) = InsertStatement<EntityID<Key>>(this, isIgnore = true).run {
	body(this)
	execute(TransactionManager.current())
	get(id)
	}

Exposed/src/main/kotlin/org/jetbrains/exposed/sql/statements/InsertStatement.kt

Lines 19 to 20 in 85f2da2

	infix operator fun <T> get(column: Column<T>): T? = resultedValues?.get(0)?.get(column)?.let { column.columnType.valueFromDB(it )} as? T
	?: if (!isIgnore) error("No key generated") else null

---

My opinion is adding column type handling to batchInsert() before returning the result, making result's type consistent with XXXAndGetId() methods.

[Tapac]

Should be fixed in master since 0.12.1 (related issue #424)