cx-oracle-users

[cx-oracle-users] type object matching - BINARY

[Mario F. <mf...@zo...>]

I've a question about this behaviour:

>>> cx_Oracle.BINARY == cx_Oracle.BINARY
True
>>> cx_Oracle.BINARY == cx_Oracle.LOB
False
>>> cx_Oracle.BINARY == cx_Oracle.BLOB
False
>>> cx_Oracle.BINARY == cx_Oracle.CLOB
False

is it correct?  after reading the pep-249 at
http://www.python.org/dev/peps/pep-0249/, I would expect all of them to
check True.  otherwise, how do I check if a field is holding a binary
object in a db-api2 compatible way?

I've checked pgdb and MySQLdb and only MySQLdb recognizes a binary
object as above.

how to test:

import cx_Oracle, open a connection, associate cr to a cursor.

>>> cr.execute('create table test(pk_test integer not null primary key, v_text clob)')
>>> cr.execute('select * from test')
[<cx_Oracle.NUMBER object at 0x40344ca0>, <cx_Oracle.CLOB object at 0x40311bd0>]
>>> cr.description[1][1]
<type 'cx_Oracle.CLOB'>
>>> cr.description[1][1] == cx_Oracle.BINARY
False
>>> 

regards,
MF

Re: [cx-oracle-users] type object matching - BINARY

[M. A. W. <maw...@gm...>]

this is the code i used to get values

import cx_Oracle
con = cx_Oracle.connect("user/userpassword@xe")
cur = con.cursor()
cur.execute("select * from tab")

res = cur.fetchall()

for rec in res:
 print rec[0] ,"--->", rec[1]

Just a test Code


On 9/13/06, Mario Frasca <mf...@zo...> wrote:
>
> I've a question about this behaviour:
>
> >>> cx_Oracle.BINARY == cx_Oracle.BINARY
> True
> >>> cx_Oracle.BINARY == cx_Oracle.LOB
> False
> >>> cx_Oracle.BINARY == cx_Oracle.BLOB
> False
> >>> cx_Oracle.BINARY == cx_Oracle.CLOB
> False
>
> is it correct?  after reading the pep-249 at
> http://www.python.org/dev/peps/pep-0249/, I would expect all of them to
> check True.  otherwise, how do I check if a field is holding a binary
> object in a db-api2 compatible way?
>
> I've checked pgdb and MySQLdb and only MySQLdb recognizes a binary
> object as above.
>
> how to test:
>
> import cx_Oracle, open a connection, associate cr to a cursor.
>
> >>> cr.execute('create table test(pk_test integer not null primary key,
> v_text clob)')
> >>> cr.execute('select * from test')
> [<cx_Oracle.NUMBER object at 0x40344ca0>, <cx_Oracle.CLOB object at
> 0x40311bd0>]
> >>> cr.description[1][1]
> <type 'cx_Oracle.CLOB'>
> >>> cr.description[1][1] == cx_Oracle.BINARY
> False
> >>>
>
> regards,
> MF
>
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job
> easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] type object matching - BINARY

[Anthony T. <ant...@gm...>]

To answer your question: Oracle has three types that hold binary data
-- RAW, LONG RAW and BLOB. They are all quite different as far as
Oracle is concerned so some means of dealing with that is rather
important -- they cannot simply be the same type. If you're dealing
with Oracle you'll have to deal with this issue -- there is no
database independent way to deal with it that I am aware of. Please
feel free to educate me if necessary. :-)

In addition, Python doesn't really have a concept of "binary" data --
it stores everything in "strings". Its just that some of those
"strings" are not null terminated. :-) I've taken advantage of that
and not bothered with a "Binary()" constructor since I figured that
you can create strings in Python just fine already. Use
setinputsizes() to specify to Oracle how to interpret that data.

BTW, CLOB is not binary data but text data and LOB is simply a way of
indicating that a LOB (Large Object) indicator is present and nothing
more.

Does that answer your questions?

On 9/13/06, Mario Frasca <mf...@zo...> wrote:
> I've a question about this behaviour:
>
> >>> cx_Oracle.BINARY == cx_Oracle.BINARY
> True
> >>> cx_Oracle.BINARY == cx_Oracle.LOB
> False
> >>> cx_Oracle.BINARY == cx_Oracle.BLOB
> False
> >>> cx_Oracle.BINARY == cx_Oracle.CLOB
> False
>
> is it correct?  after reading the pep-249 at
> http://www.python.org/dev/peps/pep-0249/, I would expect all of them to
> check True.  otherwise, how do I check if a field is holding a binary
> object in a db-api2 compatible way?
>
> I've checked pgdb and MySQLdb and only MySQLdb recognizes a binary
> object as above.
>
> how to test:
>
> import cx_Oracle, open a connection, associate cr to a cursor.
>
> >>> cr.execute('create table test(pk_test integer not null primary key, v_text clob)')
> >>> cr.execute('select * from test')
> [<cx_Oracle.NUMBER object at 0x40344ca0>, <cx_Oracle.CLOB object at 0x40311bd0>]
> >>> cr.description[1][1]
> <type 'cx_Oracle.CLOB'>
> >>> cr.description[1][1] == cx_Oracle.BINARY
> False
> >>>
>
> regards,
> MF
>
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] type object matching - BINARY

[Mario F. <mf...@zo...>]

On 2006-0913 08:09:50, Anthony Tuininga wrote:
> To answer your question: Oracle has three types that hold binary
> data -- RAW, LONG RAW and BLOB. They are all quite different
> [...]  Please feel free to educate me if necessary. :-)

I'm a very bad educator! :)  I hope I will manage to explain myself
at least.

let's start from the problem I'm trying to solve...  I need to perform a
SELECT DISTINCT <fields> FROM ... and I don't want to make any
assumptions about the table...  I know that the table may contain LOBs
and on them Oracle complains that it cannot perform a comparison.  to be
safe, I want to filter out all ''BINARY'' fields, in the sense, all
those fields where comparison is not possible or would take too long a
time...  as stated in the PEP249:

BINARY
   This type object is used to describe (long) binary columns
   in a database (e.g. LONG, RAW, BLOBs).

this 'BINARY' is not a type, but a type object, something quite abstract
that is used to cathegorize the concrete types in the database into
db-api2 concepts.  back to my question, at the very least 
cx_Oracle.BINARY == cx_Oracle.BLOB
should check True...  my interpretation is that also LOBs and CLOBs are
cathegorized as BINARY...  but this is my interpretation...

thanks for your time,
Mario

-- 
... hinc sequitur, unamquamque rem naturalem tantum iuris ex natura habere,
    quantum potentiae habet ad existendum et operandum ...
  -- Baruch de Spinoza, TRACTATUS POLITICUS

Re: [cx-oracle-users] type object matching - BINARY

[D.R. B. <da...@as...>]

Hello Mario,

The pep that you refer to, http://www.python.org/dev/peps/pep-0249/
contains the answer to your question, although I'll admit that it's not
obvious.
In the pep you'll find a helper class DBAPITypeObject without an explanation
on how to use it.
A simple example hopefully explains what you'll have to do

   >>> import cx_Oracle

   >>> print DBAPITypeObject(cx_Oracle.CLOB)   == DBAPITypeObject(cx_Oracle.BINARY, cx_Oracle.BLOB, cx_Oracle.CLOB)
   True

   >>> print DBAPITypeObject(cx_Oracle.NUMBER) == DBAPITypeObject(cx_Oracle.BINARY, cx_Oracle.BLOB, cx_Oracle.CLOB)
   False

So, on the left side of the comparison you instantiate a DBAPITypeObject with
the type identifier that you obtained from the cursor description.
On the right side you instantiate a DBAPITypeObject with _all_ the type
identifiers that you think behave the same as one of the type identifiers
mentioned in the pep. In the above example, cx_Oracle.BINARY cx_Oracle.BLOB
and cx_Oracle.CLOB are all considered BINARY.
The DBAPITypeObject will make sure that the equivalence operator will
behave as you expect. Of course, to make the code independent of the
database backend you'll have to do a bit more, but this should explain how
to do it.

Good luck,

                                                   Danny


On Wed, Sep 13, 2006 at 05:07:41PM +0200, Mario Frasca wrote:
> On 2006-0913 08:09:50, Anthony Tuininga wrote:
> > To answer your question: Oracle has three types that hold binary
> > data -- RAW, LONG RAW and BLOB. They are all quite different
> > [...]  Please feel free to educate me if necessary. :-)
> 
> I'm a very bad educator! :)  I hope I will manage to explain myself
> at least.
> 
> let's start from the problem I'm trying to solve...  I need to perform a
> SELECT DISTINCT <fields> FROM ... and I don't want to make any
> assumptions about the table...  I know that the table may contain LOBs
> and on them Oracle complains that it cannot perform a comparison.  to be
> safe, I want to filter out all ''BINARY'' fields, in the sense, all
> those fields where comparison is not possible or would take too long a
> time...  as stated in the PEP249:
> 
> BINARY
>    This type object is used to describe (long) binary columns
>    in a database (e.g. LONG, RAW, BLOBs).
> 
> this 'BINARY' is not a type, but a type object, something quite abstract
> that is used to cathegorize the concrete types in the database into
> db-api2 concepts.  back to my question, at the very least 
> cx_Oracle.BINARY == cx_Oracle.BLOB
> should check True...  my interpretation is that also LOBs and CLOBs are
> cathegorized as BINARY...  but this is my interpretation...
> 
> thanks for your time,
> Mario
> 
> -- 
> ... hinc sequitur, unamquamque rem naturalem tantum iuris ex natura habere,
>     quantum potentiae habet ad existendum et operandum ...
>   -- Baruch de Spinoza, TRACTATUS POLITICUS
> 
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] type object matching - BINARY

[Mario F. <mf...@zo...>]

Hi Danny,

... thinking a bit...  but what you describe is what I would expect to
find already in cx_Oracle...  I expected BINARY to have been defined
as DBAPITypeObject(cx_Oracle.BLOB, cx_Oracle.LOB, cx_Oracle.CLOB) (or
something similar, maybe including LONG_STRING?)...  I would even have a
look at the sources, but the Python/C bindings still scare me a bit away.

if there was a cx_Oracle.py source, I would have added the following
line:

BINARY = DBAPITypeObject(BLOB, LOB, CLOB)

made some tests, submitted a patch.

as I said, I'm a very bad educator...

regards,
Mario

-- 
 je vais m'envoyer une lettre de felicitation.
 Des felicitations, ça fait toujours plaisir, 
 surtout de la part de quelqu'un qu'on aime bien.

Re: [cx-oracle-users] type object matching - BINARY

[D.R. B. <da...@as...>]

Hoi Mario,

You are right, but I'm happy to leave the C implementation to Anthony.
Personally, I very much dislike the whole idea of using the equivalence
operator on types in the way suggested in the pep. For example

   BINARY =3D=3D CLOB

To me BINARY and CLOB are not equivalent and never will be.
More natural would be

   issubclass(CLOB, BINARY)

(Now you now what C and B in the help() for issubclass() stand for ;)
but even so, it's usually better to stay as far as possible from discussi=
ons
on such matters.

Cheers,

                                                   Danny

On Wed, Sep 13, 2006 at 05:41:30PM +0200, Mario Frasca wrote:
> Hi Danny,
>=20
> ... thinking a bit...  but what you describe is what I would expect to
> find already in cx_Oracle...  I expected BINARY to have been defined
> as DBAPITypeObject(cx_Oracle.BLOB, cx_Oracle.LOB, cx_Oracle.CLOB) (or
> something similar, maybe including LONG_STRING?)...  I would even have =
a
> look at the sources, but the Python/C bindings still scare me a bit awa=
y.
>=20
> if there was a cx_Oracle.py source, I would have added the following
> line:
>=20
> BINARY =3D DBAPITypeObject(BLOB, LOB, CLOB)
>=20
> made some tests, submitted a patch.
>=20
> as I said, I'm a very bad educator...
>=20
> regards,
> Mario
>=20
> --=20
>  je vais m'envoyer une lettre de felicitation.
>  Des felicitations, =E7a fait toujours plaisir,=20
>  surtout de la part de quelqu'un qu'on aime bien.
>=20
> -----------------------------------------------------------------------=
--
> Using Tomcat but need to do more? Need to support web services, securit=
y?
> Get stuff done quickly with pre-integrated technology to make your job =
easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geron=
imo
> http://sel.as-us.falkag.net/sel?cmd=3Dlnk&kid=3D120709&bid=3D263057&dat=
=3D121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] type object matching - BINARY

[Anthony T. <ant...@gm...>]

The PEP is very vague on the types and their handling. It specifies
only BINARY and then proceeds to indicate that LONG (long strings in
Oracle) should be based on this as well?? That doesn't make any sense
at all. Note that CLOB would fall into "BINARY" as far as the PEP is
concerned but as far as Oracle is concerned that makes no sense. I've
taken the approach that the DB API PEP should be followed as much as
possible -- except where following it would make the interface to
Oracle difficult to follow/understand for anyone who understands
Oracle. In addition, binary data is rarely used in databases so using
setinputsizes() to specify the type directly works quite well and
doesn't involve (IMHO) brain damage. :-)

I'd be happy to suggest a change to the PEP that would work for other
interfaces as well -- I know there are a number of interfaces that
deal with input/output in a much more controlled fashion but none of
this has gone into the PEP. I subscribe to the db-sig mailing list as
well and there is significant resistance to make any changes at all.

If you have any further suggestions I'd be happy to entertain them. I
won't be adding anything along the lines of DBAPITypeObject() as it
doesn't make any sense -- see above. If that is something you really
care about you can always create your own Python wrapper.... :-)

On 9/13/06, D.R.   Boxhoorn <da...@as...> wrote:
>
> Hoi Mario,
>
> You are right, but I'm happy to leave the C implementation to Anthony.
> Personally, I very much dislike the whole idea of using the equivalence
> operator on types in the way suggested in the pep. For example
>
>    BINARY =3D=3D CLOB
>
> To me BINARY and CLOB are not equivalent and never will be.
> More natural would be
>
>    issubclass(CLOB, BINARY)
>
> (Now you now what C and B in the help() for issubclass() stand for ;)
> but even so, it's usually better to stay as far as possible from discussi=
ons
> on such matters.
>
> Cheers,
>
>                                                    Danny
>
> On Wed, Sep 13, 2006 at 05:41:30PM +0200, Mario Frasca wrote:
> > Hi Danny,
> >
> > ... thinking a bit...  but what you describe is what I would expect to
> > find already in cx_Oracle...  I expected BINARY to have been defined
> > as DBAPITypeObject(cx_Oracle.BLOB, cx_Oracle.LOB, cx_Oracle.CLOB) (or
> > something similar, maybe including LONG_STRING?)...  I would even have =
a
> > look at the sources, but the Python/C bindings still scare me a bit awa=
y.
> >
> > if there was a cx_Oracle.py source, I would have added the following
> > line:
> >
> > BINARY =3D DBAPITypeObject(BLOB, LOB, CLOB)
> >
> > made some tests, submitted a patch.
> >
> > as I said, I'm a very bad educator...
> >
> > regards,
> > Mario
> >
> > --
> >  je vais m'envoyer une lettre de felicitation.
> >  Des felicitations, =E7a fait toujours plaisir,
> >  surtout de la part de quelqu'un qu'on aime bien.
> >
> > -----------------------------------------------------------------------=
--
> > Using Tomcat but need to do more? Need to support web services, securit=
y?
> > Get stuff done quickly with pre-integrated technology to make your job =
easier
> > Download IBM WebSphere Application Server v.1.0.1 based on Apache Geron=
imo
> > http://sel.as-us.falkag.net/sel?cmd=3Dlnk&kid=3D120709&bid=3D263057&dat=
=3D121642
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job ea=
sier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronim=
o
> http://sel.as-us.falkag.net/sel?cmd=3Dlnk&kid=3D120709&bid=3D263057&dat=
=3D121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] type object matching - BINARY

[Mario F. <mf...@zo...>]

On 2006-0913 11:33:04, Anthony Tuininga wrote:
> [...] I won't be adding anything along the lines of
> DBAPITypeObject() as it doesn't make any sense -- see above. If
> that is something you really care about you can always create
> your own Python wrapper.... :-)

pity, I don't like needing wrappers, I prefer using things out of the box
(sounds like the classical lazy developer), even if that involves needing
to patch them.  I also don't like the style chosen in the db-api2:
testing for equality and implementing it as a test for inclusion is
quite confusing, to say the least.  subclassing seems more appropriate.
as far as I understood, it has been implemented like this because this
way you can define a CLOB to fall into a BINARY but also into a STRING...
you can achieve this also by multiple inheritance, but maybe it has been
considered overkill, less efficient, I don't know, just making hypoteses.

I had seen there was some movement on db-sig and a possible new version
of the db-api agreements.  until then, my library will have to cope with
so many different interpretations of the directives...  hope this better
db-api3 comes soon!

greetings,
Mario

-- 
Nie udało się wysłać wiadomości pod numer: +4851482xxxx
Użytkownik nie ma aktywnej usługi SMS z Internetu, SMS nie został wysłany

Re: [cx-oracle-users] type object matching - BINARY

[Anthony T. <ant...@gm...>]

On 9/13/06, Mario Frasca <mf...@zo...> wrote:
> On 2006-0913 11:33:04, Anthony Tuininga wrote:
> > [...] I won't be adding anything along the lines of
> > DBAPITypeObject() as it doesn't make any sense -- see above. If
> > that is something you really care about you can always create
> > your own Python wrapper.... :-)
>
> pity, I don't like needing wrappers, I prefer using things out of the box
> (sounds like the classical lazy developer), even if that involves needing
> to patch them.  I also don't like the style chosen in the db-api2:
> testing for equality and implementing it as a test for inclusion is
> quite confusing, to say the least.  subclassing seems more appropriate.
> as far as I understood, it has been implemented like this because this
> way you can define a CLOB to fall into a BINARY but also into a STRING...
> you can achieve this also by multiple inheritance, but maybe it has been
> considered overkill, less efficient, I don't know, just making hypoteses.

I don't like wrappers either -- but the great goal of being able to
simply drop in a database driver just isn't there. There are too many
discrepancies and the DB-API does not cover them (and doesn't really
try to, either). Its been great for making the interfaces __similar__
-- and that's good enough for me, for now.

> I had seen there was some movement on db-sig and a possible new version
> of the db-api agreements.  until then, my library will have to cope with
> so many different interpretations of the directives...  hope this better
> db-api3 comes soon!

Agreed.

> greetings,
> Mario