英文:
How do I update a single element of an ARRAY with a prepared statement?
问题
我们有一个带有ARRAY列的HSQLDB表格。如何使用低成本的预处理语句来更新单个元素？
到目前为止，我们尝试过：
PreparedStatement pstmt = con.prepareStatement("UPDATE mytbl SET col[?] = ? WHERE id = ?");
...
pstmt.setInt(1, idx);
pstmt.setInt(2, val);
pstmt.setInt(3, id);
...
但是这并不起作用。
英文:
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.
答案1
得分: 1
数据库引擎需要一个CAST作为提示，以确定参数之一的类型。在这种情况下，请使用数组元素的类型。下面的示例显示了当数组包含INTEGER或TIMESTAMP值时所需的转换：
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 = ?");
英文:
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 = ?");
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论