cx-oracle-users

[cx-oracle-users] set{input,output}sizes() (changed subject)

[Guido v. R. <gu...@el...>]

> setinputsizes() is used to define what type (and size) of data is
> being bound to the cursor. In most cases cx_Oracle (and other database
> access modules) can guess the type from the type of the Python value
> that is bound but the Python None value (representing null in database
> terms) doesn't have any type that corresponds to any database type.
> Thus, if you want to bind a NULL or anything else (like CLOB/BLOB)
> where the database type and size cannot be implied from the Python
> type and size you want to use setinputsizes().
>=20
> setoutputsizes() is for defining the size of data that is being
> fetched from the database. In particular it is used for variable
> length data (like long and long raw in Oracle) where the size cannot
> be known by retrieving the information from the database. If you never
> use long or long raw (and Oracle recommends that you use CLOB and BLOB
> instead) then you never need to know about this function.
>=20
> Does that help?? How many bonus points do I get?? :-)

Sortof.  I'm surprised that NULL (which is supported in SQL) is unknown
at this level, which makes me wonder if I understand any of the rest!

I guess I'm so comfortable with the abstractions that SQL and Python
provide that I have a hard time understanding the precise nature of the
hoops that you have to jump through to tie the two together; for
example, when inserting a 100K-long string into a table column whose
type is CLOB, why would I have to say that it's a CLOB again?

--Guido van Rossum (home page: http://www.python.org/~guido)

Re: [cx-oracle-users] set{input,output}sizes() (changed subject)

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

On 5/17/05, Guido van Rossum <gu...@el...> wrote:
> > setinputsizes() is used to define what type (and size) of data is
> > being bound to the cursor. In most cases cx_Oracle (and other database
> > access modules) can guess the type from the type of the Python value
> > that is bound but the Python None value (representing null in database
> > terms) doesn't have any type that corresponds to any database type.
> > Thus, if you want to bind a NULL or anything else (like CLOB/BLOB)
> > where the database type and size cannot be implied from the Python
> > type and size you want to use setinputsizes().
> >
> > setoutputsizes() is for defining the size of data that is being
> > fetched from the database. In particular it is used for variable
> > length data (like long and long raw in Oracle) where the size cannot
> > be known by retrieving the information from the database. If you never
> > use long or long raw (and Oracle recommends that you use CLOB and BLOB
> > instead) then you never need to know about this function.
> >
> > Does that help?? How many bonus points do I get?? :-)
>=20
> Sortof.  I'm surprised that NULL (which is supported in SQL) is unknown
> at this level, which makes me wonder if I understand any of the rest!

Null in database speak still has a type. In other words, there is a
difference between a null number and a null string and a null date.
Python does not have such a distinction so cx_Oracle simply assumes
string. In many cases this won't be a problem even if the underlying
data type is number since Oracle implicitly converts data types but in
other cases it really matters so you need to specify.

> I guess I'm so comfortable with the abstractions that SQL and Python
> provide that I have a hard time understanding the precise nature of the
> hoops that you have to jump through to tie the two together; for

Interfaces are always interesting since the viewpoints are often
considerably different. Most times the interface is seamless but there
are a few instances where the database viewpoint pokes out. :-) Any
suggestions on how to improve this are welcome, of course!

> example, when inserting a 100K-long string into a table column whose
> type is CLOB, why would I have to say that it's a CLOB again?

Any string longer than 4000 characters cannot be represented as
varchar2 but there are several options: long, long raw, CLOB, BLOB so
which one gets chosen? At the moment cx_Oracle does not guess at all
and simply allows the error to be propagated. Again, suggestions are
welcome!

> --Guido van Rossum (home page: http://www.python.org/~guido)
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_idt12&alloc_id=16344&opclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] Re: set{input,output}sizes() (changed subject)

[Jani T. <re...@lu...>]

Anthony Tuininga kirjoitti:
> On 5/17/05, Guido van Rossum <gu...@el...> wrote:
> 
>>example, when inserting a 100K-long string into a table column whose
>>type is CLOB, why would I have to say that it's a CLOB again?
> 
> 
> Any string longer than 4000 characters cannot be represented as
> varchar2 but there are several options: long, long raw, CLOB, BLOB so
> which one gets chosen? At the moment cx_Oracle does not guess at all
> and simply allows the error to be propagated. Again, suggestions are
> welcome!

Actually I found out that it really tries > 4k chars to insert as LONG . 
Why , I've no idea (happens at least with cx_Oracle 4.1).

Well problem is that LONG is deprecated type, and you can have only one 
LONG column in table. So I think if string is > 4k it would be safe to 
assume it as CLOB. Of course this could be connection wide setting 
(outside DB API) to set default type for guess..

-- 

Jani Tiainen