groovy

Looks similar to GROOVY-2146, perhaps the previous fixes weren't as generic as they needed to be. We should try to test on e.g. Oracle, hsql and mysql this time.

Add code tags

Your suggested fix appears correct at first glance. I will try to apply and test when I get a chance.

OK, the patch I just applied seems ot work with hsql, oracle and mysql. Please reopen if you have further problems.

I am reopening until I perform all my earlier tests across all three databases. Our automated test (and CI) environment only does hsql so this has to be done manually.

Improved solution (previous change would have caused a regression) and now tested on hsql, oracle and mysql for both GString and String variations

groovy version 1.5.4 JVM: 10.0-b19 still has a problem with Postgresql 8.3

The script above results in the following error message:

Caught: org.postgresql.util.PSQLException: No value specified for parameter 2.
at test.run(test.groovy:11)
at test.main(test.groovy)

verified fix by testing with groovy 1.6 beta code and postgresql 8.3

Great! Thanks for testing that. This has been merged onto 1_5_X branch so should appear in 1.5.5 too.

Hi,

I don't think this is already fixed. I am using Sybase and Groovy 1.5.6 and I still get the following:

Caught: java.sql.SQLException: JZ0SE: Invalid object type (or null object) specified for setObject().

I use code below but if any one of the fields is null, I get the error.

dest.executeUpdate(insertSql, [
srcData.system_code,
srcData.ext_exec_id,
srcData.ext_msg_id,
srcData.priority,
srcData.sink_id,
srcData.time_stamp,
srcData.hldAcct,
srcData.hldTrader,
srcData.hldGroup,
srcData.hldCust,
srcData.hldCurr,
srcData.hldSecId,
srcData.hldSecName,
srcData.ordExch,
srcData.ordComment,
srcData.ordBlotter,
srcData.ordSystem,
srcData.ordTrader,
srcData.ordBSCode,
srcData.execPrice,
srcData.execQty,
srcData.execSettleDt,
srcData.execTradeDt,
srcData.execTradeTm,
srcData.execQuotePrice,
srcData.execComment,
srcData.hldLegalEntity,
srcData.location,
srcData.cancel,
srcData.appType,
srcData.ordBroker,
srcData.execStringID,
srcData.orderStringID
])

Nicolas, I suggest that you fill a new issue if none of your objects is a InParameter instance. Because in this case setNull is not used. This issue was for InParameters. The problem is that, if it is no InParameter we do not know the type we have to give to the setNull call. Therefor it would be good if you could show as how you would do that in plain jdbc java. If you do not want to use InParamerter then we would have to query the database for the column type and use that type in the setNull call. But that is quite slow.

Hi Jochen,

Thanks for the reply but I don't actually get you when you said that none of my objects is an InParameter instance. I am trying to execute code similar to the problem description:

sql.execute " insert into test (A,B) values (?,?)", [10, null] // fails

InParameter is an inner class of groovy.sql.Sql and is used to have a type even if the value is null. The problem is that PreparedStatement#setNull normally expects a type. In many databases a simple setObject(x,null) would fail and Sybase is one of these. So the usage of setNull is required. This also requires a type argument and I think there are about 3 variants. Types.OTHER, Types.NULL and the real type. AFAIK the first two fail in some databases and in others not. It depends much on the jdbc driver. And sadly there are those drivers that allow neither setObject with null, nor setNull without type and even the possible types for setNull are different. So the code fails, yes, but not really because of a bug in Groovy itself.

Ok, everything is clear now. As an alternative, I tried the normal format similar to the following and now works.

sql.execute "insert into test (A,B) values (${a},${b})"

Thanks a lot Jochen.