Re: Inconsistent casting with literal vs parameter

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