Skip site navigation (1) Skip section navigation (2)

Re: Batch INSERT failing with error 22P02

From: Eric Faulhaber <ecf(at)goldencode(dot)com>
To: Kris Jurka <books(at)ejurka(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Batch INSERT failing with error 22P02
Date: 2007-09-20 19:26:58
Message-ID: 46F2C982.2070209@goldencode.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Eric Faulhaber wrote:
> 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.

How relevant is the following change to this mystery?  This is an 
excerpt from a diff of AbstractJdbc2Statement between the versions we've 
been discussing, specifically, the internals of the setDate(int, Date, 
Calendar) method:

--- 
postgresql-jdbc-8.1-408.src/org/postgresql/jdbc2/AbstractJdbc2Statement.java        
2006-11-05 00:44:44.000000000 -0500
+++ 
postgresql-jdbc-8.2-506.src/org/postgresql/jdbc2/AbstractJdbc2Statement.java        
2007-07-27 05:01:53.000000000 -0400

...

@@ -2897,7 +2919,7 @@
        if (cal != null)
            cal = (Calendar)cal.clone();

-        // We must use INVALID here, or inserting a Date-with-timezone 
into a
+        // We must use UNSPECIFIED here, or inserting a 
Date-with-timezone into a
        // timestamptz field does an unexpected rotation by the server's 
TimeZone:
        //
        // We want to interpret 2005/01/01 with calendar +0100 as
@@ -2916,7 +2938,7 @@
        //  2005-01-01 00:00:00+03
        // (1 row)

-        bindString(i, connection.getTimestampUtils().toString(cal, d), 
Oid.INVALID);
+        bindString(i, connection.getTimestampUtils().toString(cal, d), 
Oid.UNSPECIFIED);
    }

    public void setTime(int i, Time t, java.util.Calendar cal) throws 
SQLException
...

I mean, it obviously explains where the unspecified type is coming from, 
but how likely is that to trigger the FE/BE type mismatch we're seeing 
many records later?

Thanks,
Eric Faulhaber


In response to

Responses

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2007-09-20 19:38:44
Subject: Re: Batch INSERT failing with error 22P02
Previous:From: Eric FaulhaberDate: 2007-09-20 18:49:24
Subject: Re: Batch INSERT failing with error 22P02

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group