[#GROOVY-1358] getParameters and asSql process / strip nulls incorrectly

groovy

getParameters and asSql process / strip nulls incorrectly

Created: 18/Jun/06 01:05 AM Updated: 25/Dec/08 11:18 PM

Component/s:

groovy-jdk, SQL processing

Affects Version/s:

1.0-JSR-5

Fix Version/s:

1.5.2

Time Tracking:

Not Specified

File Attachments:

groovy.sql.Sql.patch (1.0 kB)

Environment:

Mac OSX on Intel, Java 1.5.0_06

Issue Links:

Duplicate

This issue duplicates:

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

Description

« Hide

getParameters(GString) is incorrectly stripping null values out.
This causes asSql(GString,params) to fail to replace null GString variables with empty strings.

example:

def x = null
def y = 1
sql.execute("""
   insert into TABLE (X_COL, Y_COL) 
   values (${x},${y})

Produces sql of

insert into TABLE(X_COL, Y_COL)
values (?,)

The trailing variable is completely missing due to getParameters stripping nulls.
All functions who call getParameters pass the returned list to asSql who checks for null to know if it should use "?" or "null" for that variable.

asSql was also missing an iter.remove() when processing a null value from the GString. If it isn't removed, setParameters() will choke as it will set one too many parameters (due to the ? not existing on the prepared statement)

I'm attaching a patch against head that fixes this.

All

Comments

Work Log

Change History

Sort Order:

[ Permalink | « Hide ]

David Budworth added a comment - 18/Jun/06 01:12 AM

My first few lines in the description didn't exactly make sense.

asSql is expected to replace null object values in the GString with "null" (to avoid issues with databases not liking setObject(idx,null)), when it does this, it is supposed to remove the object from the param list so the prepared statement will have the correct number of args set to match the "?"

[ Permalink | « Hide ]

Paul King added a comment - 31/Dec/07 04:13 PM

Added code tags

[ Permalink | « Hide ]

Paul King added a comment - 31/Dec/07 04:16 PM

This should have been fixed as part of ~~GROOVY-2146~~ (sorry didn't see the duplication earlier).
Thanks for the patch (I wish I had seem it a little earlier).

[ Permalink | « Hide ]

Paul King added a comment - 08/Feb/08 01:26 AM

close off release 1.5.4

[ Permalink | « Hide ]

Nicolas, Brian added a comment - 05/Nov/08 05:38 AM

Hi,

I don't think this is already fixed. I am using Sybase and I still get

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

I use code similar to below but if any one of the fields are 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
])

[ Permalink | « Hide ]

David Budworth added a comment - 25/Dec/08 11:18 PM

I think that would be a different issue.

This bug was related to invalid SQL being generated when there were null references in a GString for sql execution.
ie:
var x=1
var y = null
Sql.executeUpdate("update foo set x=${x} y=${y}")

it would generate
"update foo set x=? y="

your issue looks more like Sybase isn't letting you set null, or they are generating using the wrong object type when calling "setNull(SQLType,ColumnIdx)"

you should probably file a different issue along with a stack trace.