Use COALESCE in prepareStatement for "insert into" cmd

Question

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.

code:

   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

I think this is a problem with passing the parameter, not the code. How is the parameter set? Clarify in your question. — Gordon Linoff, May 04 '20 at 13:25
This should work with the latest version of HSQLDB. Which version are you using? — fredt, May 05 '20 at 03:10

score 1 · Answer 1 · answered May 04 '20 at 13:51

1

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);

or

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

answered May 04 '20 at 13:51

Joe Taras

15,166
7
42
55

@BhawaniSingh please post the error you have with my code – Joe Taras May 04 '20 at 13:57
getting `org.hsqldb.HsqlException: incompatible data type in operation` while executing `conn.prepareStatement("INSERT INTO TableA (colA) VALUES (IFNULL(?, 1))")` line – Noob May 04 '20 at 14:02
@BhawaniSingh are you sure colA has integer as datatype? – Joe Taras May 04 '20 at 14:14

score 0 · Answer 2 · answered May 04 '20 at 13:31

0

Use setInt instead of setObject

ps.setInt(1, 5);

answered May 04 '20 at 13:31

Vikas

6,868
4
27
41

it's failing in prepareStatement itself – Noob May 04 '20 at 13:32

Use COALESCE in prepareStatement for "insert into" cmd

2 Answers2