Re: Batch INSERT failing with error 22P02

[Eric Faulhaber <ecf(at)goldencode(dot)com>]

Kris Jurka wrote:
> 2) Where's the unspecified type coming from?  I've put in a hack to 
> the driver to throw an exception when the first unspecified type is 
> set which should hopefully provide enough information to track it back 
> to your application.  Unspecified types are perfectly legal, so this 
> isn't a real problem, but it may be informational.

I turned on some additional logging in our code to see what data is 
being bound to each parameter in our UserType implementation.  It seems 
the unspecified type is coming from a Date object bound with 
PreparedStatement.setDate(int, java.sql.Date).  At least, this is the 
first parameter that triggers the test.

...
Sep 20, 2007 2:30:04 PM 
com.goldencode.p2j.persist.AbstractWrapperUserType nullSafeSet
FINEST: binding '11/30/04' to parameter: 9
Sep 20, 2007 2:30:04 PM com.goldencode.p2j.schema.ImportWorker$Library 
importTable
SEVERE: Error processing import data from 
/home/ecf/dump/export/gso/turnarnd.d;  100 of ? record(s) successfully 
processed;  0 record(s) uncommitted due to this error;  0 record(s) dropped
java.lang.IllegalArgumentException: Unspecified type comes from here, 
parameter: 9
       at 
org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:68)
       at 
org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:120)
       at 
org.postgresql.jdbc2.AbstractJdbc2Statement.bindString(AbstractJdbc2Statement.java:2119)
       at 
org.postgresql.jdbc2.AbstractJdbc2Statement.setDate(AbstractJdbc2Statement.java:3015)
       at 
org.postgresql.jdbc2.AbstractJdbc2Statement.setDate(AbstractJdbc2Statement.java:1296)
       at 
com.goldencode.p2j.persist.DateUserType.bindNonNull(DateUserType.java:159)
       at 
com.goldencode.p2j.persist.AbstractWrapperUserType.nullSafeSet(AbstractWrapperUserType.java:263)
       at 
com.goldencode.p2j.persist.DateUserType.nullSafeSet(DateUserType.java:39)
       at org.hibernate.type.CustomType.nullSafeSet(CustomType.java:141)
       at 
org.hibernate.persister.entity.BasicEntityPersister.dehydrate(BasicEntityPersister.java:1617)
       at 
org.hibernate.persister.entity.BasicEntityPersister.dehydrate(BasicEntityPersister.java:1594)
       at 
org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1850)
       at 
org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:2200)
       at 
org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:46)
       at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
       at 
org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:223)
       at 
org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:136)
       at 
org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:274)
       at 
org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
       at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:730)
       at 
com.goldencode.p2j.schema.ImportWorker$Library.importTable(ImportWorker.java:710)
       at 
com.goldencode.p2j.schema.ImportWorker$Library$1.run(ImportWorker.java:1020)
       at java.lang.Thread.run(Thread.java:619)
...

A number of previous inserts contained "unknown" dates.  These were 
bound with PreparedStatement.setNull(index, Types.DATE) before the test 
was tripped by the PreparedStatement.setDate(index, date) in the stack 
trace above.

I will run with the enhanced logging patch and upload results shortly.

Thanks,
Eric Faulhaber