为什么使用准备好的语句会因为空值而失败，而使用 GString 则会成功？

发布于 2024-09-24 13:44:53 字数 1539 浏览 9 评论 0原文

简而言之，问题是：

当尝试使用准备好的语句和 groovy.sql.Sql 插入具有一些空列的行时会发生这种情况：

groovy:000> val
===> [123123123, 2, null, 0, 0, , null, , 1213020112511801, 1283425009158952, 1, 2, null, 0, 0, , null, , 1213020112511801, 1283425009158952]
groovy:000> destSql.execute "insert into my_table values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", val
ERROR java.sql.SQLException: Type is not supported.
        at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException (Unknown Source)
        at org.apache.derby.client.am.SqlException.getSQLException (Unknown Source)
        at org.apache.derby.client.am.PreparedStatement.setObject (Unknown Source)
        at groovysh_evaluate.run (groovysh_evaluate:3)
        ...
groovy:000>

我使用 destSql.dataSet("my_table") 得到了类似的结果。添加 valueMap。

这就是尝试插入与 GString 相同的行时发生的情况：

groovy:000> destSql.execute "insert into my_table values (${val[0]}, ${val[1]}, ${val[2]}, ${val[3]}, ${val[4]}, ${val[5]}, ${val[6]}, ${val[7]}, ${val[8]}, ${val[9]})"
===> false
groovy:000>

现在，我了解到使用准备好的语句插入空值存在问题（如文档所述，例如此处和此处）。

我想了解的是：

1）我很难相信用准备好的语句插入空值是不可能的，那么为什么它有时会导致问题呢？不够完善的 JDBC 驱动程序、数据库引擎？

2) 为什么使用 GString 的示例可以工作？遵循相同的逻辑，它不是仍然键入有关空列的信息吗？那么它是如何工作的呢？

原文

The problem in a nutshell:

This is what happens when trying to insert a row with a few null columns using a prepared statement and groovy.sql.Sql:

groovy:000> val
===> [123123123, 2, null, 0, 0, , null, , 1213020112511801, 1283425009158952, 1, 2, null, 0, 0, , null, , 1213020112511801, 1283425009158952]
groovy:000> destSql.execute "insert into my_table values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", val
ERROR java.sql.SQLException: Type is not supported.
        at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException (Unknown Source)
        at org.apache.derby.client.am.SqlException.getSQLException (Unknown Source)
        at org.apache.derby.client.am.PreparedStatement.setObject (Unknown Source)
        at groovysh_evaluate.run (groovysh_evaluate:3)
        ...
groovy:000>

I get a similar result with destSql.dataSet("my_table").add valueMap.

This is what happens when trying to insert the same row as a GString:

groovy:000> destSql.execute "insert into my_table values (${val[0]}, ${val[1]}, ${val[2]}, ${val[3]}, ${val[4]}, ${val[5]}, ${val[6]}, ${val[7]}, ${val[8]}, ${val[9]})"
===> false
groovy:000>

Now, I understand that there are problems inserting null values using a prepared statement (as documented e.g. here and here).

What I would like to understand is the following:

1) I find it hard to believe that inserting nulls with prepared statements is impossible, so why does it sometimes cause problems? Less than perfect JDBC drivers, database engines?

2) Why does the example with the GString work? Following the same logic, doesn't it still type information even about the null columns, so how come it works?

分享到QQ

分享到微博