Re: Inconsistent casting with literal vs parameter - Mailing list pgsql-jdbc

I wrote the following code

        Properties props = new Properties();
        props.put("user", "postgres");
        props.put("password", "****");
        props.put("stringtype", "unspecified");
        Connection conn =
DriverManager.getConnection("jdbc:postgresql://localhost/cpas", props);
        DatabaseMetaData md = conn.getMetaData();
        System.out.println("Server URL:               " + md.getURL());
        System.out.println("Database Product Name:    " +
md.getDatabaseProductName());
        System.out.println("Database Product Version: " +
md.getDatabaseProductVersion());
        System.out.println("JDBC Driver Name:         " +
md.getDriverName());
        System.out.println("JDBC Driver Version:      " +
md.getDriverVersion());

Unfortunately, it gives the same result.

    Server URL:               jdbc:postgresql://localhost/cpas
    Database Product Name:    PostgreSQL
    Database Product Version: 8.1.0
    JDBC Driver Name:         PostgreSQL Native Driver
    JDBC Driver Version:      PostgreSQL 8.1 JDBC3 with SSL (build 404)

So, does this mean this parameter is not properly respected in the driver?

Matt

Matthew Bellew wrote:

> Fantastic, thanks for the pointer this parameter.  I would argue that
> the expected behavior would be that these two cases are semantically
> equivalent.  The fact that this is configurable is great, but my gripe
> would be that the default setting results in 'wrong' answers for a
> very unobvious reason.
>
> Again thanks,
> Matt
>
> Kris Jurka wrote:
>
>>
>>
>> On Tue, 7 Feb 2006, Matthew Bellew wrote:
>>
>>> I have an example here where replacing a string literal with a
>>> string parameter in the same query yields different results.  See
>>> Java code below, and note that this example works with int as well
>>> as float.  In one case the comparisions are done as numbers in the
>>> other they are done as strings. The explanation I received from Tom
>>> Lane is that in stmtA with (x < '100'), '100' is an untyped literal,
>>> in stmtB and stmtC (x < ?), the parameter is treated as typed, and
>>> thus the coercion occurs differently.   I'm afraid someone is going
>>> to answer "it works this way because..."  I'm more interested to
>>> know if anyone else thinks it is a problem that these two statements
>>> return different results. I seems to me that these queries
>>> reasonably have to be considered the same, and should return the
>>> same answer..
>>
>>
>>
>> I don't think these queries are the same, consider
>> SELECT '12' < '2', 12 < 2;
>>
>> In the first case the server considers it text and the second it
>> considers it numeric.  Now when considering the mixed case the server
>> has to decide what to do.  When you say 12 < '2' you aren't
>> conclusively stating what type '2' is and it gets coerced to numeric,
>> but when you say setString you are explicitly telling it that it is a
>> text data type and a text comparison should be done.  When you say
>> setObject with a String object you also say that it is text data.  If
>> you want it interpreted as numeric data use setFloat or similar.
>>
>> The 8.2 driver has an option to allow setString data to passed to the
>> server without a type and you will get the result you desire.  See
>> the stringtype parameter here:
>>
>> http://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters
>>
>>
>> Kris Jurka
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend