Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fQsnG-0000yZ-TH for pgadmin-hackers@arkaria.postgresql.org; Thu, 07 Jun 2018 11:09:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fQsnF-0005Sc-TL for pgadmin-hackers@arkaria.postgresql.org; Thu, 07 Jun 2018 11:09:25 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fQsjr-00026v-0h for pgadmin-hackers@lists.postgresql.org; Thu, 07 Jun 2018 11:05:55 +0000 Received: from mail-lf0-x232.google.com ([2a00:1450:4010:c07::232]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fQsjl-0008UT-AO for pgadmin-hackers@postgresql.org; Thu, 07 Jun 2018 11:05:53 +0000 Received: by mail-lf0-x232.google.com with SMTP id i15-v6so3703212lfc.2 for ; Thu, 07 Jun 2018 04:05:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb-com.20150623.gappssmtp.com; s=20150623; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=V50kOpwF1gOBaZRHHUWdFr6L9k1Lx6wVED5+oNA0wnI=; b=FaoLxH8JX/YhEsAeLjdnISfWC62DrEmKwzeQJMgoU2dUxCLTQtK9qzW00fWtTvffHw SkCgxVckLcyalo878Ok0yPMkxcw1CrAkEpskg3CVwM4Uv0bEXfZZZjT4k0IkaUOmt2WT ROmbT2k5Y1aKypchcy0M97kx7ecH9UhRFX2ZJENjqNZjuc11IcEtuvBN5PnqbHT02Wsp W9QnCxscPWnv2DmwUbZMg5Y7iCbz1MBC7TMpzZBw4XmOEY5MSNYaAbmcd2kWlv5/zMzQ lhRHTJ3cL+VfFalTtrkbVu5gCMFwUHQ8AKuIOt7K9ATR3phnmR+HCDNvkT8zAGKub7Uh A6hg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=V50kOpwF1gOBaZRHHUWdFr6L9k1Lx6wVED5+oNA0wnI=; b=cSMfu20BfTUX02CDHsZZMmnxP2uCmGcy4CAz2UlJHJtZ+Jl4kpl0uxxPvokhXfe+TW l3G/w3ykOtLvB14PbptX8qcwjJy+SVFN4qc+OVot9k3ov8RAAUxeJ34c45Q3w4qG7RGz qrITDm5+vxBZYwygiGXhjwX7+ExStC8ofjl3R+hiBve3pajACz6b0MPU6uz5UF5uTI+o GAjt4NyRHOeyyj0pS018wrP23HIYzjohm1gNybqJcB0yrhIj+/vTJnt+s2ZQnx0jsqUE lynTlLZpeqeqdecGjHZOIbpy4HIpVUALhIJ3PGVhGIcA8VmBgl7iRE1b2VCLMekXhJbK ex0A== X-Gm-Message-State: APt69E2ZkIcSshUZvrswzvmknIVggWwDhFe3+W2SBLUu2h0pBGBW8h9j rajKvaOF7TwrTRVjDfZ5KFd0g6bhGxu81nbX6hVY7w== X-Google-Smtp-Source: ADUXVKImfC4nMBBHeRWnUzte2VJbue13XmVNqOvlt3+cj57Wff3n150Pnl1haa8c3AERo6QBQOIWaKjvgnu30l0epyk= X-Received: by 2002:a2e:1545:: with SMTP id 5-v6mr1176031ljv.56.1528369547278; Thu, 07 Jun 2018 04:05:47 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a2e:9e8a:0:0:0:0:0 with HTTP; Thu, 7 Jun 2018 04:05:46 -0700 (PDT) In-Reply-To: References: From: Aditya Toshniwal Date: Thu, 7 Jun 2018 16:35:46 +0530 Message-ID: Subject: Re: [pgAdmin4][RM#3289] Can't query SQL_ASCII database. To: Dave Page Cc: Joao De Almeida Pereira , pgadmin-hackers Content-Type: multipart/alternative; boundary="000000000000f392cb056e0b436f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000f392cb056e0b436f Content-Type: text/plain; charset="UTF-8" Hi Dave, On Thu, Jun 7, 2018 at 4:07 PM, Dave Page wrote: > Hi > > On Wed, Jun 6, 2018 at 2:02 PM, Aditya Toshniwal < > aditya.toshniwal@enterprisedb.com> wrote: > >> Hi Hackers, >> >> PFA updated patch as the previous one was not working as expected. I have >> tried to make it similar to that of pgAdmin3 and you do not need to change >> client_encoding as it is set now based on server encoding. It works fine >> with "view data" also. >> > > - In connection.py, at ~409, shouldn't we set the client_encoding to > SQL_ASCII? Otherwise it could be overridden with something unexpected if > the client has PGCLIENTENCODING set for example. > Yeah I agree, it would be better to add. Will add the change. > > - With or without that change, I get the following test failure on macOS > with Python 2.7.10: > It works fine on my machine with Python 2.7 and macOS. Could you please let me know the Postgres DB version also. Will test on few more machines. > > ====================================================================== > ERROR: runTest (pgadmin.tools.sqleditor.tests.test_encoding_charset. > TestEncodingCharset) > With Encoding SQL_ASCII > ---------------------------------------------------------------------- > Traceback (most recent call last): > File "/Users/dpage/git/pgadmin4/web/pgadmin/tools/sqleditor/ > tests/test_encoding_charset.py", line 86, in runTest > response = self.tester.get(url) > File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/test.py", > line 830, in get > return self.open(*args, **kw) > File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/testing.py", > line 127, in open > follow_redirects=follow_redirects) > File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/test.py", > line 803, in open > response = self.run_wsgi_app(environ, buffered=buffered) > File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/test.py", > line 716, in run_wsgi_app > rv = run_wsgi_app(self.application, environ, buffered=buffered) > File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/test.py", > line 923, in run_wsgi_app > app_rv = app(environ, start_response) > File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py", > line 1997, in __call__ > return self.wsgi_app(environ, start_response) > File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py", > line 1985, in wsgi_app > response = self.handle_exception(e) > File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py", > line 1540, in handle_exception > reraise(exc_type, exc_value, tb) > File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py", > line 1982, in wsgi_app > response = self.full_dispatch_request() > File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py", > line 1614, in full_dispatch_request > rv = self.handle_user_exception(e) > File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py", > line 1517, in handle_user_exception > reraise(exc_type, exc_value, tb) > File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py", > line 1612, in full_dispatch_request > rv = self.dispatch_request() > File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py", > line 1598, in dispatch_request > return self.view_functions[rule.endpoint](**req.view_args) > File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask_login.py", > line 792, in decorated_view > return func(*args, **kwargs) > File "/Users/dpage/git/pgadmin4/web/pgadmin/tools/sqleditor/__init__.py", > line 576, in poll > 'oids': oids > File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/ajax.py", line 61, in > make_json_response > separators=(',', ':')), > File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site- > packages/simplejson/__init__.py", line 399, in dumps > **kw).encode(obj) > File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site- > packages/simplejson/encoder.py", line 291, in encode > chunks = self.iterencode(o, _one_shot=True) > File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site- > packages/simplejson/encoder.py", line 373, in iterencode > return _iterencode(o, 0) > UnicodeDecodeError: 'utf8' codec can't decode byte 0xad in position 0: > invalid start byte > > ---------------------------------------------------------------------- > Ran 317 tests in 30.692s > > FAILED (errors=1, skipped=21) > > > >> The only problem is, I cannot find equivalent codec for wxConvLibc in >> python. The closest one I could find is raw_unicode_escape. So, in a >> SQL_ASCII database, non ASCII characters may differ in pgAdmin4 and >> pgAdmin3, but it will display results. >> > > Yeah, I think that's fine. For the small number of people with SQL_ASCII > databases, seeing escaped characters is better than nothing. > > >> >> >> Dave, >> You need to add "E" before the string to be inserted, otherwise \x will >> be considered as a plain string. >> INSERT INTO sql_ascii (data) VALUES (E'[Invalid UTF-8] Blob: >> \xf4\xa5\xa3\xa5'); >> > > Yeah, sorry - I copied the wrong version of the query :-( > > >> >> >> 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 6:42 PM, Dave Page wrote: >> >>> Hi >>> >>> On Tue, Jun 5, 2018 at 2:03 PM, Aditya Toshniwal < >>> aditya.toshniwal@enterprisedb.com> wrote: >>> >>>> Hi >>>> >>>> On Tue, Jun 5, 2018 at 6:25 PM, Dave Page wrote: >>>> >>>>> >>>>> >>>>> On Tue, Jun 5, 2018 at 1:49 PM, Aditya Toshniwal < >>>>> aditya.toshniwal@enterprisedb.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). >>>>> >>>> >>>> After setting the client_encoding to SQL_ASCII you got the output. >>>> Previously, it used to fail in the back end itself because python encoding >>>> failure. That is fixed. >>>> The error ERROR: invalid byte sequence for encoding "UTF8": 0x80 is >>>> thrown by postgres and not python or pgAdmin4. You will get the same error >>>> even if you >>>> connect from psql. >>>> >>> >>> Sure - but that is not a fix. You have no way of running the SET command >>> if you're using "view data" - and in the query tool, users just expect it >>> to work (as it did in pgAdmin 3). >>> >>> >>>> >>>>> >>>>>> 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 >>>>> >>>>> wxLogInfo(wxT("Setting client_encoding to '%s'") >>>>> , encoding.c_str()); >>>>> 301 >>>>> >>>>> if (PQsetClientEncoding(conn, encoding.ToAscii())) >>>>> 302 >>>>> >>>>> { >>>>> 303 >>>>> >>>>> wxLogError(wxT("%s"), GetLastError().c_str()); >>>>> 304 >>>>> >>>>> } >>>>> 305 >>>>> >>>>> >>>>> Oops ! Missed that. Apologies. >>>> >>>>> >>>>> >>>>>> >>>>>> 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). >>>>> >>>>> >>>> Need to rework on the initialise method. Will come with an updated. >>>> patch. Sorry for trouble. >>>> >>>>> >>>>>> >>>>>> On Tue, Jun 5, 2018 at 6:05 PM, Dave Page wrote: >>>>>> >>>>>>> Hi >>>>>>> >>>>>>> On Tue, Jun 5, 2018 at 1:21 PM, Aditya Toshniwal < >>>>>>> aditya.toshniwal@enterprisedb.com> wrote: >>>>>>> >>>>>>>> Hi Dave, >>>>>>>> >>>>>>>> >>>>>>>> On Tue, Jun 5, 2018 at 4:56 PM, Dave Page >>>>>>>> wrote: >>>>>>>> >>>>>>>>> Hi >>>>>>>>> >>>>>>>>> On Tue, Jun 5, 2018 at 9:50 AM, Aditya Toshniwal < >>>>>>>>> aditya.toshniwal@enterprisedb.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.toshniwal@enterprisedb.com> wrote: >>>>>>>>>> >>>>>>>>>>> Hi >>>>>>>>>>> >>>>>>>>>>> On Tue, Jun 5, 2018 at 1:08 AM, Joao De Almeida Pereira < >>>>>>>>>>> jdealmeidapereira@pivotal.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 >>>>> >>>> >>>> >>> >>> >>> -- >>> 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 > --000000000000f392cb056e0b436f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Dave,


On Thu, Jun 7, 2018 at 4:07 PM, Dave Page <= dpage@pgadmin.org> wrote:
<= div dir=3D"ltr">Hi

On Wed, Jun 6, 2018 at 2:02 PM, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:<= br>
Hi Hackers,

PFA updated patch as the = previous one was not working as expected. I have tried to make it similar t= o that of pgAdmin3 and you do not need to change client_encoding as it is s= et now based on server encoding. It works fine with "view data" a= lso.

- In connection.py,= at ~409, shouldn't we set the client_encoding to SQL_ASCII? Otherwise = it could be overridden with something unexpected if the client has PGCLIENT= ENCODING set for example.
Yeah I a= gree, it would be better to add. Will add the change.=C2=A0

- With or without that change, I get t= he following test failure on macOS with Python 2.7.10:
It works fine on my machine with Python 2.7 and macOS.= Could you please let me know the Postgres DB version also.
Will = test on few more machines.=C2=A0

<= /div>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D
ERROR: runTest (pgadmin.tools.sqleditor.tests.t= est_encoding_charset.TestEncodingCharset)
With Encoding SQL_= ASCII
------------------------------------------------------= ----------------
Traceback (most recent cal= l last):
=C2=A0 File "/Users/dpage/git/pgadmin4/= web/pgadmin/tools/sqleditor/tests/test_encoding_charset.py",= line 86, in runTest
=C2=A0 =C2=A0 response =3D self.tester.get(u= rl)
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib= /python2.7/site-packages/werkzeug/test.py", line 830, in get
=C2=A0 =C2=A0 return self.open(*args, **kw)
=C2=A0 File &qu= ot;/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages= /flask/testing.py", line 127, in open
=C2=A0 =C2=A0 follow_r= edirects=3Dfollow_redirects)
=C2=A0 File "/Users/dpage/= .virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/test.p= y", line 803, in open
=C2=A0 =C2=A0 response =3D self.run_ws= gi_app(environ, buffered=3Dbuffered)
=C2=A0 File "/Users/dpa= ge/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/tes= t.py", line 716, in run_wsgi_app
=C2=A0 =C2=A0 rv =3D run_ws= gi_app(self.application, environ, buffered=3Dbuffered)
=C2=A0 Fil= e "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-pac= kages/werkzeug/test.py", line 923, in run_wsgi_app
=C2=A0 = =C2=A0 app_rv =3D app(environ, start_response)
=C2=A0 File "= /Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/fl= ask/app.py", line 1997, in __call__
=C2=A0 =C2=A0 return sel= f.wsgi_app(environ, start_response)
=C2=A0 File "/Users/dpag= e/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py&= quot;, line 1985, in wsgi_app
=C2=A0 =C2=A0 response =3D self.han= dle_exception(e)
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py", line 1540, i= n handle_exception
=C2=A0 =C2=A0 reraise(exc_type, exc_value, tb)=
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/py= thon2.7/site-packages/flask/app.py", line 1982, in wsgi_app
=
=C2=A0 =C2=A0 response =3D self.full_dispatch_request()
=C2= =A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-<= wbr>packages/flask/app.py", line 1614, in full_dispatch_request
<= div>=C2=A0 =C2=A0 rv =3D self.handle_user_exception(e)
=C2=A0 Fil= e "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-pac= kages/flask/app.py", line 1517, in handle_user_exception
=C2= =A0 =C2=A0 reraise(exc_type, exc_value, tb)
=C2=A0 File "/Us= ers/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask= /app.py", line 1612, in full_dispatch_request
=C2=A0 =C2=A0 = rv =3D self.dispatch_request()
=C2=A0 File "/Users/dpage/.vi= rtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py"= , line 1598, in dispatch_request
=C2=A0 =C2=A0 return self.view_f= unctions[rule.endpoint](**req.view_args)
=C2=A0 File "/= Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/fla= sk_login.py", line 792, in decorated_view
=C2=A0 =C2=A0 retu= rn func(*args, **kwargs)
=C2=A0 File "/Users/dpage/git/pgadm= in4/web/pgadmin/tools/sqleditor/__init__.py", line 576, in p= oll
=C2=A0 =C2=A0 'oids': oids
=C2=A0 File &quo= t;/Users/dpage/git/pgadmin4/web/pgadmin/utils/ajax.py", line 61, = in make_json_response
=C2=A0 =C2=A0 separators=3D(',', &#= 39;:')),
=C2=A0 File "/Users/dpage/.virtualenvs/pga= dmin4/lib/python2.7/site-packages/simplejson/__init__.py", l= ine 399, in dumps
=C2=A0 =C2=A0 **kw).encode(obj)
=C2= =A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-<= wbr>packages/simplejson/encoder.py", line 291, in encode
=C2=A0 =C2=A0 chunks =3D self.iterencode(o, _one_shot=3DTrue)
= =C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/sit= e-packages/simplejson/encoder.py", line 373, in iterencode
=C2=A0 =C2=A0 return _iterencode(o, 0)
UnicodeDecodeErro= r: 'utf8' codec can't decode byte 0xad in position 0: invalid s= tart byte

-----------------------------------= -----------------------------------
Ran 317 tests in 30.692s=

FAILED (errors=3D1, skipped=3D21)

<= /div>
=C2=A0
The only problem is, I cannot fin= d equivalent codec for wxConvLibc in python. The closest one I could find i= s raw_unicode_escape. So, in a SQL_ASCII database, non ASCII characters may= differ in pgAdmin4 and pgAdmin3, but it will display results.
<= /blockquote>

Yeah, I think that's fine. For t= he small number of people with SQL_ASCII databases, seeing escaped characte= rs is better than nothing.
=C2=A0


You need to add &q= uot;E" before the string to be inserted, otherwise \x will be consider= ed as a plain string.
INSERT INT= O sql_ascii (data) VALUES (E'[Invalid UTF-8]=C2=A0 Blob: \xf4\xa5\xa3\x= a5');

Yeah, s= orry - I copied the wrong version of the query :-(
=C2=A0


Kindly review.

Thanks and Regards,
Aditya Toshniwal
Software Engineer |=C2=A0EnterpriseD= B Software Solutions |=C2=A0Pune=
"Don't Complain about He= at, Plant a tree"

On Tue, Jun 5, 2018 at 6:42 PM, Da= ve Page <dpage@pgadmin.org> wrote:
Hi

On Tue, Jun 5, 2= 018 at 2:03 PM, Aditya Toshniwal <aditya.toshniwal@enterpr= isedb.com> wrote:
Hi

On Tue, Jun 5, 2018 at 6:25 PM, Dave Page <dpag= e@pgadmin.org> wrote:


On Tue, Jun 5, 2018 at 1:49 PM, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote= :
Hi Dave,

The problem of SQL ASCII is solved with th= e 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).

After setting the client_encoding= to SQL_ASCII you got the output. Previously, it used to fail in the back e= nd itself because python encoding failure. That is fixed.
The err= or=C2=A0ERROR:=C2=A0 invalid byte sequence f= or encoding "UTF8": 0x80=C2=A0is thrown by postgres= and not python or pgAdmin4. You will get the same error even if you=C2=A0<= /div>
connect from psql.

<= /div>
Sure - but that is not a fix. You have no way of running t= he SET command if you're using "view data" - and in the query= tool, users just expect it to work (as it did in pgAdmin 3).
=C2=A0
= =C2=A0
I can see there is no SET call in pgAdmin3 for client_encoding.=C2=A0 I= can remove the=C2=A0SET client_encoding=3D'= UNICODE'; that will solve the problem.= But, can you please let me know why that was added.=C2=A0

There is, but it's inside an API ca= ll (PQsetClientEncoding):

300 = =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0wxL= ogInfo(wxT("Setting=C2=A0client_encoding=C2=A0to=C2=A0'%s'= ;"),=C2=A0encoding.c_str());
301 =C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0if=C2=A0(PQsetClientEncod= ing(conn,=C2=A0encoding.ToAscii()))
302 =C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0{
303 =C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0wxLogError(wxT("%s"),=C2=A0GetLastError().c_str()= );
304 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0}
305

=
Oops ! Missed that. Apologies.=C2=A0
=C2=A0

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 da= tabase (I believe).
=C2=A0
Need to rework on the initialise method. Will come with= an updated. patch. Sorry for trouble.=C2=A0


On Tue, Jun 5, 2018 at 6:05 PM, Dave Page &l= t;dpage@pgadmin.org<= /a>> wrote:
=
Hi

On Tue, Jun 5, 2018 at= 1:21 PM, Aditya Toshniwal <aditya.toshniwal@enterprisedb<= wbr>.com> wrote:
Hi Dave,


On Tue, Jun 5, 2018 at 4:56 PM, Dave Page <dpage@p= gadmin.org> wrote:
Hi

On Tue, Jun 5, 2018 at 9:50 AM, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:=
Hi H= ackers,

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 wi= th data:

/Library/PostgreSQL/9.4/bin/psq= l -d sql_ascii -U postgres -c "INSERT INTO sql_acsii (data) VALUES (&#= 39;[Windows-1252]=C2=A0 =C2=A0Euro: \x80=C2=A0 =C2=A0Double dagger: \x87= 9;);"
/Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U = postgres -c "INSERT INTO sql_ascii (data) VALUES ('[Latin-1]=C2=A0= =C2=A0Yen: \xa5=C2=A0 =C2=A0Half: \xbd');"
/Library/Pos= tgreSQL/9.4/bin/psql -d sql_ascii -U postgres -c "INSERT INTO sql= _ascii (data) VALUES ('[Japanese]=C2=A0 =C2=A0Ship: \xe8\x88\xb9');= "
/Library/PostgreSQL/9.4/bin/psql -d sql_ascii -U post= gres -c "INSERT INTO sql_ascii (data) VALUES ('[Invalid UTF-8]=C2= =A0 Blob: \xf4\xa5\xa3\xa5');"

I th= en 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 pgadmi= n: Execute (async) for server #= 1 - CONN:1187535 (Query-id: 8522474):
SELECT * FROM public.sql_as= cii
ORDER BY id ASC=C2=A0
2018-06-05 12:23:27,320: ERRO= R pgadmin: Failed to execute query (execute_async) for the ser= ver #1 - CONN:1187535(Query-id: 8522474):
Error Message:ERROR:=C2= =A0 invalid byte sequence for encoding "UTF8": 0x80
SQL= state: 22021

Running "SELECT * FROM sq= l_ascii" in the query tool resulted in the same error, however, if I r= an "SET client_encoding =3D 'SQL_ASCII';" first, I do see= results.

I have confirmed that I've restarted= the server after applying the patch.

What am I mi= ssing? Why don't we just set the client_encoding to SQL_ASCII if it'= ;s a SQL_ASCII database?
= =C2=A0
It is by default same as the server encoding. But, the fol= lowing existing code in=C2=A0=C2=A0web/pgadmin/utils/drive= r/psycopg2/connection.py makes the client_encoding as UNICODE f= or every connection. I am not sure it should be removed.

=C2=A0 =C2= =A0 =C2=A0 =C2=A0 status =3D _execute(cur, "SET DateStyle=3DISO= ;"

=C2=A0=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 "SET client_min_messages=3Dnotice;&quo= t;

=C2=A0=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 "SET bytea_output=3Descape;"

=C2=A0=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 "SET client_encoding=3D'UNICODE= 9;;")


<= /div>
It was probably before you joined, but I have said a = number of times that pgAdmin 3 handled this differently and that maybe we s= hould do it the same way here. See=C2=A0https://git.postgresql.org/gitweb/?p=3Dpgadmin3.git;a=3Dblob;f=3Dpgadmin/db/pgConn.cpp, in the pgConn::Initialize() function.

Either way, your patch isn't working for me.
=C2=A0


<= div class=3D"gmail_quote">
Note that this testing was on Python 2.7.10 = on MacOS.
=C2=A0

Kindly review.

Thanks and Regards,<= /font>
Aditya Toshniwal
Software E= ngineer |=C2=A0EnterpriseDB Software Solutions |=C2=A0Pune
"D= on't Complain about Heat, Plant a tree"
<= /div>

On Tue, Jun 5, 2018 at 10:15 AM, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:
Hi

On Tue, Jun 5, 2018 a= t 1:08 AM, Joao De Almeida Pereira <jdealmeidapereira@pivotal.i= o> wrote:
Hello Aditya,


<= /div>
There is no change related to notifications in this patch.=C2=A0<= /div>
The below code is minor fix related to connection status of sql e= ditor. Can you please share the code snippet if it is not the below.
<= div>
-=C2=A0 =C2=A0 =C2=A0 =C2=A0 # Check for the asynch= ronous notifies statements.
-=C2=A0 =C2=A0 =C2=A0 =C2=A0 conn.che= ck_notifies(True)
-=C2=A0 =C2=A0 =C2=A0 =C2=A0 notifies =3D conn.= get_notifies()
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 if status is not None= :
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 # Check for the asyn= chronous notifies statements.
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 conn.check_notifies(True)
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 notifies =3D 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 &&a= mp; Joao





--
Dave Page
Blog: http://pgsnake.blogsp= ot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
T= he 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: @pg= snake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL C= ompany




--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
<= /div>




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

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

--000000000000f392cb056e0b436f--