cx-oracle-users

[cx-oracle-users] Feedback desired on types and binding

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

The methods cursor.setinputsizes(), cursor.var() and cursor.callfunc()
accept types to indicate what sort of bind variable is desired. These
types are generally the types defined by the DB API but I have added a
number of additional ones to support data types supported by Oracle
but not covered by the DB API. There are a number of cases where the
type indicated by Oracle covers multiple Python types. This is seen
primarily in two places (that I am aware of at the moment). The first
is the desire to return integers from callfunc() and the second is the
desire to return unicode strings directly from cx_Oracle (rather than
have to decode them yourself). A couple of patches have been sent to
me to do both of these things by adding another set of high-level
types. The problem with this is that it further confuses the already
confused type hierarchy. So far all of the types are types from the
Oracle perspective only and I'd like to keep it that way. So, I have
an alternative suggestion which I'd like some feedback on: allow
setinputsizes(), var() and calfunc() to accept Python types and use
these to tweak the type of Python object that will be returned. In
other words

cursor.callfunc("SomeFunction", int, (parm1, parm2))

or

cursor.setinputsizes(someparm = int)

or

cursor.var(int)

In all of the above cases a NUMBER bind variable would be created but
whenever the value of the variable is acquired the type of object
returned would be an integer. The same thing would occur with unicode.

Does this seem reasonable to you? Any comments appreciated. Thank you.

Anthony Tuininga

Re: [cx-oracle-users] Feedback desired on types and binding

[Amaury F. d'A. <ama...@gm...>]

Anthony Tuininga wrote:
> The methods cursor.setinputsizes(), cursor.var() and cursor.callfunc()
> accept types to indicate what sort of bind variable is desired. These
> types are generally the types defined by the DB API but I have added a
> number of additional ones to support data types supported by Oracle
> but not covered by the DB API. There are a number of cases where the
> type indicated by Oracle covers multiple Python types. This is seen
> primarily in two places (that I am aware of at the moment). The first
> is the desire to return integers from callfunc() and the second is the
> desire to return unicode strings directly from cx_Oracle (rather than
> have to decode them yourself). A couple of patches have been sent to
> me to do both of these things by adding another set of high-level
> types. The problem with this is that it further confuses the already
> confused type hierarchy. So far all of the types are types from the
> Oracle perspective only and I'd like to keep it that way. So, I have
> an alternative suggestion which I'd like some feedback on: allow
> setinputsizes(), var() and calfunc() to accept Python types and use
> these to tweak the type of Python object that will be returned. In
> other words
>
> cursor.callfunc("SomeFunction", int, (parm1, parm2))
>
> or
>
> cursor.setinputsizes(someparm = int)
>
> or
>
> cursor.var(int)
>
> In all of the above cases a NUMBER bind variable would be created but
> whenever the value of the variable is acquired the type of object
> returned would be an integer. The same thing would occur with unicode.
>
> Does this seem reasonable to you? Any comments appreciated. Thank you.

+1.
Most of the time, user code knows the datatype used by the database,
and the desired python type, but doesn't want to care about the type
of the intermediate OCI variable, as long as no information is lost.

BTW, is there a corresponding python type for LOB locators? file? StringIO?
It may be necessary because cx_Oracle.STRING seems to be limited to 32K.

-- 
Amaury Forgeot d'Arc

Re: [cx-oracle-users] Feedback desired on types and binding

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

On 10/12/06, Amaury Forgeot d'Arc <ama...@gm...> wrote:
> Anthony Tuininga wrote:
> > The methods cursor.setinputsizes(), cursor.var() and cursor.callfunc()
> > accept types to indicate what sort of bind variable is desired. These
> > types are generally the types defined by the DB API but I have added a
> > number of additional ones to support data types supported by Oracle
> > but not covered by the DB API. There are a number of cases where the
> > type indicated by Oracle covers multiple Python types. This is seen
> > primarily in two places (that I am aware of at the moment). The first
> > is the desire to return integers from callfunc() and the second is the
> > desire to return unicode strings directly from cx_Oracle (rather than
> > have to decode them yourself). A couple of patches have been sent to
> > me to do both of these things by adding another set of high-level
> > types. The problem with this is that it further confuses the already
> > confused type hierarchy. So far all of the types are types from the
> > Oracle perspective only and I'd like to keep it that way. So, I have
> > an alternative suggestion which I'd like some feedback on: allow
> > setinputsizes(), var() and calfunc() to accept Python types and use
> > these to tweak the type of Python object that will be returned. In
> > other words
> >
> > cursor.callfunc("SomeFunction", int, (parm1, parm2))
> >
> > or
> >
> > cursor.setinputsizes(someparm = int)
> >
> > or
> >
> > cursor.var(int)
> >
> > In all of the above cases a NUMBER bind variable would be created but
> > whenever the value of the variable is acquired the type of object
> > returned would be an integer. The same thing would occur with unicode.
> >
> > Does this seem reasonable to you? Any comments appreciated. Thank you.
>
> +1.
> Most of the time, user code knows the datatype used by the database,
> and the desired python type, but doesn't want to care about the type
> of the intermediate OCI variable, as long as no information is lost.

Thank you. From this and the other comments it would appear this
change is supported. I'll see if I can make these changes for the next
release.

> BTW, is there a corresponding python type for LOB locators? file? StringIO?
> It may be necessary because cx_Oracle.STRING seems to be limited to 32K.

The corresponding Python type for LOB locators is str -- for those you
will have to use cx_Oracle.BLOB or cx_Oracle.CLOB as appropriate. Yes,
cx_Oracle.STRING (Oracle type of string) is limited to 32K so if you
need more you have to use cx_Oracle.CLOB.

> --
> Amaury Forgeot d'Arc
>
> -------------------------------------------------------------------------
> 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
>