Search code examples
javaarraysjdbchsqldb

How do I update a single element of an ARRAY with a prepared statement?

We have a HSQLDB table with an ARRAY column. How can we update a single element with a low-cost prepared statement?

So far, we have tried:

PreparedStatement pstmt = con.prepareStatement("UPDATE mytbl SET col[?] = ? WHERE id = ?");
...
pstmt.setInt(1, idx);
pstmt.setInt(2, val);
pstmt.setInt(3, id);
...

But that does not work.

Solution

  • The database engine needs a CAST as a hint to determine the type of one of the parameters. Use the type of array elements in the case. The examples below show the required cast when the array contains INTEGER or TIMESTAMP values:

    PreparedStatement pstmt = con.prepareStatement("UPDATE mytbl SET col[?] = CAST(? AS INT) WHERE id = ?");
PreparedStatement pstmt = con.prepareStatement("UPDATE mytbl SET col[?] = CAST(? AS TIMESTAMP) WHERE id = ?");