Re: Filtering on char/nchar

--===============8682892700484893827==
Content-Type: multipart/alternative; boundary="0000000000002deb160593c88553"

--0000000000002deb160593c88553
Content-Type: text/plain; charset="UTF-8"

Hi,

This is expected behaviour. The "CHAR" type fields all expect space-padded
values. cx_Oracle will bind as VARCHAR2 for string values (or NVARCHAR2 for
Python 2.7 unicode values), however. In that case, you need to specify the
right number of spaces for the comparison to be successful. If you use CHAR
(or NCHAR) specifically, Oracle automatically adds the necessary spaces. In
general, you should only use CHAR or NCHAR for fixed width values -- or you
will have to pad with the right number of spaces yourself or specifically
use the CHAR or NCHAR types.

Anthony

On Mon, Sep 30, 2019 at 3:40 AM kotofos <[email protected]> wrote:

> 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
> _______________________________________________
> cx-oracle-users mailing list
> cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

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

<div dir=3D"ltr">Hi,<div><br></div><div>This is expected behaviour. The &qu=
ot;CHAR&quot; type fields all expect space-padded values. cx_Oracle will bi=
nd as VARCHAR2 for string values (or NVARCHAR2 for Python 2.7 unicode value=
s), however. In that case, you need to specify=C2=A0the right number of spa=
ces=C2=A0for the comparison to be successful. If you use CHAR (or NCHAR) sp=
ecifically, Oracle automatically adds the necessary spaces. In general, you=
 should only use CHAR or NCHAR for fixed width values -- or you will have t=
o pad with the right number of spaces yourself or specifically use the CHAR=
 or NCHAR types.</div><div><br></div><div>Anthony</div></div><br><div class=
=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Mon, Sep 30, 2019=
 at 3:40 AM kotofos &lt;<a href=3D"mailto:[email protected]" target=3D"_bla=
nk">[email protected]</a>&gt; wrote:<br></div><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,20=
4);padding-left:1ex"><div dir=3D"ltr">Hello,<br><br>I have a question about=
 char/nchar and binding parameters type.<br><br>We have tables which have c=
har and nchar PK columns. When filtering with bound parameters on these col=
umns, it is required to put in exact value with padding in where clause. Th=
ough, when the same is done as text, without params filtering works as expe=
cted.<br><br>Examples:<br>CREATE TABLE DBTESTING.nchartable<br>(<br>=C2=A0 =
=C2=A0 id NCHAR(4) PRIMARY KEY NOT NULL<br>);<br>insert into nchartable val=
ues (&#39;1&#39;);<br><br>cur.execute(&quot;select * from nchartable where =
id =3D &#39;1&#39;&quot;) =C2=A0found<br>cur.execute(&quot;select * from nc=
hartable where id =3D :id&quot;, [&#39;1 =C2=A0 &#39;]) found<br>cur.execut=
e(&quot;select * from nchartable where id =3D :id&quot;, [&#39;1&#39;]) not=
 found<br><br>After debugging I found, that where clause fails because the =
parameter is nvarchar2. When I manually set the type to nchar all cases abo=
ve works. See attached code.<br>Is this behavior intended or it is a bug?<b=
r>How to correctly search on nchar column without adding padding in a query=
 and preferably without specifying type manually?<br><br>Checked on cx_orac=
le 7.2.2</div>
_______________________________________________<br>
cx-oracle-users mailing list<br>
<a href=3D"mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org" target=3D"_blank">=
cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org</a><br>
<a href=3D"https://lists.sourceforge.net/lists/listinfo/cx-oracle-users" re=
l=3D"noreferrer" target=3D"_blank">https://lists.sourceforge.net/lists/list=
info/cx-oracle-users</a><br>
</blockquote></div>

--0000000000002deb160593c88553--


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


--===============8682892700484893827==
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

--===============8682892700484893827==--