On Thu, 25 Aug 2005, Kevin Grittner wrote:
> My client has a standard that only a subset of the 7 bit ASCII character
> set is to be allowed in character columns in their database (decimal 32
> to 126 in most columns, decimal 10 also allowed in some). They would
> prefer to see exceptions from the JDBC driver on attempts to insert or
> retrieve any character outside the ASCII range than to have it silently
> written or returned.
The JDBC driver will throw an Exception upon reading data that it
receives that is not valid UTF-8 data. This will not detect all problems
because it always sends valid UTF-8 data to the server and expects it to
convert it the server encoding or complain. This means if you've got
a non-ascii character the JDBC driver will send it to the database and
read it from the database as UTF-8 and will not complain. Problems
arise when:
1) Another client adds data of another encoding to the database. Then
the JDBC driver tries to read it and finds it isn't UTF-8 and bails
out.
2) Sending non ascii data in as UTF-8 can convert one character into
multiple bytes. In a real encoding the server realizes this and converts
the multiple bytes back into one character for checks like VARCHAR(N).
SQL_ASCII stores the multiple bytes as multiple characters and this check
can fail when you don't expect it to.
> In this particular case, do you see a problem with using the SQL_ASCII
> encoding?
>
The question is, "why would you want to?" Why not select a real encoding
that is a superset of ascii, like say LATIN1. It provides a much more
reasonable fallback behavior if you ever do add non-ascii data.
Kris Jurka