Обсуждение: Detecting SQL_ASCII databases

8.0 backends report the server_encoding value of the database via
ParameterStatus during startup. I'd like to use this to detect SQL_ASCII
databases and complain loudly.

The simplest approach is: if the server reports server_encoding on V3
startup, and it is SQL_ASCII, close the connecton and throw SQLException.

Perhaps a nicer approach is to do this on V3 connections:

1) if charSet=foo is specified, send a startup packet with
client_encoding = foo, otherwise send client_encoding = UNICODE. Set the
actual connection encoding to match client_encoding.
2) if the server reports server_encoding = SQL_ASCII and charSet was not
specified, close the connection and throw SQLException.

and change the >=7.3 logic in the V2 path to respect charSet if used,
for consistency with the 7.2 and V3 paths.

This lets users who have SQL_ASCII databases continue to use them by
explicitly specifying an encoding to use.

We still have some inconsistency in when you get the "this db is
SQL_ASCII, go away" class of error but I'm not sure if this is worth
fixing. It seems like we'd need an extra roundtrip on connection setup
to check it for 7.4 servers.

Another issue is whether we should allow charSet to be used when
server_encoding != SQL_ASCII. It seems confusing to allow this, but we
can only detect it after we've established a connection and set
everything up. Should we throw an error, change client_encoding back to
UNICODE, or just continue with the specified charSet?

Opinions?

-O

On Sun, 19 Sep 2004, Oliver Jowett wrote:

> 8.0 backends report the server_encoding value of the database via
> ParameterStatus during startup. I'd like to use this to detect SQL_ASCII
> databases and complain loudly.

That would certainly help people who have setup their databases
incorrectly, but it is actually legal to have a setup like this as long as
you only use seven bit ascii characters.  I'm not sure where I stand on
this.  There are probably plenty of databases that are working fine with
the current setup.  For example even using 8 bit chars in a SQL_ASCII
database, if all clients are unicode then the only problem you'll see is
the length check in something like varchar(100) failing.

Kris Jurka

Kris Jurka wrote:
>
> On Sun, 19 Sep 2004, Oliver Jowett wrote:
>
>
>>8.0 backends report the server_encoding value of the database via
>>ParameterStatus during startup. I'd like to use this to detect SQL_ASCII
>>databases and complain loudly.
>
>
> That would certainly help people who have setup their databases
> incorrectly, but it is actually legal to have a setup like this as long as
> you only use seven bit ascii characters.

We could support this case by requiring charSet=SQL_ASCII to be
specified if you have this case. (this would set client_encoding =
SQL_ASCII and interpret data as US-ASCII).

Part of the problem is that SQL_ASCII seems to mean "just store whatever
I'm given directly"; there is no real "7-bit ascii only" encoding?

> I'm not sure where I stand on
> this.  There are probably plenty of databases that are working fine with
> the current setup.  For example even using 8 bit chars in a SQL_ASCII
> database, if all clients are unicode then the only problem you'll see is
> the length check in something like varchar(100) failing.

We could support this with charSet=UNICODE (set client_encoding =
SQL_ASCII and interpret data as UTF-8)

-O

On Thu, 23 Sep 2004, Oliver Jowett wrote:

> >>8.0 backends report the server_encoding value of the database via
> >>ParameterStatus during startup. I'd like to use this to detect SQL_ASCII
> >>databases and complain loudly.
> >

Sounds good to me as long as we can make existing "broken" setups still
work (at least as well as they do now).  I was initially hesitant because
you seemed to want to force people to encode their data correctly
(requiring a dump/reload).  If they just need to set a URL parameter
acknowledging that their setup isn't quite right, that's low impact.

Kris Jurka

One of the Java developers in my group asked me today if there was any
reliable and portable way he could query JDBC to find out what database he
was connected to, Postgres vs Oracle vs TimesTen vs ???

So, I'm curious, are there any SQL standard methods of doing this?  Is the
information_schema any part of the standard, i.e. could we

SELECT character_value FROM information.schema.sql_implementation_info
    WHERE implementation_info_name = 'SERVER NAME';

?

Or do we have to poke around at each of these DBMS servers and figure out
whats unique about them ?

John R Pierce wrote:
> One of the Java developers in my group asked me today if there was any
> reliable and portable way he could query JDBC to find out what database
> he was connected to, Postgres vs Oracle vs TimesTen vs ???

connection.getMetaData().getDatabaseProductName()

There are similar methods to get version and driver info.

-O