Re: encoding using the odbc driver

Very confused describes me.
As I mentioned it chose SQL_ASCII by default the first time I created the
db.
When I tried to back it up and restore it I got an error because the test I
was restoring it to (created using pgadmin instead of the command prompt on
the linux machine) WAS UNICODE.
Honestly I am completely ignorant on this stuff (sorry but is it is true). I
got the impression that Unicode was better for our needs as my understanding
was it allowed larger chars to be stored.

That being said, I did the next iteration of the database as Unicode and
tried to load it with the MSSQL data (using the odbc driver).

It failed on French strings we had in the database (this was confusing as it
had worked prior). After looking at it I isolated the issue to the encoding
being Unicode (the odbc driver loads the data base when the data base is
SQL_ASCII, but not when it is Unicode; I thought I would want Unicode).

Can you straighten me out here do I not need Unicode (in which case the odbc
works fine as is).

If I should be using Unicode is there something I need to do to get the odbc
driver to work (the .net allowed me to specify Unicode on the connection
string and worked).

I appreciate all the help, and sorry if I am asking stupid questions based
on total ignorance, but I am making some progress.

Joel

"Joel Fradkin" <jfradkin(at)wazagua(dot)com> writes:
> I changed my encoding from SQL_ASCII to UNICODE.
> I believe the Unicode is supposed to support more languages?

I think you're missing the point. The SQL_ASCII setting isn't an
encoding, really; it's a declaration of ignorance. In this setting
the server will just store and regurgitate whatever character strings
you send it. This will work fine, more or less, if all your clients
use exactly the same encoding and you don't care about functions like
upper()/lower().

When you use UNICODE (or any other setting) then the server tries to
enforce that what's stored in the database is actually valid data per
that encoding. It will also provide encoding conversion for clients
who select a different specific client_encoding. But a client that
sets client_encoding = SQL_ASCII defeats the conversion and will see
whatever's stored in the database.

If you flip between SQL_ASCII and other settings, on either end, without
clearly understanding what's happening, you're likely to get very
confused.

regards, tom lane