[#GROOVY-2585] Can not insert nulls into the table using Sql.execute

groovy

Can not insert nulls into the table using Sql.execute

Created: 06/Feb/08 03:27 PM Updated: 05/Nov/08 07:55 PM

Component/s:

SQL processing

Affects Version/s:

1.5.4

Fix Version/s:

1.6-beta-1, 1.5.5

Time Tracking:

Not Specified

Environment:

Windows XP

Issue Links:

Related

This issue is related to:

~~GROOVY-2146~~ Can't insert null values into database

Testcase included:

yes

Description

« Hide

Running this script:

import groovy.sql.Sql

Sql sql = Sql.newInstance("jdbc:oracle:oci:@???, "???", "???", "oracle.jdbc.driver.OracleDriver")

sql.execute "CREATE TABLE TEST (A INT, B INT)"

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

Results in:

Caught: java.sql.SQLException: ORA-01008: not all variables bound
	at test.run(test.groovy:11)
	at test.main(test.groovy)

AFAIK, th problem lies with the following code (groovy.sql.Sql.java: 1390)

if (value != null) {
                setObject(statement, i++, value);

I think it should be

if (value != null) {
                setObject(statement, i++, value);
          else
                 statement.setNull(i, in.getType());

All

Comments

Work Log

Change History

Sort Order:

Ascending order - Click to sort in descending order

[ Permlink | « Hide ]

Paul King - 06/Feb/08 04:09 PM - edited

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.

[ Permlink | « Hide ]

Paul King - 06/Feb/08 04:11 PM

Add code tags

[ Permlink | « Hide ]

Paul King - 06/Feb/08 04:12 PM

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

[ Permlink | « Hide ]

Paul King - 08/Feb/08 06:50 AM

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

[ Permlink | « Hide ]

Paul King - 08/Feb/08 04:01 PM

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.

[ Permlink | « Hide ]

Paul King - 08/Feb/08 05:12 PM

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

[ Permlink | « Hide ]

Phil Walker - 02/Mar/08 02:16 PM

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)

[ Permlink | « Hide ]

Phil Walker - 02/Mar/08 06:52 PM

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

[ Permlink | « Hide ]

Paul King - 02/Mar/08 09:16 PM - edited

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

[ Permlink | « Hide ]

Nicolas, Brian - 05/Nov/08 05:44 AM

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
])

[ Permlink | « Hide ]

Jochen Theodorou - 05/Nov/08 06:19 AM

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.

[ Permlink | « Hide ]

Nicolas, Brian - 05/Nov/08 06:28 PM

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

[ Permlink | « Hide ]

Jochen Theodorou - 05/Nov/08 07:47 PM

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.

[ Permlink | « Hide ]

Nicolas, Brian - 05/Nov/08 07:55 PM

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.