Re: Is this error correct/possible?

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