Having a odd issue with accent chars in spanish names

[snorkel]

I have a user who is having a issue with accent/tickmark chars in
spanish language names and I can't reproduce the issue.

He says it happens on old databases, and works fine when he creates a new database
with Lightning Admin(my software which uses PGdac)

He is seeing this:

DB text PGLA text
Rodríguez Rodr�guez
Galván Galv�n
Raúl Ra�l
López L�pez
Peña Pe�a
Héctor H�ctor

When I create a new SQL_ASCII or a UTF8 database and create a new table I can import example data no problem.

Anyone have any ideas?

[AlexP]

hello,

Please specify the exact versions of the PostgreSQL server, PgDAC, IDE, encoding of the database, and OS, and we will try to reproduce the problem

[snorkel]

This is the info the user supplied me:
postgresql version: 9.1.3
OS the server is running on: FreeBSD 8.2-RELEASE amd 64 compiled by gcc (GCC) 4.2.1 20070719 [FreeBSD], 64-Bit GENERIC
OS LA is running on: Windows 7 Home Basic Service Pack 1 64 bits. Intel celeron cpu B800 @ 1.50 GHz 1.50 GHz 4.00 GB RAM
Encoding of database: SQL_ASCII
Collation: es_MX.ISO8859-15
Ctype : C
Show client_encoding : UTF8
Show server_encoding: SQL_ASCII

Image of what customer is seeing:



My software is built with the latest production release of PGDac from July I believe.

I have not been able to reproduce it.

[bork]

Hello

The SQL_ASCII setting behaves considerably differently from the other settings. When the server character set is SQL_ASCII, the server interprets byte values 0-127 according to the ASCII standard, while byte values 128-255 are taken as uninterpreted characters. No encoding conversion will be done when the setting is SQL_ASCII. Thus, this setting is not so much a declaration that a specific encoding is in use, as a declaration of ignorance about the encoding. In most cases, if you are working with any non-ASCII data, it is unwise to use the SQL_ASCII setting because PostgreSQL will be unable to help you by converting or validating non-ASCII characters.

You can find more information about SQL_ASCII encoding here: http://www.postgresql.org/docs/9.1/stat ... ibyte.html

If server has SQL_ASCII encoding, then all non-ASCII characters will be stored incorrectly. You should change server encoding from SQL_ASCII to UTF8.

[snorkel]

Ok, I found the problem, the customer sent me a database dump and I found that if
I disable the use unicode in the PGDac connection options it displays the accent chars
properly.

[snorkel]

So what is the deal with the useunicode option?
It appears that even if I choose sql_ascii for the database encoding, and useunicde is true, I can store any kind of languages fine even in the SQL_ASCII.

I was led to believe that the use unicode option should be enabled all the time.

So what is the best practice for this?

[bork]

If you set UseUnicode=True, two situations are possible:
1. The server has the UTF8 encoding. In this case, the all the text data will be sent to client-side "as is" without any conversations.
2. The server has no UTF8 encoding. In this case, the PostgreSQL server will convert the text from the server encoding to the UTF8 encoding and send the result to client-side.

It will work correctly for any server encodings except SQL_ASCII. For SQL_ASCII all characters with codes more than 127 are unprintable and cannot be converted to UTF8.

[snorkel]

ok, so I should leave the use unicode option enabled for all encodings except SQL_ASCII?
Does this sound like the best solution?
I can check what the encoding of the database the user is connecting to and if SQL_ASCII then set useunicode to false.

Thanks,

Snorkel

[bork]

If you are not able to change the server encoding, then the best way is to (as you wrote before) check the server encoding and not use UseUnicode option if server has the SQL_ASCII encoding.