Re: Can PostgreSQL do data type automated casting in

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
>