Thread: encoding using the odbc driver

"Joel Fradkin" <jfradkin@wazagua.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

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@wazagua.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

"Joel Fradkin" <jfradkin@wazagua.com> writes:
> 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).

That means that the data you have is not in Unicode, but some other
encoding (and no, I cannot tell what from this information).  You can
load it into the database only by setting client_encoding to tell what
encoding the data is, so that the server can convert it to Unicode
correctly.

BTW, I believe that server_encoding = UNICODE doesn't currently work
well on Windows, due to inadequate support from the operating system.
So that could be contributing to your problems as well, if you're
trying to run the server on Windows.

If you don't know and don't care what encoding you're using, you're
best off to set server_encoding = SQL_ASCII; that is more or less meant
to describe that state of ignorance ;-)

            regards, tom lane

IT is coming from MSSQL server going to Redhat AS.
I looked it up and see (as one other lister mentioned and I was a bit
ignorant) it is SQL_LATIN_General_CP1_CI_AS.

I would like for the lower function to work correctly.
I will try making a database Latin 1 and see if I get it to both load and
have a functioning lower sql statement.

Thanks again for all the insight, whish I understood it all better, but I am
more of a programmer then systems guy (I wear too many hats).


"Joel Fradkin" <jfradkin@wazagua.com> writes:
> 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).

That means that the data you have is not in Unicode, but some other
encoding (and no, I cannot tell what from this information).  You can
load it into the database only by setting client_encoding to tell what
encoding the data is, so that the server can convert it to Unicode
correctly.

BTW, I believe that server_encoding = UNICODE doesn't currently work
well on Windows, due to inadequate support from the operating system.
So that could be contributing to your problems as well, if you're
trying to run the server on Windows.

If you don't know and don't care what encoding you're using, you're
best off to set server_encoding = SQL_ASCII; that is more or less meant
to describe that state of ignorance ;-)

            regards, tom lane