Re: [cx-oracle-users] Re: setinputsizes (Was: Inserting on table where is CLOB column)

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

On 5/18/05, Jani Tiainen <re...@lu...> wrote:
> Anthony Tuininga kirjoitti:
> > On 5/18/05, Jani Tiainen <re...@lu...> wrote:
> >
> >>Jani Tiainen kirjoitti:
> >>
> >>
> >>>In application code insert clause is in form:
> >>>
> >>>sql =3D "INSERT INTO FOOBAR (ID, VALUE, CLOB_VALUE) VALUES (%s, %s, %s=
)"
> >>>params =3D (1, 'foobar', 'verylongfoobar')
> >>>
> >>>This is converted to format:
> >>>
> >>>sql =3D "INSERT INTO FOOBAR (ID, VALUE, CLOB_VALUE) VALUES (:val1, :va=
l2,
> >>>:val3)"
> >>>params =3D {'val1' : 1,  'val2' : 'foobar', 'val3' : 'verylongfoobar'}
> >>>
> >>>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.
> >>>
> >>>I've tried following piece of code:
> >>>
> >>># 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
> >>>
> >>>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.
> >>>
> >>>Even I used cx_Oracle.STRING instead of None result was same.
> >>>
> >>
> >>Only occassion when abowe works is when named parameters are passed.
> >>
> >>Even following doesn't seem to work:
> >>
> >>cursor.setinputvalues([None, cx_Oracle.CLOB])
> >>cursor.execute("INSERT INTO FOOBAR(ID, CLOB_VAR) VALUES (:1, :2)",
> >>        [{'1' : 1002}, {'2' : 'longfoobartext'}])
> >
> >
> > This should be
> >
> > cursor.execute(insert_statement, [1002, 'longfoobartext'])
> >
> > In other words, you should not pass a dictionary for positional
> > arguments but a sequence instead.
>=20
> I saw that on some example and it seemed to work tough.. :)
>=20
> Well finally I got it workin (felt a little stupid after all.. :)
>=20
> But thak you very much for help and patience.

You're welcome. Glad to hear you got it working.

> Final question: Is there plans to support c-format (%s) parameters in
> execute query?

Not really. You can always use "sql_statement % args" as the statement
parameter to the execute or you can subclass cx_Oracle.Cursor and
perform this yourself -- plenty of options if you really like that
motif. Its generally not considered good form, though, since you
destroy Oracle's cursor cache which reduces performance quite a bit
when a lot of statements are being issued.

> --
>=20
> Jani Tiainen
>=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
>