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

[Fwd: Re: [Pljava-dev] char with trailing space, PreparedStatement.setObject & SetString]

From: Thomas Hallgren <thomas(at)tada(dot)se>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: [Fwd: Re: [Pljava-dev] char with trailing space, PreparedStatement.setObject & SetString]
Date: 2006-06-27 16:26:14
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
There's an inconsistency between the handling of trailing whitespace in 
query parameters in the client jdbc driver compared to the PL/Java SPI 
based driver. According to Jean-Pierre, the former apparently trims the 
trailing spaces before passing the query (see below). What is the 
correct behavior?

Thomas Hallgren

-------- Original Message --------
Subject: 	Re: [Pljava-dev] char with trailing space, 
PreparedStatement.setObject & SetString
Date: 	Tue, 27 Jun 2006 12:07:19 -0400
From: 	JEAN-PIERRE PELLETIER <pelletier_32(at)sympatico(dot)ca>
To: 	thomas(at)tada(dot)se
CC: 	pljava-dev(at)gborg(dot)postgresql(dot)org

Hi Thomas,

There are very few char columns on my system, I can easily live with 
explicit trim
in my application code, I only wanted to let you guys know.

I am not sure which of the two JDBC implementations is right.
psql and pgadmin would both handle char as expected.
As for JDBC, you might want to know how other dbms are handling this.

Thanks for your reply.
Jean-Pierre Pelletier

>From: Thomas Hallgren <thomas(at)tada(dot)se>
>To: JEAN-PIERRE PELLETIER <pelletier_32(at)sympatico(dot)ca>
>CC: pljava-dev(at)gborg(dot)postgresql(dot)org
>Subject: Re: [Pljava-dev] char with trailing 
>space,	PreparedStatement.setObject & SetString
>Date: Tue, 27 Jun 2006 17:47:24 +0200
>Hi Jean-Pierre,
>I'm not sure this is incorrect behavior. There's nothing in the spec that 
>indicates that String values should be trimmed by setString and setObject. 
>On the contrary. Some datatypes (the CHAR in particular) are sensitive to 
>whitespace according to the SQL standard. Perhaps the client jdbc driver is 
>doing something wrong here?
>Thomas Hallgren
>>Trailing space are not handled properly by setObject & setString.
>>PreparedStatement pstmt = connection.prepareStatement(
>>    "select * from mytable where mycharcolumn = ?");
>>String myString = "abc ";
>>pstmt.setObject(1, myString); // or setObject(1, myString, Types.CHAR) or 
>>setString(1, myString)
>>No rows are returned, but using trim works fine as in:
>>pstmt.setObject(1, myString.trim());
>>My environment is Pl/Java 1.3, Sun JDK 1.5.07,  PostgreSQL 8.1.4, Windows 
>>XP SP2
>>With PostgreSQL own (non pl/java) jdbc driver, setObject on char works 
>>fine without the trim.
>>Jean-Pierre Pelletier
>>Pljava-dev mailing list


pgsql-hackers by date

Next:From: Tom LaneDate: 2006-06-27 16:28:35
Subject: Re: SO_SNDBUF size is small on win32?
Previous:From: Bruce MomjianDate: 2006-06-27 16:23:29
Subject: Re: vacuum, performance, and MVCC

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