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: 73985A37-FA66-4E31-95C1-045F5AE31726@fastcrypt.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Interesting.

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

Dave
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(rs.next());
>         assertEquals(1, rs.getInt(1));
>         assertTrue(!rs.next());
>
>         // Change to text parameter, check it still works.
>         pstmt.setString(1, "test string");
>         rs = pstmt.executeQuery();
>         assertTrue(rs.next());
>         assertEquals("test string", rs.getString(1));
>         assertTrue(!rs.next());
>     }
>
> 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?
>
>               http://archives.postgresql.org
>


In response to

Responses

pgsql-jdbc by date

Next:From: Roman ChervotkinDate: 2005-11-24 13:58:11
Subject: org.postgresql.util.PSQLException Column name candidates.id 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-2014 The PostgreSQL Global Development Group