Thread: BUG #5099: When MetaData is acquired, it becomes an SQL error.

The following bug has been logged online:

Bug reference:      5099
Logged by:          konishi
Email address:      a.konisi@gmail.com
PostgreSQL version: 8.4.1
Operating system:   FreeBSD 6.0
Description:        When MetaData is acquired, it becomes an SQL error.
Details:

In sample source and sample db
when used postgresql-8.4-701.jdbc3.jar is error
when used postgresql-8.3-603.jdbc3.jar is no error


The error disappears when "prepared.getParameterMetaData()" row delete.


sample source:

String url = "jdbc:postgresql://XXX.XXX.XXX.XXX:5432/test";
Connection con = DriverManager.getConnection(url, "postgres", "test");
try{
    String sql = "insert into test(filename,upddate) values(?,?)";
    PreparedStatement prepared = con.prepareStatement(sql);
    System.out.println("ParameterMetaData[" +
prepared.getParameterMetaData() + "]");
    prepared.setString(1, "0");
    prepared.setTimestamp(2, new
Timestamp(Calendar.getInstance().getTimeInMillis()));
    prepared.executeUpdate();
}catch(Exception e){
    System.out.println(e.getMessage());
}

sample table:
test=# \d+ test
                               Table "public.test"
  Column  |            Type             |   Modifiers   | Storage  |
Description
----------+-----------------------------+---------------+----------+--------
-----
 filename | text                        |               | extended |
 upddate  | timestamp without time zone | default now() | plain    |
Has OIDs: no

error message:
java.lang.IllegalArgumentException: Can't change resolved type for param: 1
from 1043 to 25
    at
org.postgresql.core.v3.SimpleParameterList.setResolvedType(SimpleParameterLi
st.java:230)
    at
org.postgresql.core.v3.QueryExecutorImpl.sendOneQuery(QueryExecutorImpl.java
:1488)
    at
org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:10
62)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)

    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
ava:479)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2St
atement.java:367)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2State
ment.java:321)
    at JdbcTest.testJdbctest(JdbcTest.java:42)
...

On Wed, 7 Oct 2009, konishi wrote:

> Bug reference:      5099
> PostgreSQL version: 8.4.1
> Description:        When MetaData is acquired, it becomes an SQL error.
> Details:
>
> In sample source and sample db
> when used postgresql-8.4-701.jdbc3.jar is error
> when used postgresql-8.3-603.jdbc3.jar is no error
>
>
> The error disappears when "prepared.getParameterMetaData()" row delete.
>
> String url = "jdbc:postgresql://XXX.XXX.XXX.XXX:5432/test";
> Connection con = DriverManager.getConnection(url, "postgres", "test");
> try{
>    String sql = "insert into test(filename,upddate) values(?,?)";
>    PreparedStatement prepared = con.prepareStatement(sql);
>    System.out.println("ParameterMetaData[" +
> prepared.getParameterMetaData() + "]");
>    prepared.setString(1, "0");
>    prepared.setTimestamp(2, new
> Timestamp(Calendar.getInstance().getTimeInMillis()));
>    prepared.executeUpdate();
> }catch(Exception e){
>    System.out.println(e.getMessage());
> }
>
> error message:
> java.lang.IllegalArgumentException: Can't change resolved type for param: 1
> from 1043 to 25
>     at
> org.postgresql.core.v3.SimpleParameterList.setResolvedType(SimpleParameterLi
> st.java:230)
>     at


I have applied a fix to CVS for this problem and it will be included in
the next release.

For the 8.4 release an optimization was added to avoid re-describing a
statement if we already had the type information available by copying the
resolved type information from the query to the provided parameters.  Its
goal was just to overwrite parameters without a type (unknown), but it was
actually overwriting all types which could change the query's desired
behavior.  In this case a safeguard to prevent that change was throwing
the exception noted.

Kris Jurka

Hi Kris-san.

Great, thanks!!
You have spent very much time for this problem.
I think very many Postgres users was helped by it.

Thanks again:-)

Regards,
Hiroshi Saito

----- Original Message -----
From: "Kris Jurka" <books@ejurka.com>


>
>
> On Wed, 7 Oct 2009, konishi wrote:
>
>> Bug reference:      5099
>> PostgreSQL version: 8.4.1
>> Description:        When MetaData is acquired, it becomes an SQL error.
>> Details:
>>
>> In sample source and sample db
>> when used postgresql-8.4-701.jdbc3.jar is error
>> when used postgresql-8.3-603.jdbc3.jar is no error
>>
>>
>> The error disappears when "prepared.getParameterMetaData()" row delete.
>>
>> String url = "jdbc:postgresql://XXX.XXX.XXX.XXX:5432/test";
>> Connection con = DriverManager.getConnection(url, "postgres", "test");
>> try{
>>    String sql = "insert into test(filename,upddate) values(?,?)";
>>    PreparedStatement prepared = con.prepareStatement(sql);
>>    System.out.println("ParameterMetaData[" +
>> prepared.getParameterMetaData() + "]");
>>    prepared.setString(1, "0");
>>    prepared.setTimestamp(2, new
>> Timestamp(Calendar.getInstance().getTimeInMillis()));
>>    prepared.executeUpdate();
>> }catch(Exception e){
>>    System.out.println(e.getMessage());
>> }
>>
>> error message:
>> java.lang.IllegalArgumentException: Can't change resolved type for param: 1
>> from 1043 to 25
>> at
>> org.postgresql.core.v3.SimpleParameterList.setResolvedType(SimpleParameterLi
>> st.java:230)
>> at
>
>
> I have applied a fix to CVS for this problem and it will be included in
> the next release.
>
> For the 8.4 release an optimization was added to avoid re-describing a
> statement if we already had the type information available by copying the
> resolved type information from the query to the provided parameters.  Its
> goal was just to overwrite parameters without a type (unknown), but it was
> actually overwriting all types which could change the query's desired
> behavior.  In this case a safeguard to prevent that change was throwing
> the exception noted.
>
> Kris Jurka