Re: SQL_ASCII support (or lack thereof)

EDB team; I forget which one of you was going to look into the existing
ticket about issues with SQL_ASCII handling, but here's some more
information that may help. Please take a look ASAP.

Thanks.

On Thu, May 17, 2018 at 5:53 PM, richard coleman <
rcoleman(dot)ascentgl(at)gmail(dot)com> wrote:

> Dave,
>
> Thanks for getting back to me on this issue. If this is the case then it
> would seem that using python for pgAdmin4 development was a poor choice
> considering the fact that PostgreSQL 10 supports more than two dozen types
> of encoding, only one of which is UTF8.
>
> In my case I have a SQL_ASCII database that backs a windows desktop
> application (among other things). The users have the ability to copy/paste
> into various text fields (say from email or Microsoft Word). In the
> immediate case pgAdmin4 is apparently complaining about 0xc9 (É) and 0x4f
> (O). The *solution* if I want to use pgAdmin4 is to find and
> convert/remove the characters that pgAdmin4 is complaining about. As I had
> mentioned previously, pgAdmin3 doesn't have this issue (unfortunately it
> doesn't fully support postgresql 10) neither does the .Net application that
> makes use of this postgresql database. As this is a production database,
> it isn't practical to convert the database to UTF8, and then rewrite the
> applications (there are several) to scrub user input to limit it to only
> UTF8 characters. Ideally pgAdmin4 should support all of the encoding
> schemes that postgresql 10 does.
>
> Here's the DDL for my database:
>
> CREATE DATABASE tms_production
>> WITH
>> OWNER = local_user
>> ENCODING = 'SQL_ASCII'
>> LC_COLLATE = 'English_United States.1252'
>> LC_CTYPE = 'English_United States.1252'
>> TABLESPACE = pg_default
>> CONNECTION LIMIT = -1;
>> ALTER DATABASE tms_production
>> SET default_transaction_read_only TO off;
>> ALTER DATABASE tms_production
>> SET client_encoding TO SQL_ASCII;
>> ALTER DATABASE tms_production
>> SET standard_conforming_strings TO off;
>
>
> To replicate the issue;
>
> - create a table with a text field.
> - paste some valid ASCII.1252, but invalid UTF8 data into it.
> - try to do something (ex:SELECT * FROM <your_table> ;) in pgAdmin4
> with that table
> - watch pgAdmin4 throw errors.
>
> rik.
>
> On Thu, May 17, 2018 at 10:17 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>
>>
>>
>> On Thu, May 17, 2018 at 3:06 PM, richard coleman <
>> rcoleman(dot)ascentgl(at)gmail(dot)com> wrote:
>>
>>> Why is pgAdmin 4 so hostile to SQL_ASCII databases?
>>>
>>> We have several production databases dating back to 9.1 that are
>>> SQL_ASCII encoding but in pgAdmin4 I am constantly having to *clean up*
>>> non *UTF8* data. The same data works just fine in the pgAdmin3 series.
>>>
>>> Running the same query in psql yields the expected results, but in this
>>> case I get:
>>>
>>> "ERROR: invalid byte sequence for encoding "UTF8": 0xc9 0x4f
>>> SQL state: 22021"
>>>
>>> in pgAdmin4.
>>>
>>> If I remove the offending characters then pgAdmin4 returns a result
>>> set. The database is SQL_ASCII encoded, pgAdmin4 *shouldn't care* that
>>> there are non UTF8 characters present.
>>>
>>
>> pgAdmin doesn't - Python does. If you can give some examples, we may be
>> able to figure out the issue and work around it.
>>
>> It's worth noting though that it's usually a bad idea to use SQL_ASCII
>> (that's not intended as an excuse, just as some advice).
>>
>> --
>> Dave Page
>> Blog: http://pgsnake.blogspot.com
>> Twitter: @pgsnake
>>
>> EnterpriseDB UK: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>
>

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company