I am occasionally having a problem to insert row to postgresql. The DB server is windows version 15, and application is written in kotlin, using this component:
implementation('org.postgresql:postgresql:42.7.2')
The kotlin code to insert db:
class PlcMeasure(val position:Int,
val plcFlow:Int,
val plcLong:Int,
val plcShort:Int,
val plcThickness:Int,
val plcQRCode: String,
val enterTime: Date
) {
init {
if (!DB.loading) {
DB.executeSql(
"INSERT INTO plc_measure (position, plcFlow, plcQrcode, plcLong, plcShort, plcThickness, enterTime)" +
" VALUES ($position, $plcFlow, '$plcQRCode', $plcLong, $plcShort, $plcThickness, ${enterTime.time})")
Publisher.publish("NEW PlcMeasure $position $plcFlow")
}
}
}
And the DB.executeSql function:
object DB {
fun executeSql(sql:String, logit:Boolean = true) {
if (logit)
Logger.I("SQL $sql")
try {
val statement = conn.createStatement()
statement.queryTimeout = 10
statement.execute(sql)
statement.close()
} catch (e:Exception) {
Logger.E("SQL error:${e.message}: ${sql}")
}
}
}
The sql is a simple insert statement, I log every sql so I have this in log:
I 11:51:57.384 :SQL INSERT INTO plc_measure (position, plcFlow, plcQrcode, plcLong, plcShort, plcThickness, enterTime) VALUES (1, 4239, ' ', 0, 0, -1, 1736221917384)
E 11:51:57.387 :SQL error:错误: 信息中剩下的数据不够: INSERT INTO plc_measure (position, plcFlow, plcQrcode, plcLong, plcShort, plcThickness, enterTime) VALUES (1, 4239, ' ', 0, 0, -1, 1736221917384)
It means "not enough data in what is left in the information", but the values and the fields matches, and if I manually run this sql in tools like pgadmin, it runs ok.
I checked postgresql server log, and there is same error in the log:
2025-01-07 11:51:57.386 CST [6368] 错误: 信息中剩下的数据不够
I am really confused, please help. Is postgresql reliable?
Here are the table plc_measure's create statement.
CREATE TABLE plc_measure (
position int, -- Device's position
plcLong int, -- measured long
plcShort int, -- measured short
plcThickness int, -- measured thickness
plcFlow bigint,
plcQrcode varchar(32),
enterTime bigint,
doneTime bigint default 0,
mesid bigint default 0,
mesDirection int default 0
);
CREATE INDEX plc_measure_plcFlow on plc_measure (position, plcFlow);
CREATE INDEX plc_measure_mesid ON plc_measure (position, mesid);
Sorry I didn't investigate well on this problem before posting the question. It turned out that the qrcode received from the plc device is invalid. It contains only \0 while claiming length is 1. The problem is reproduced and fixed by checking c-style string end in the qrcode.
Thanks all who looked at the problem.