groovy

Sql throws exceptions that says wrong datatype in 1.5.4

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Major Major
  • Resolution: Duplicate
  • Affects Version/s: 1.5.4
  • Fix Version/s: None
  • Component/s: SQL processing
  • Labels:
    None
  • Number of attachments :
    0

Description

given the following table definition

create table tiger_1 ( TLID numeric not null,
SIDE1 numeric ,
SOURCE varchar(1) ,
FEDIRP varchar(2) ,
FENAME varchar(30) ,
FETYPE varchar(4) ,
FEDIRS varchar(2) ,
CFCC varchar(3) not null,
FRADDL varchar(11) ,
TOADDL varchar(11) ,
FRADDR varchar(11) ,
TOADDR varchar(11) ,
FRIADDL varchar(1) ,
TOIADDL varchar(1) ,
FRIADDR varchar(1) ,
TOIADDR varchar(1) ,
ZIPL varchar(5) ,
ZIPR varchar(5) ,
AIANHHFPL varchar(5) ,
AIANHHFPR varchar(5) ,
AIHHTLIL varchar(1) ,
AIHHTLIR varchar(1) ,
CENSUS1 varchar(1) ,
CENSUS2 varchar(1) ,
STATEL varchar(2) ,
STATER varchar(2) ,
COUNTYL varchar(3) ,
COUNTYR varchar(3) ,
COUSUBL varchar(5) ,
COUSUBR varchar(5) ,
SUBMCDL varchar(5) ,
SUBMCDR varchar(5) ,
PLACEL varchar(5) ,
PLACER varchar(5) ,
TRACTL varchar(6) ,
TRACTR varchar(6) ,
BLOCKL varchar(4) ,
BLOCKR varchar(4) ,
FRLONG numeric not null,
FRLAT numeric not null,
TOLONG numeric not null,
TOLAT numeric not null);

and the following insert statement:

insert into tiger_1 (TLID,SIDE1,SOURCE,FEDIRP,FENAME,FETYPE,FEDIRS,CFCC,FRADDL,TOADDL,FRADDR,TOADDR,FRIADDL,TOIADDL,FRIADDR,TOIADDR,ZIPL,ZIPR,AIANHHFPL,AIANHHFPR,AIHHTLIL,AIHHTLIR,CENSUS1,CENSUS2,STATEL,STATER,COUNTYL,COUNTYR,COUSUBL,COUSUBR,SUBMCDL,SUBMCDR,PLACEL,PLACER,TRACTL,TRACTR,BLOCKL,BLOCKR,FRLONG,FRLAT,TOLONG,TOLAT) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);

the Sql package failed to insert the following list

[60874148, null, "N", null, "Charmed Circle", "Dr", null, "A41", "2", "98", "1", "99", "0", "0", "0", "0", "17325", "17325", null, null, null, null, null, null, "42", "42", "001", "001", "74680", "74680", null, null, null, null, "030600", "030600", "2025", "2025", -77211273, 39859742, -77209462, 39857529]

If i switched back to 1.5.1, everything works as expected

the exceptions were 

 
Exception in thread "main" java.sql.SQLException: Wrong data type: java.lang.NumberFormatException
	at org.hsqldb.jdbc.Util.throwError(Unknown Source)
	at org.hsqldb.jdbc.jdbcPreparedStatement.setParameter(Unknown Source)
	at org.hsqldb.jdbc.jdbcPreparedStatement.setObject(Unknown Source)
	at groovy.sql.Sql.setObject(Sql.java:1423)
	at groovy.sql.Sql.setParameters(Sql.java:1391)
	at groovy.sql.Sql.execute(Sql.java:833)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:86)
	at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:226)
	at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:899)
	at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:740)
	at org.codehaus.groovy.runtime.InvokerHelper.invokePojoMethod(InvokerHelper.java:765)
	at org.codehaus.groovy.runtime.InvokerHelper.invokeMethod(InvokerHelper.java:753)
	at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.invokeMethodN(ScriptBytecodeAdapter.java:167)
	at hello$_processFile_closure5.doCall(hello.groovy:39)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:86)
	at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:226)
	at org.codehaus.groovy.runtime.metaclass.ClosureMetaClass.invokeMethod(ClosureMetaClass.java:250)
	at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:740)
	at groovy.lang.Closure.call(Closure.java:292)
	at groovy.lang.Closure.call(Closure.java:305)
	at org.codehaus.groovy.runtime.DefaultGroovyMethods.eachLine(DefaultGroovyMethods.java:6985)
	at org.codehaus.groovy.runtime.DefaultGroovyMethods.eachLine(DefaultGroovyMethods.java:6960)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.codehaus.groovy.runtime.metaclass.ReflectionMetaMethod.invoke(ReflectionMetaMethod.java:51)
	at org.codehaus.groovy.runtime.metaclass.NewInstanceMetaMethod.invoke(NewInstanceMetaMethod.java:54)
	at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:226)
	at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:899)
	at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:740)
	at org.codehaus.groovy.runtime.InvokerHelper.invokePojoMethod(InvokerHelper.java:765)
	at org.codehaus.groovy.runtime.InvokerHelper.invokeMethod(InvokerHelper.java:753)
	at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.invokeMethodN(ScriptBytecodeAdapter.java:167)
	at hello.processFile(hello.groovy:26)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:86)
	at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:226)
	at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:899)
	at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.invokeMethodOnCurrentN(ScriptBytecodeAdapter.java:77)
	at hello.run(hello.groovy:52)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:86)
	at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:226)
	at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:899)
	at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:740)
	at org.codehaus.groovy.runtime.InvokerHelper.invokePogoMethod(InvokerHelper.java:777)
	at org.codehaus.groovy.runtime.InvokerHelper.invokeMethod(InvokerHelper.java:757)
	at org.codehaus.groovy.runtime.InvokerHelper.runScript(InvokerHelper.java:402)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:86)
	at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:226)
	at groovy.lang.MetaClassImpl.invokeStaticMethod(MetaClassImpl.java:1094)
	at org.codehaus.groovy.runtime.InvokerHelper.invokeMethod(InvokerHelper.java:748)
	at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.invokeMethodN(ScriptBytecodeAdapter.java:167)

sorry i didnt download the source code and trace thru to find out exactly what happened.
the database/driver used is
<dependency>
<groupId>hsqldb</groupId>
<artifactId>hsqldb</artifactId>
<version>1.8.0.7</version>
</dependency>

to recreate, try the following script

 
import groovy.sql.Sql
def db = Sql.newInstance("jdbc:hsqldb:file:testdb;shutdown=true", 
        "sa", "", 'org.hsqldb.jdbcDriver')

db.execute '''
create table tiger_1 ( TLID  numeric  not null,
SIDE1  numeric  ,
SOURCE  varchar(1)  ,
FEDIRP  varchar(2)  ,
FENAME  varchar(30)  ,
FETYPE  varchar(4)  ,
FEDIRS  varchar(2)  ,
CFCC  varchar(3)  not null,
FRADDL  varchar(11)  ,
TOADDL  varchar(11)  ,
FRADDR  varchar(11)  ,
TOADDR  varchar(11)  ,
FRIADDL  varchar(1)  ,
TOIADDL  varchar(1)  ,
FRIADDR  varchar(1)  ,
TOIADDR  varchar(1)  ,
ZIPL  varchar(5)  ,
ZIPR  varchar(5)  ,
AIANHHFPL  varchar(5)  ,
AIANHHFPR  varchar(5)  ,
AIHHTLIL  varchar(1)  ,
AIHHTLIR  varchar(1)  ,
CENSUS1  varchar(1)  ,
CENSUS2  varchar(1)  ,
STATEL  varchar(2)  ,
STATER  varchar(2)  ,
COUNTYL  varchar(3)  ,
COUNTYR  varchar(3)  ,
COUSUBL  varchar(5)  ,
COUSUBR  varchar(5)  ,
SUBMCDL  varchar(5)  ,
SUBMCDR  varchar(5)  ,
PLACEL  varchar(5)  ,
PLACER  varchar(5)  ,
TRACTL  varchar(6)  ,
TRACTR  varchar(6)  ,
BLOCKL  varchar(4)  ,
BLOCKR  varchar(4)  ,
FRLONG  numeric  not null,
FRLAT  numeric  not null,
TOLONG  numeric  not null,
TOLAT  numeric  not null);
'''

def insert =  '''
insert into tiger_1 (TLID,SIDE1,SOURCE,FEDIRP,FENAME,FETYPE,FEDIRS,CFCC,FRADDL,TOADDL,FRADDR,TOADDR,FRIADDL,TOIADDL,FRIADDR,TOIADDR,ZIPL,ZIPR,AIANHHFPL,AIANHHFPR,AIHHTLIL,AIHHTLIR,CENSUS1,CENSUS2,STATEL,STATER,COUNTYL,COUNTYR,COUSUBL,COUSUBR,SUBMCDL,SUBMCDR,PLACEL,PLACER,TRACTL,TRACTR,BLOCKL,BLOCKR,FRLONG,FRLAT,TOLONG,TOLAT) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);
'''

//blows up in 1.5.4, but not 1.5.1
db.execute(insert,
[60874148, null, "N", null, "Charmed Circle", "Dr", null, "A41", "2", "98", "1", "99", "0", "0", "0", "0", "17325", "17325", null, null, null, null, null, null, "42", "42", "001", "001", "74680", "74680", null, null, null, null, "030600", "030600", "2025", "2025", -77211273, 39859742, -77209462, 39857529])

db.close()

Activity

Hide
Permalink
Paul King added a comment -

Thanks for the bug submission and additional example. This is already fixed in 1.5.5 and 1.6+.

Show
Paul King added a comment - Thanks for the bug submission and additional example. This is already fixed in 1.5.5 and 1.6+.

People

  • Assignee:
    Paul King
    Reporter:
    Jay Liang
Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved: