Re: [cx-oracle-users] Re: Inserting on table where is CLOB column

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

On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> Anthony Tuininga kirjoitti:
> > On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> >
> >>Anthony Tuininga kirjoitti:
> >>
> >>>On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> >>>
> >>>
> >>>>>BTW, if you __really__ have no control, you can always subclass
> >>>>>Connection and Cursor and do whatever you need to do. Subclassing ha=
s
> >>>>>been quite convenient and I use it myself for a number of situations
> >>>>>where the code is used by multiple database adapters.
> >>>>
> >>>>Any pointers in web (example would be nice) since I'm just becoming
> >>>>friend of Python and I might be trying to do things wrong here...
> >>>
> >>>
> >>>Recent e-mails to this list have had some examples. Subclassing
> >>>cx_Oracle.Connection and cx_Oracle.Cursor are the same as sublcassing
> >>>any other class in Python -- not very helpful if you haven't done much
> >>>of it yourself yet, I guess. :-)
> >>
> >>Well I have something like following:
> >>
> >>query =3D "insert into foobar (id, name) values (:1, :2)"
> >>values =3D {'1' : 123, '2' : 'Mr. Dumb'
> >>cursor.setinputsizes({'2' : cx_Oracle.CLOB})
> >>cursor.execute(query, values)
> >>
> >>But I'm getting error:
> >>Variable_TypeByPythonType(): unhandled data type
> >>
> >>What I'm doing wrong..?
> >
> >
> > Oracle uses :1, :2 for __positional__ arguments and :fred and :george
> > for __named__ arguments. cx_Oracle expects a dictionary for named
> > arguments and a sequence for positional arguments. So instead of
> > passing a dictionary as the arguments, pass a list instead and that
> > should solve your problems. In addition, setinputsizes() uses the
> > *args and **kwargs notation so you should specify the following:
> >
> > cursor.setinputsizes([None, cx_Oracle.CLOB]) OR
>=20
> I got this never working... It just says "cx_Oracle.ProgrammingError:
> number of elements must be an integer"

What version of cx_Oracle are you using? The above will only work with
cx_Oracle 4.1.

> > cursor.setinputsizes(value =3D cx_Oracle.CLOB)
> >
> > and reference :value in your statement. Make sense?
>=20
> Yes, but... I think I pasted too short description of my problem.
>=20
> In application code insert clause is in form:
>=20
> sql =3D "INSERT INTO FOOBAR (ID, VALUE, CLOB_VALUE) VALUES (%s, %s, %s)"
> params =3D (1, 'foobar', 'verylongfoobar')
>=20
> This is converted to format:
>=20
> sql =3D "INSERT INTO FOOBAR (ID, VALUE, CLOB_VALUE) VALUES (:val1, :val2,
> :val3)"
> params =3D {'val1' : 1,  'val2' : 'foobar', 'val3' : 'verylongfoobar'}
>=20
> Now this works as long as string I'm trying to insert doesn't exceed
> 4000 chars. If that happens it tries insert LONG to CLOB column.
>=20
> I've tried following piece of code:
>=20
> # Adjust inputsizes..
> inputsizes =3D {}
> for k, v in params.items():
>         if type(v) is str and len(v) > 4000:
>                 inputsizes[k] =3D cx_Oracle.CLOB
>         else:
>                 inputsizes[k] =3D None
>=20
> cursor.setinputsizes(inputsizes)

You want to use this instead:

cursor.setinputsizes(**inputsizes)

> And I always endup having "cx_Oracle.NotSupportedError:
> Variable_TypeByPythonType(): unhandled data type" error. Even I remove
> else-branch final result is still same.

Yes, see above. The method is expecting keyword arguments or a list of
arguments (when using cx_Oracle 4.1). So if you build your dictionary
in this fashion you need to use the ** syntax to pass it through as
keyword arguments.

> Even I used cx_Oracle.STRING instead of None result was same.

Certainly. BTW, you don't need to specify inputsizes except to tell
the driver about things it can't figure out on its own. In the above
code you can eliminate the else branch completely.

> --
>=20
> Jani Tiainen
>=20
>=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_id=3D7412&alloc_id=3D16344&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>