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

Re: Can PostgreSQL do data type automated casting in

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Oliver Jowett <oliver(at)opencloud(dot)com>
Cc: Mark Lewis <mark(dot)lewis(at)mir3(dot)com>, Kris Jurka <books(at)ejurka(dot)com>, Tjioe Ai Xin <xinxincute(at)gmail(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Can PostgreSQL do data type automated casting in
Date: 2005-11-24 13:25:22
Message-ID: (view raw or whole thread)
Lists: pgsql-jdbc

Looking at the test case is this a realistic situation ? Would anyone  
really want to change the types of a parameter of a statement ?

On 24-Nov-05, at 12:00 AM, Oliver Jowett wrote:

> Dave Cramer wrote:
>> The easier way to deal with this has already been discussed.  
>> Simply  bind String to the Oid.Unknown type, and let the server  
>> deal with it.
>> How about we make this a configuration parameter.
> I've implemented this and it seems ok with one exception. This is  
> from ServerPreparedStmtTest:
>     public void testTypeChange() throws Exception {
>         PreparedStatement pstmt = con.prepareStatement("SELECT ?");
>         ((PGStatement)pstmt).setUseServerPrepare(true);
>         // Prepare with int parameter.
>         pstmt.setInt(1, 1);
>         ResultSet rs = pstmt.executeQuery();
>         assertTrue(;
>         assertEquals(1, rs.getInt(1));
>         assertTrue(!;
>         // Change to text parameter, check it still works.
>         pstmt.setString(1, "test string");
>         rs = pstmt.executeQuery();
>         assertTrue(;
>         assertEquals("test string", rs.getString(1));
>         assertTrue(!;
>     }
> With String bound to OID 0 this fails with:
>  ERROR: invalid input syntax for integer: "test string"
> What is happening is that the use of setInt() prepares a server- 
> side statement with the parameter typed as integer. When setString 
> () is later used, it feeds oid 0 down into the query executor which  
> decides to reuse the existing statement since it is "compatible  
> enough" as the parameter which was previously integer now has an  
> unspecified type. Then the server tries to parse the string as an  
> integer and breaks.
> IIRC this was originally done to avoid re-preparing the statement  
> when setNull(x,Types.OTHER) was done (or equivalently, setObject 
> (x,null), which just calls setNull) -- which can pass oid 0 to an  
> existing query with resolved types.
> I guess that we should tighten the checks in the query executor so  
> that it will only consider types "compatible enough" if the new  
> value is both of unspecified type *and* null?
> (I am also renaming Oid.INVALID to Oid.UNSPECIFIED in the driver to  
> reduce confusion -- InvalidOid in the backend is indeed 0 but  
> "invalid" is not the protocol-level meaning for oid 0 here, and  
> "unknown" is an actual pseudotype that has a non-zero oid).
> -O
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?

In response to


pgsql-jdbc by date

Next:From: Roman ChervotkinDate: 2005-11-24 13:58:11
Subject: org.postgresql.util.PSQLException Column name wasn't found in the ResultSet
Previous:From: Oliver JowettDate: 2005-11-24 11:25:23
Subject: Re: Room to optimize updates through ResultSet

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