Open
Description
Dialect: SQLite
failing sql:
WITH new(number, modificationDate, firstName, lastName, languageId, telephoneNumber1, email1, street, city, zipCode, countryId) AS ( VALUES (?,?,?,?,?,?,?,?,?,?,?) )
INSERT OR REPLACE INTO customer (localId, number, modificationDate, firstName, lastName, languageId, telephoneNumber1, email1, street, city, zipCode, countryId)
SELECT old.localId, new.number, new.modificationDate, new.firstName, new.lastName, new.languageId, new.telephoneNumber1, new.email1, new.street, new.city, new.zipCode, new.countryId
FROM new LEFT JOIN customer AS old ON new.number = old.number;The above statements doe not compile in sqldelight with the error: Cannot bind unknown types or null. Running it as a SQL statement works fine.
Scheme:
import java.time.LocalDateTime;
import ch.dreipol.app.database.extensions.CustomerID;
CREATE TABLE customer (
localId INTEGER AS CustomerID NOT NULL PRIMARY KEY AUTOINCREMENT,
number TEXT,
modificationDate TEXT AS LocalDateTime,
firstName TEXT,
lastName TEXT,
languageId TEXT,
telephoneNumber1 TEXT,
email1 TEXT,
street TEXT,
city TEXT,
zipCode TEXT,
countryId TEXT
);
CREATE INDEX customer_number ON customer(number);
CREATE INDEX customer_lastName ON customer(lastName);Helper classes:
import com.squareup.sqldelight.ColumnAdapter
import java.time.LocalDateTime
class LocalDateTimeAdapter : ColumnAdapter<LocalDateTime, String> {
override fun decode(databaseValue: String): LocalDateTime {
return LocalDateTime.parse(databaseValue)
}
override fun encode(value: LocalDateTime): String {
return value.toString()
}
}
typealias CustomerID = Long