Nullable primitive columns cannot be compiled #31
Description
Environment
- Engine:
postgresql - Plugin:
sqlc-gen-kotlin_1.2.0.wasm/22b437ecaea66417bbd3b958339d9868ba89368ce542c936c37305acf373104b - sqlc version: 1.28.0
Issue
Setup
I have the following migration.
CREATE TABLE checking_accounts
(
id SERIAL PRIMARY KEY,
deposit_amount INT,
deposit_rate DOUBLE PRECISION
);I have the following query,
-- name: CreateCheckingAccount :one
INSERT INTO checking_accounts (deposit_amount, deposit_rate)
VALUES ($1, $2) RETURNING *;Generated Code
The generated model
data class CheckingAccount (
val id: Int,
val depositAmount: Int?,
val depositRate: Double?
)Generated queries
@Throws(SQLException::class)
override fun createCheckingAccount(depositAmount: Int?, depositRate: Double?): CheckingAccount? {
return conn.prepareStatement(createCheckingAccount).use { stmt ->
stmt.setInt(1, depositAmount)
stmt.setDouble(2, depositRate)
val results = stmt.executeQuery()
if (!results.next()) {
return null
}
val ret = CheckingAccount(
results.getInt(1),
results.getInt(2),
results.getDouble(3)
)
if (results.next()) {
throw SQLException("expected one row in result set, but got many")
}
ret
}
Errors
Compile Error
The JDBC methods .setInt and .setDouble expect non-nullable primitive types. Since the arguments passed into createCheckingAccount are nullable, this code cannot compile.
Runtime Errors
Additionally, .getInt and .getDouble will return 0 and 0.0 if it is null in the row. These should be null. But I am not directly running into this issue today.
Proposed Changes
Compile Errors
We need to check if the fields are null. If they are, set the parameter with stmt.setNull(1, Types.INTEGER). For example
if (depositAmount != null) {
stmt.setInt(1, depositAmount)
} else {
stmt.setNull(1, Types.INTEGER)
}