Re: [pgAdmin4][RM#3289] Can't query SQL_ASCII database.

On Tue, Jun 5, 2018 at 1:49 PM, Aditya Toshniwal <
aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:

> Hi Dave,
>
> The problem of SQL ASCII is solved with the patch, and not related to
> setting the client encoding of the sql window.
>

No it's not. It doesn't work for me as I said (and showed the example of).

> I can see there is no SET call in pgAdmin3 for client_encoding. I can
> remove the SET client_encoding='UNICODE'; that will solve the problem.
> But, can you please let me know why that was added.
>

There is, but it's inside an API call (PQsetClientEncoding):

300
<https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l300>
wxLogInfo(wxT("Setting client_encoding to '%s'"), encoding.c_str());
301
<https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l301>
if (PQsetClientEncoding(conn, encoding.ToAscii()))
302
<https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l302>
{
303
<https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l303>
wxLogError(wxT("%s"), GetLastError().c_str());
304
<https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l304>
}
305
<https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob;f=pgadmin/db/pgConn.cpp#l305>

>
> Will remove the set call and will send you the updated patch if everything
> works fine.
>

No, we need to ensure the client encoding is set correctly. It just needs
to be set to SQL_ASCII if it's a SQL_ASCII database (I believe).

>
>
> Thanks and Regards,
> Aditya Toshniwal
> Software Engineer | EnterpriseDB Software Solutions | Pune
> "Don't Complain about Heat, Plant a tree"
>
> On Tue, Jun 5, 2018 at 6:05 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>
>> Hi
>>
>> On Tue, Jun 5, 2018 at 1:21 PM, Aditya Toshniwal <
>> aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:
>>
>>> Hi Dave,
>>>
>>>
>>> On Tue, Jun 5, 2018 at 4:56 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>>
>>>> Hi
>>>>
>>>> On Tue, Jun 5, 2018 at 9:50 AM, Aditya Toshniwal <
>>>> aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:
>>>>
>>>>> Hi Hackers,
>>>>>
>>>>> PFA updated patch. The sqleditor change is sent separately and removed
>>>>> from current patch as suggested.
>>>>> The test cases were running fine when the module was specified using
>>>>> --pkg but were failing in complete run. Fixed that.
>>>>>
>>>>
>>>> I did a quick test by creating a SQL_ASCII database containing a simple
>>>> table:
>>>>
>>>> CREATE TABLE sql_ascii (id serial primary key, data text);
>>>>
>>>> And then populated it with data:
>>>>
>>>> /Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U postgres -c "INSERT
>>>> INTO sql_acsii (data) VALUES ('[Windows-1252] Euro: \x80 Double dagger:
>>>> \x87');"
>>>> /Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U postgres -c "INSERT
>>>> INTO sql_ascii (data) VALUES ('[Latin-1] Yen: \xa5 Half: \xbd');"
>>>> /Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U postgres -c "INSERT
>>>> INTO sql_ascii (data) VALUES ('[Japanese] Ship: \xe8\x88\xb9');"
>>>> /Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U postgres -c "INSERT
>>>> INTO sql_ascii (data) VALUES ('[Invalid UTF-8] Blob: \xf4\xa5\xa3\xa5');"
>>>>
>>>> I then right-clicked the table in the treeview, and selected the option
>>>> to view all rows, and immediately saw an error:
>>>>
>>>> 2018-06-05 12:23:27,319: SQL pgadmin: Execute (async) for server #1 -
>>>> CONN:1187535 (Query-id: 8522474):
>>>> SELECT * FROM public.sql_ascii
>>>> ORDER BY id ASC
>>>> 2018-06-05 12:23:27,320: ERROR pgadmin: Failed to execute query
>>>> (execute_async) for the server #1 - CONN:1187535(Query-id: 8522474):
>>>> Error Message:ERROR: invalid byte sequence for encoding "UTF8": 0x80
>>>> SQL state: 22021
>>>>
>>>> Running "SELECT * FROM sql_ascii" in the query tool resulted in the
>>>> same error, however, if I ran "SET client_encoding = 'SQL_ASCII';" first, I
>>>> do see results.
>>>>
>>>> I have confirmed that I've restarted the server after applying the
>>>> patch.
>>>>
>>>> What am I missing? Why don't we just set the client_encoding to
>>>> SQL_ASCII if it's a SQL_ASCII database?
>>>>
>>>
>>> It is by default same as the server encoding. But, the following
>>> existing code in web/pgadmin/utils/driver/psycopg2/connection.py makes
>>> the client_encoding as UNICODE for every connection. I am not sure it
>>> should be removed.
>>>
>>> status = _execute(cur, "SET DateStyle=ISO;"
>>>
>>> "SET client_min_messages=notice;"
>>>
>>> "SET bytea_output=escape;"
>>>
>>> "SET client_encoding='UNICODE';")
>>>
>>
>> It was probably before you joined, but I have said a number of times that
>> pgAdmin 3 handled this differently and that maybe we should do it the same
>> way here. See https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob
>> ;f=pgadmin/db/pgConn.cpp, in the pgConn::Initialize() function.
>>
>> Either way, your patch isn't working for me.
>>
>>
>>>
>>>
>>> Note that this testing was on Python 2.7.10 on MacOS.
>>>>
>>>>
>>>>>
>>>>> Kindly review.
>>>>>
>>>>> Thanks and Regards,
>>>>> Aditya Toshniwal
>>>>> Software Engineer | EnterpriseDB Software Solutions | Pune
>>>>> "Don't Complain about Heat, Plant a tree"
>>>>>
>>>>> On Tue, Jun 5, 2018 at 10:15 AM, Aditya Toshniwal <
>>>>> aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:
>>>>>
>>>>>> Hi
>>>>>>
>>>>>> On Tue, Jun 5, 2018 at 1:08 AM, Joao De Almeida Pereira <
>>>>>> jdealmeidapereira(at)pivotal(dot)io> wrote:
>>>>>>
>>>>>>> Hello Aditya,
>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> There is no change related to notifications in this patch.
>>>>>>>> The below code is minor fix related to connection status of sql
>>>>>>>> editor. Can you please share the code snippet if it is not the below.
>>>>>>>>
>>>>>>>> - # Check for the asynchronous notifies statements.
>>>>>>>> - conn.check_notifies(True)
>>>>>>>> - notifies = conn.get_notifies()
>>>>>>>> + if status is not None:
>>>>>>>> + # Check for the asynchronous notifies statements.
>>>>>>>> + conn.check_notifies(True)
>>>>>>>> + notifies = conn.get_notifies()
>>>>>>>>
>>>>>>>>
>>>>>>> This is a minor fix, but is it related to querying SQL_ASCII
>>>>>>> database?
>>>>>>>
>>>>>> No its not. It is something I found when I was working on SQL_ASCII
>>>>>> related changes.
>>>>>> Well then, will send a separate patch for it.
>>>>>>
>>>>>>>
>>>>>>> Thanks
>>>>>>> Victoria && Joao
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> 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
>>
>
>

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

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