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 1fV19l-0000am-DU for pgadmin-hackers@arkaria.postgresql.org; Mon, 18 Jun 2018 20:53:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fV19j-0001Dy-QI for pgadmin-hackers@arkaria.postgresql.org; Mon, 18 Jun 2018 20:53:43 +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 1fV19j-0001Do-7o for pgadmin-hackers@lists.postgresql.org; Mon, 18 Jun 2018 20:53:43 +0000 Received: from mail-wm0-x230.google.com ([2a00:1450:400c:c09::230]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fV19e-0004H4-RJ for pgadmin-hackers@postgresql.org; Mon, 18 Jun 2018 20:53:41 +0000 Received: by mail-wm0-x230.google.com with SMTP id j15-v6so18164144wme.0 for ; Mon, 18 Jun 2018 13:53:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pgadmin-org.20150623.gappssmtp.com; s=20150623; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=+zzUWncRHwlm/2uKORrGQfekdWa7mjLizV2k8uKWKrk=; b=q3Z4jsvqmjndDE2QJVDQNnmqeouYLk0MxDGgRKXgascxckpCJavqpqx0LVOGCrwSHP iqHDHAJOSwHkvHcSVbaNcmkN+/cdmlA3Z6K58kQ/EzHlxcbu0qS4E58Df4fmv8lyIT00 POp08OmqYCF2jwiYjo3JNrGccQC3QqvkNo3hXeKE6/hwI2DAKFaSpl56N4l8DnXKQi1k 2Y+bHnyodbnZyPZJCGLaiPm/i5CpCuaiteQYZFOLcg7z7P6IyVQXFJs1ry9tIDAOlNYF eFr1nF4maSC42ZW/Gc22bWF4XGIL5TcdNIJxeoLs3JRFCZ9J7Nz6+Ha2A/TeSNxbwiy+ qsZw== 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=+zzUWncRHwlm/2uKORrGQfekdWa7mjLizV2k8uKWKrk=; b=oJYxmEKa1VbxAb5wCylt1fTzT8NfGN7EAnEs7IMVOYQ83HRVl3QYXOVxVlglPIQAHM GCauF+NFUIey6gAn57H1eVpBrKRAmChhK/4wOvdTDa7frGR5f8Bos01D9tYL1yiYas4a TcNximmhuI7yIA2Nzj5WriqzwgJSd9b8bxjRHpVxrpEh9QHfpKe6zKZfaxo5zwmttina zqJKFo9q2+Cc0QVLrQ+WSISYEz1CClpj2rySmMCU6GUUNmrBGbxpn8/EW4e1nPSHJIAl UBs47+ic31chV43D4V+rk/SQnfehh1PZgBbRN64hmGsxHCyR2/Nv3q8PTEM92cOSFDLY HKew== X-Gm-Message-State: APt69E1IJw3qXNGjIhXUsHyPyBRaoabMenTBtF1yhxhd7UXjspC9bfTQ /7yrAeo3R/V9SP+sjmlm12LnK8k2mmTc0wc4QSDuXg== X-Google-Smtp-Source: ADUXVKIuqoORrun+2qpGcAyQ/n1JKvOMDRDtI063h9FBAwTMq7eEmhVa5k9xEpnTGrSgnfQyV2PzeA6Q4toNEPTWzuc= X-Received: by 2002:a1c:ec84:: with SMTP id h4-v6mr10118221wmi.154.1529355216361; Mon, 18 Jun 2018 13:53:36 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a1c:2907:0:0:0:0:0 with HTTP; Mon, 18 Jun 2018 13:53:35 -0700 (PDT) In-Reply-To: References: From: Dave Page Date: Mon, 18 Jun 2018 21:53:35 +0100 Message-ID: Subject: Re: [pgAdmin4][RM#3289] Can't query SQL_ASCII database. To: Aditya Toshniwal Cc: Joao De Almeida Pereira , pgadmin-hackers Content-Type: multipart/alternative; boundary="000000000000683011056ef0c23b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000683011056ef0c23b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi On Mon, Jun 18, 2018 at 2:14 PM, Aditya Toshniwal < aditya.toshniwal@enterprisedb.com> wrote: > Hi Hackers, > > Attached is the updated patch which includes the fix for Download CSV fai= l > in SQL_ASCII database, which is RM3250 > > This should fix RM3289 and RM3250. As they interrelated, sending the > combined patch. > Kindly review. > This is definitely looking better - both view and save now work as expected. However, using the test data the I posted upthread, if I try to edit a value (in this case by adding a couple of chars to the end of the data in row 2) I get: 2018-06-18 16:41:40,895: SQL pgadmin: Execute (void) for server #1 - DB:ascii (Query-id: 3093186): UPDATE public.ascii SET data =3D %(data)s::text WHERE id =3D '2'; 2018-06-18 16:41:41,027: INFO werkzeug: 127.0.0.1 - - [18/Jun/2018 16:41:41] "POST /sqleditor/save/2805058 HTTP/1.1" 500 - 2018-06-18 16:41:41,042: ERROR werkzeug: Error on request: Traceback (most recent call last): File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/se= rving.py", line 270, in run_wsgi execute(self.server.app) File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/se= rving.py", line 258, in execute application_iter =3D app(environ, start_response) File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.p= y", line 1997, in __call__ return self.wsgi_app(environ, start_response) File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.p= y", line 1985, in wsgi_app response =3D self.handle_exception(e) File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.p= y", line 1540, in handle_exception reraise(exc_type, exc_value, tb) File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.p= y", line 1982, in wsgi_app response =3D self.full_dispatch_request() File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.p= y", line 1614, in full_dispatch_request rv =3D self.handle_user_exception(e) File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.p= y", line 1517, in handle_user_exception reraise(exc_type, exc_value, tb) File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.p= y", line 1612, in full_dispatch_request rv =3D self.dispatch_request() File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.p= y", 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 776, in save default_conn) File "/Users/dpage/git/pgadmin4/web/pgadmin/tools/sqleditor/command.py", line 829, in save item['sql'], item['data']) File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/driver/psycopg2/connection.py"= , line 975, in execute_void self.__internal_blocking_execute(cur, query, params) File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/driver/psycopg2/connection.py"= , line 629, in __internal_blocking_execute cur.execute(query, params) File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/driver/psycopg2/cursor.py", line 176, in execute return _cursor.execute(self, query, params) UnicodeEncodeError: 'ascii' codec can't encode characters in position 19-21: ordinal not in range(128) > > On Fri, Jun 15, 2018 at 2:33 PM, Aditya Toshniwal enterprisedb.com> wrote: > >> Hi Dave, >> >> On Fri, Jun 15, 2018 at 2:08 PM, Dave Page wrote: >> >>> Hi >>> >>> On Thu, Jun 14, 2018 at 7:05 AM, Aditya Toshniwal < >>> aditya.toshniwal@enterprisedb.com> wrote: >>> >>>> I am sorry I missed the attachment. :( >>>> PFA. >>>> >>> >>> It looks like the encoding names are getting munged somewhere. I see >>> you've accounted for that to some degree in connection.py (you have bot= h >>> SQL_ASCII/MULE_INTERNAL and SQLASCII/MULEINTERNAL), however it doesn't = seem >>> to be enough as I'm getting the following error when trying to download= CSV >>> from the query tool. Can we ensure that conn.encoding contains an un-mu= nged >>> value at all times, or is that coming from psycopg2? >>> >> =E2=80=8BThat is done by pyscopg2 and conn.encoding is a psycopg2 connec= tion >> property.=E2=80=8B >> >> >>> >>> 2018-06-15 09:32:28,799: INFO werkzeug: 127.0.0.1 - - [15/Jun/2018 >>> 09:32:28] "GET /sqleditor/query_tool/download >>> /2732923?query=3DSELECT%20*%20FROM%20public.sql_ascii%0AORDER% >>> 20BY%20id%20ASC%20&filename=3Dsql_ascii.csv HTTP/1.1" 500 - >>> 2018-06-15 09:32:28,801: ERROR werkzeug: Error on request: >>> Traceback (most recent call last): >>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/= werkzeug/serving.py", >>> line 270, in run_wsgi >>> execute(self.server.app) >>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/= werkzeug/serving.py", >>> line 260, in execute >>> for data in application_iter: >>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/= werkzeug/wsgi.py", >>> line 870, in __next__ >>> return self._next() >>> File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/= werkzeug/wrappers.py", >>> line 82, in _iter_encoded >>> for item in iterable: >>> File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/driver/psycopg2/con= nection.py", >>> line 752, in gen >>> column_name =3D column_name.decode(conn_encoding) >>> LookupError: unknown encoding: SQLASCII >>> >> >> =E2=80=8BThis is because there is code bug here. Below is code used to d= ecode a >> column name. Connection encoding and python encoding are two different >> things. Python does not know what SQLASCII is. This will work with UTF-8 >> because python has decoder with same name. I tried to download CSV with = the >> original code without changes and it fails there too. I will fix this an= d >> will send the updated patch. I should have checked this. >> conn_encoding =3D cur.connection.encoding >> column_name =3D column_name.decode(conn_encoding)=E2=80=8B >> >> >>> >>> -- >>> Dave Page >>> Blog: http://pgsnake.blogspot.com >>> Twitter: @pgsnake >>> >>> EnterpriseDB UK: http://www.enterprisedb.com >>> The Enterprise PostgreSQL Company >>> >> >> >> >> -- >> Thanks and Regards, >> Aditya Toshniwal >> Software Engineer | EnterpriseDB Software Solutions | Pune >> "Don't Complain about Heat, Plant a tree" >> > > > > -- > Thanks and Regards, > Aditya Toshniwal > Software Engineer | EnterpriseDB Software Solutions | Pune > "Don't Complain about Heat, Plant a tree" > --=20 Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company --000000000000683011056ef0c23b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

On Mon, Jun 18, 2018 at 2:14 PM, Aditya Toshniwal &= lt;a= ditya.toshniwal@enterprisedb.com> wrote:
Hi Hackers,

Attached i= s the updated patch which includes the fix for Download CSV fail in SQL_ASC= II database, which is RM3250=C2=A0
This should fix RM3289 and RM3250. As they interrelated, sendi= ng the combined patch.
K= indly review.

This is definitel= y looking better - both view and save now work as expected. However, using = the test data the I posted upthread, if I try to edit a value (in this case= by adding a couple of chars to the end of the data in row 2) I get:
<= div>
2018-06-18 16:41:40,895: SQL pgadmin: Execute (v= oid) for server #1 - DB:ascii (Query-id: 3093186):
UPDATE public.= ascii SET
data =3D %(data)s::text WHERE
id =3D '2&#= 39;;
2018-06-18 16:41:41,027: INFO werkzeug: 127.0.0.1 - - [18= /Jun/2018 16:41:41] "POST /sqleditor/save/2805058 HTTP/1.1" 500 -=
2018-06-18 16:41:41,042: ERROR <= /span>werkzeug: Error on request:
Traceback (most recent call last):
=C2=A0 File "/Use= rs/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/werkzeug/serving= .py", line 270, in run_wsgi
=C2=A0 =C2=A0 execute(self.serve= r.app)
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/p= ython2.7/site-packages/werkzeug/serving.py", line 258, in execute
=C2=A0 =C2=A0 application_iter =3D app(environ, start_response)
=
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/sit= e-packages/flask/app.py", line 1997, in __call__
=C2=A0 =C2= =A0 return self.wsgi_app(environ, start_response)
=C2=A0 File &qu= ot;/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/flask/app= .py", line 1985, in wsgi_app
=C2=A0 =C2=A0 response =3D self= .handle_exception(e)
=C2=A0 File "/Users/dpage/.virtualenvs/= pgadmin4/lib/python2.7/site-packages/flask/app.py", line 1540, in hand= le_exception
=C2=A0 =C2=A0 reraise(exc_type, exc_value, tb)
=
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/sit= e-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-packages/flask/app.py&= quot;, line 1614, in full_dispatch_request
=C2=A0 =C2=A0 rv =3D s= elf.handle_user_exception(e)
=C2=A0 File "/Users/dpage/.virt= ualenvs/pgadmin4/lib/python2.7/site-packages/flask/app.py", line 1517,= in handle_user_exception
=C2=A0 =C2=A0 reraise(exc_type, exc_val= ue, tb)
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/= python2.7/site-packages/flask/app.py", line 1612, in full_dispatch_req= uest
=C2=A0 =C2=A0 rv =3D self.dispatch_request()
=C2= =A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packag= es/flask/app.py", line 1598, in dispatch_request
=C2=A0 =C2= =A0 return self.view_functions[rule.endpoint](**req.view_args)
= =C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-pac= kages/flask_login.py", line 792, in decorated_view
=C2=A0 = =C2=A0 return func(*args, **kwargs)
=C2=A0 File "/Users/dpag= e/git/pgadmin4/web/pgadmin/tools/sqleditor/__init__.py", line 776, in = save
=C2=A0 =C2=A0 default_conn)
=C2=A0 File "/Use= rs/dpage/git/pgadmin4/web/pgadmin/tools/sqleditor/command.py", line 82= 9, in save
=C2=A0 =C2=A0 item['sql'], item['data'= ])
=C2=A0 File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/= driver/psycopg2/connection.py", line 975, in execute_void
= =C2=A0 =C2=A0 self.__internal_blocking_execute(cur, query, params)
=C2=A0 File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/driver/psyc= opg2/connection.py", line 629, in __internal_blocking_execute
=C2=A0 =C2=A0 cur.execute(query, params)
=C2=A0 File "/Use= rs/dpage/git/pgadmin4/web/pgadmin/utils/driver/psycopg2/cursor.py", li= ne 176, in execute
=C2=A0 =C2=A0 return _cursor.execute(self, que= ry, params)
UnicodeEncodeError: 'ascii' codec can't e= ncode characters in position 19-21: ordinal not in range(128)
=C2=A0

On Fri, Jun 15, 2018 at 2:33 PM, Aditya Toshniwal = <aditya.toshniwal@enterprisedb.com> wro= te:
<= div style=3D"font-family:verdana,sans-serif">Hi Dave,

On Fri, Jun 15, 2018 at 2:0= 8 PM, Dave Page <dpage@pgadmin.org> wrote:
=E2=80=8BTh= at is done by pyscopg2 and conn.encoding is a psycopg2 connection property.= =E2=80=8B
=C2=A0

2018-06-15 09:32:28,799: INFO werkzeug: 127.0.0.1 - - [15/Jun/2018 09:32:28] "GET /sqleditor/quer= y_tool/download/2732923?query=3DSELECT%20*%20FROM%20public.sql_as= cii%0AORDER%20BY%20id%20ASC%20&filename=3Dsql_ascii.csv HTTP/= 1.1" 500 -
2018-06-15 09:32:28,801: ERROR werkzeug: Error on request:
Traceback (most recent call last):
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/pytho= n2.7/site-packages/werkzeug/serving.py", line 270, in run_wsgi
=C2=A0 =C2=A0 execute(self.server.app)
=C2=A0 File "= /Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/site-packages/we= rkzeug/serving.py", line 260, in execute
=C2=A0 =C2=A0 for d= ata in application_iter:
=C2=A0 File "/Users/dpage/.virtuale= nvs/pgadmin4/lib/python2.7/site-packages/werkzeug/wsgi.py", = line 870, in __next__
=C2=A0 =C2=A0 return self._next()
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/python2.7/si= te-packages/werkzeug/wrappers.py", line 82, in _iter_encoded
=C2=A0 =C2=A0 for item in iterable:
=C2=A0 File "/User= s/dpage/git/pgadmin4/web/pgadmin/utils/driver/psycopg2/connection= .py", line 752, in gen
=C2=A0 =C2=A0 column_name =3D column_= name.decode(conn_encoding)
LookupError: unknown encoding: SQ= LASCII
=C2=A0
=E2= =80=8BThis is because there is code bug here. Below is code used to decode = a column name. Connection encoding and python encoding are two different th= ings. Python does not know what SQLASCII is. This will work with UTF-8 beca= use python has decoder with same name. I tried to download CSV with the ori= ginal code without changes and it fails there too. I will fix this and will= send the updated patch. I should have checked this.
conn_encoding =3D cu= r.connection.encoding
column_name =3D column_name.decode(conn_encodi= ng)=E2=80=8B
=C2=A0

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

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



--
Thanks and Regards,
Aditya Toshniwal
Software Engineer |=C2=A0= EnterpriseDB Software Solutions |=C2=A0= Pune
"Don't Compla= in about Heat, Plant a tree"



--
Thanks and Regards,
Aditya Toshniwal
Software Engineer |=C2=A0EnterpriseDB Software Solutions |= =C2=A0Pune
"Don't Complain about Heat, Plant a tree"=



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

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgre= SQL Company
--000000000000683011056ef0c23b--