Filtering on char/nchar

--0000000000004577e70593c2044a
Content-Type: multipart/alternative; boundary="0000000000004577e20593c20448"

--0000000000004577e20593c20448
Content-Type: text/plain; charset="UTF-8"

Hello,

I have a question about char/nchar and binding parameters type.

We have tables which have char and nchar PK columns. When filtering with
bound parameters on these columns, it is required to put in exact value
with padding in where clause. Though, when the same is done as text,
without params filtering works as expected.

Examples:
CREATE TABLE DBTESTING.nchartable
(
    id NCHAR(4) PRIMARY KEY NOT NULL
);
insert into nchartable values ('1');

cur.execute("select * from nchartable where id = '1'")  found
cur.execute("select * from nchartable where id = :id", ['1   ']) found
cur.execute("select * from nchartable where id = :id", ['1']) not found

After debugging I found, that where clause fails because the parameter is
nvarchar2. When I manually set the type to nchar all cases above works. See
attached code.
Is this behavior intended or it is a bug?
How to correctly search on nchar column without adding padding in a query
and preferably without specifying type manually?

Checked on cx_oracle 7.2.2

--0000000000004577e20593c20448
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello,<br><br>I have a question about char/nchar and bindi=
ng parameters type.<br><br>We have tables which have char and nchar PK colu=
mns. When filtering with bound parameters on these columns, it is required =
to put in exact value with padding in where clause. Though, when the same i=
s done as text, without params filtering works as expected.<br><br>Examples=
:<br>CREATE TABLE DBTESTING.nchartable<br>(<br>=C2=A0 =C2=A0 id NCHAR(4) PR=
IMARY KEY NOT NULL<br>);<br>insert into nchartable values (&#39;1&#39;);<br=
><br>cur.execute(&quot;select * from nchartable where id =3D &#39;1&#39;&qu=
ot;) =C2=A0found<br>cur.execute(&quot;select * from nchartable where id =3D=
 :id&quot;, [&#39;1 =C2=A0 &#39;]) found<br>cur.execute(&quot;select * from=
 nchartable where id =3D :id&quot;, [&#39;1&#39;]) not found<br><br>After d=
ebugging I found, that where clause fails because the parameter is nvarchar=
2. When I manually set the type to nchar all cases above works. See attache=
d code.<br>Is this behavior intended or it is a bug?<br>How to correctly se=
arch on nchar column without adding padding in a query and preferably witho=
ut specifying type manually?<br><br>Checked on cx_oracle 7.2.2</div>

--0000000000004577e20593c20448--
--0000000000004577e70593c2044a
Content-Type: text/x-python-script; charset="US-ASCII"; name="cx_oracle_query.py"
Content-Disposition: attachment; filename="cx_oracle_query.py"
Content-Transfer-Encoding: base64
Content-ID: <f_k167zwwz0>
X-Attachment-Id: f_k167zwwz0

ZnJvbSBfX2Z1dHVyZV9fIGltcG9ydCBwcmludF9mdW5jdGlvbgppbXBvcnQgY3hfT3JhY2xlCgp1
c2VycHdkID0gJ3A0c3N3MHJkJwpjb25uZWN0aW9uID0gY3hfT3JhY2xlLmNvbm5lY3QoJ2RidGVz
dGluZycsIHVzZXJwd2QsICcxMjcuMC4wLjE6MTUyMS94ZScsIGVuY29kaW5nPSdVVEYtOCcpCgoj
IHNjaGVtYToKIyBDUkVBVEUgVEFCTEUgREJURVNUSU5HLm5jaGFydGFibGUKIyAoCiMgICAgIGlk
IE5DSEFSKDQpIFBSSU1BUlkgS0VZIE5PVCBOVUxMCiMgKTsKIwojIGluc2VydCBpbnRvIG5jaGFy
dGFibGUgdmFsdWVzICgnMScpOwoKCnByaW50KCcjYXMgdGV4dCAtIHdvcmsgYXMgZXhwZWN0ZWQn
KQpjdXIgPSBjb25uZWN0aW9uLmN1cnNvcigpCmZvciBwYXJhbSBpbiAoMSwgJzEnLCAnMSAnLCAn
MSAgICcsICcxICAgICcsIHUnMScsIHUnMSAnLCB1JzEgICAnLCB1JzEgICAgJyk6CiAgICBzdG10
ID0gInNlbGVjdCBpZCBmcm9tIGRidGVzdGluZy5uY2hhcnRhYmxlIHdoZXJlIGlkID0ge30iLmZv
cm1hdChwYXJhbSkKICAgIGZvciByb3cgaW4gY3VyLmV4ZWN1dGUoc3RtdCk6CiAgICAgICAgcHJp
bnQoJ3t9Jy5mb3JtYXQocmVwcihwYXJhbSkpLCByb3csICdmb3VuZCcpCiAgICAgICAgYnJlYWsK
ICAgIGVsc2U6CiAgICAgICAgcHJpbnQoJ3t9Jy5mb3JtYXQocmVwcihwYXJhbSkpLCAnbm90IGZv
dW5kJykKcHJpbnQoJ1xuJykKCgpwcmludCgnI3dpdGggcGFyYW1zJykKc3RtdCA9ICdzZWxlY3Qg
aWQgZnJvbSBkYnRlc3RpbmcubmNoYXJ0YWJsZSB3aGVyZSBpZCA9IDppZCcKZm9yIHBhcmFtIGlu
ICgxLCAnMScsICcxICcsICcxICAgJywgJzEgICAgJywgdScxJywgdScxICcsIHUnMSAgICcsIHUn
MSAgICAnKToKICAgIGZvciByb3cgaW4gY3VyLmV4ZWN1dGUoc3RtdCwgW3BhcmFtXSk6CiAgICAg
ICAgcHJpbnQoJ3t9Jy5mb3JtYXQocmVwcihwYXJhbSkpLCByb3csICdmb3VuZCcpCiAgICAgICAg
YnJlYWsKICAgIGVsc2U6CiAgICAgICAgcHJpbnQoJ3t9Jy5mb3JtYXQocmVwcihwYXJhbSkpLCAn
bm90IGZvdW5kJykKCnByaW50KCdcbicpCgoKcHJpbnQoJyN3aXRoIG5jaGFyIHBhcmFtcyAtIHdv
cmsgYXMgZXhwZWN0ZWQnKQp2YXIgPSBjdXIudmFyKGN4X09yYWNsZS5GSVhFRF9OQ0hBUikKc3Rt
dCA9ICdzZWxlY3QgaWQgZnJvbSBkYnRlc3RpbmcubmNoYXJ0YWJsZSB3aGVyZSBpZCA9IDppZCcK
Zm9yIHBhcmFtIGluICgnMScsICcxICcsICcxICAgJywgJzEgICAgJywgdScxJywgdScxICcsIHUn
MSAgICcsIHUnMSAgICAnKToKICAgIHZhci5zZXR2YWx1ZSgwLCBwYXJhbSkKICAgIGZvciByb3cg
aW4gY3VyLmV4ZWN1dGUoc3RtdCwgW3Zhcl0pOgogICAgICAgIHByaW50KCd7fScuZm9ybWF0KHJl
cHIocGFyYW0pKSwgcm93LCAnZm91bmQnKQogICAgICAgIGJyZWFrCiAgICBlbHNlOgogICAgICAg
IHByaW50KCd7fScuZm9ybWF0KHJlcHIocGFyYW0pKSwgJ25vdCBmb3VuZCcpCg==
--0000000000004577e70593c2044a
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline


--0000000000004577e70593c2044a
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

_______________________________________________
cx-oracle-users mailing list
cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

--0000000000004577e70593c2044a--