Re: Very strange Error in Updates

Dario V. Fassi wrote:
>
>
> Oliver Jowett wrote:
>
>> Dario V. Fassi wrote:
>>
>>> When the parameter fill the full-length of  receiving field and has
>>> any non common character, the update throw a exception like:
>>>
>>> java.sql.SQLException: ERROR:  value too long for type character
>>> varying(30)
>>>    at
>>> org.postgresql.core.v2.QueryExecutorImpl.receiveErrorMessage(QueryExecutorImpl.java:420)
>>
>>
>>
>>
>>
>> What server version (I'm guessing <= 7.3 since the driver is using the
>> V2 protocol) and database encoding are you using?
>>
>> -O
>
>
> Server 7.3.4  for W2K and Linux too.
> Encoding SQL_ASCII  in both cases.

You can only put 7-bit characters into a SQL_ASCII database.

The JDBC driver always speaks UNICODE when it can, since that matches
Java's internal string representation. I suspect that what's happening is:

0) the driver sets client_encoding = UNICODE during connection setup
1) the driver encodes the parameter as UNICODE (== UTF8); for characters
above 127 this encoding will result in more than one byte per character.
2) the server converts from client_encoding UNICODE to database encoding
SQL_ASCII; for characters that are invalid in SQL_ASCII (>127) it does
some arbitary conversion, probably just copying the illegal values
unchanged.
3) you end up with extra characters in the resulting value which exceeds
the varchar's size.

The solution is to use a database encoding that matches your data.

-O