Why does using a prepared statement fail with nulls and succed with GStrings?

Question

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?

Answer 1

the gstring example is working because the code ${val[0]} will return null if the array element is null.

if the code you inserted in the question is correct, some of you elements do not have a null value... this just a guess

Answer 2

You'll get better responses if you include the actual exception that you've encountered. You can find the exception in your Derby server's derby.log file, or you can also modify your application to print the entire exception chain, not just the client-side outermost exception: http://wiki.apache.org/db-derby/UnwindExceptionChain

Without seeing the actual exception, it's quite hard to help you, but I'll venture a guess that you're seeing https://issues.apache.org/jira/browse/DERBY-1938