Use COALESCE in prepareStatement for "insert into" cmd

2020-05-04 java sql prepared-statement hsqldb

I am trying to execute below cmd but this one is throwing an exception:

org.hsqldb.HsqlException: incompatible data type in operation

conn.prepareStatement("INSERT INTO TableA (colA) VALUES (IFNULL(?, 1)))

Here TableA has colA with integer datatype. However, if I change above cmd to

conn.prepareStatement("INSERT INTO TableA (colA) VALUES(IFNULL(null, 1)))

works properly but doesn't make any sense because want to pass colA value at runtime.


   ps = conn.prepareStatement("INSERT INTO TableA (colA) VALUES(IFNULL(?, 1))")

   ps.setObject(1, 5);  // this cmd doesn't have any problem code is failing at preparestatement itself


Use setInt instead of setObject

ps.setInt(1, 5);

setObject is generic so if you want to force to use a type you can use the third parameter as follow:

ps.setObject(1, 5, JDBCType.INTEGER);

Because you have used a constant value, so your compiler must decide if is an integer or another numeric type.

You can write also

Integer myValue = 5;
ps.setObject(1, myValue);


Integer myValue = 5;
ps.setObject(1, myValue.intValue());