Thread: [cx-oracle-users] Inserting on table where is CLOB column
Brought to you by:
atuining
Menu
▾
▴
cx-oracle-users
|
From: Jani T. <re...@lu...> - 2005-05-17 17:13:12
|
When I'm trying to insert long text in CLOB column I get error ORA-01461. Something like LONG value can be only to be bind in LONG column. It should be CLOB instead of LONG. Is there way to get around this problem? When value is less than 4000 chars inserts goes well, but after breaking barrier problem occurs. Using cx_Oracle 4.1 with Python 2.3, Windows XP, Oracle 10g -- Jani Tiainen |
|
From: Charl P. B. <cp...@gm...> - 2005-05-17 18:18:56
|
On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> When I'm trying to insert long text in CLOB column I get error
> ORA-01461. Something like LONG value can be only to be bind in LONG colum=
n.
>=20
> It should be CLOB instead of LONG. Is there way to get around this proble=
m?
>=20
> When value is less than 4000 chars inserts goes well, but after breaking
> barrier problem occurs.
>=20
> Using cx_Oracle 4.1 with Python 2.3, Windows XP, Oracle 10g
It looks like you have to use bind variables. This is from an example
Anthony posted on this list to answer one of my questions:
connection =3D cx_Oracle.Connection("user/pw@tns")
cursor =3D connection.cursor()
cursor.setinputsizes(value =3D cx_Oracle.CLOB)
cursor.execute("insert into table values (:value)",
value =3D "A very long string")
HTH,
Charl
--=20
charl p. botha http://cpbotha.net/ http://visualisation.tudelft.nl/
|
|
From: Jani T. <re...@lu...> - 2005-05-17 18:49:57
|
Charl P. Botha kirjoitti:
> On 5/17/05, Jani Tiainen <re...@lu...> wrote:
>
>>When I'm trying to insert long text in CLOB column I get error
>>ORA-01461. Something like LONG value can be only to be bind in LONG column.
>>
>>It should be CLOB instead of LONG. Is there way to get around this problem?
>>
>>When value is less than 4000 chars inserts goes well, but after breaking
>>barrier problem occurs.
>>
>>Using cx_Oracle 4.1 with Python 2.3, Windows XP, Oracle 10g
>
>
> It looks like you have to use bind variables. This is from an example
> Anthony posted on this list to answer one of my questions:
>
> connection = cx_Oracle.Connection("user/pw@tns")
> cursor = connection.cursor()
> cursor.setinputsizes(value = cx_Oracle.CLOB)
> cursor.execute("insert into table values (:value)",
> value = "A very long string")
Didn't saw that in list. It would solve my problem if I knew queries
before, but...
Problem is that I haven't got control over bindings since code is
basically just code below:
def runquery(cursor, query, args):
return cursor.execute(query, args)
Any ideas?
--
Jani Tiainen
|
|
From: Anthony T. <ant...@gm...> - 2005-05-17 19:26:51
|
On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> Charl P. Botha kirjoitti:
> > On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> >
> >>When I'm trying to insert long text in CLOB column I get error
> >>ORA-01461. Something like LONG value can be only to be bind in LONG col=
umn.
> >>
> >>It should be CLOB instead of LONG. Is there way to get around this prob=
lem?
> >>
> >>When value is less than 4000 chars inserts goes well, but after breakin=
g
> >>barrier problem occurs.
> >>
> >>Using cx_Oracle 4.1 with Python 2.3, Windows XP, Oracle 10g
> >
> >
> > It looks like you have to use bind variables. This is from an example
> > Anthony posted on this list to answer one of my questions:
> >
> > connection =3D cx_Oracle.Connection("user/pw@tns")
> > cursor =3D connection.cursor()
> > cursor.setinputsizes(value =3D cx_Oracle.CLOB)
> > cursor.execute("insert into table values (:value)",
> > value =3D "A very long string")
>=20
> Didn't saw that in list. It would solve my problem if I knew queries
> before, but...
>=20
> Problem is that I haven't got control over bindings since code is
> basically just code below:
>=20
> def runquery(cursor, query, args):
> return cursor.execute(query, args)
The args __are__ the bind variables so you definitely have control.
You simply have to make sure you call cursor.setinputsizes() before
calling runquery().
> Any ideas?
>=20
> --
>=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
>
|
|
From: Jani T. <re...@lu...> - 2005-05-17 19:46:37
|
Anthony Tuininga kirjoitti:
> On 5/17/05, Jani Tiainen <re...@lu...> wrote:
>
>>Charl P. Botha kirjoitti:
>>
>>>On 5/17/05, Jani Tiainen <re...@lu...> wrote:
>>>
>>>
>>>>When I'm trying to insert long text in CLOB column I get error
>>>>ORA-01461. Something like LONG value can be only to be bind in LONG column.
>>>>
>>>>It should be CLOB instead of LONG. Is there way to get around this problem?
>>>>
>>>>When value is less than 4000 chars inserts goes well, but after breaking
>>>>barrier problem occurs.
>>>>
>>>>Using cx_Oracle 4.1 with Python 2.3, Windows XP, Oracle 10g
>>>
>>>
>>>It looks like you have to use bind variables. This is from an example
>>>Anthony posted on this list to answer one of my questions:
>>>
>>>connection = cx_Oracle.Connection("user/pw@tns")
>>>cursor = connection.cursor()
>>>cursor.setinputsizes(value = cx_Oracle.CLOB)
>>>cursor.execute("insert into table values (:value)",
>>> value = "A very long string")
>>
>>Didn't saw that in list. It would solve my problem if I knew queries
>>before, but...
>>
>>Problem is that I haven't got control over bindings since code is
>>basically just code below:
>>
>>def runquery(cursor, query, args):
>> return cursor.execute(query, args)
>
>
> The args __are__ the bind variables so you definitely have control.
> You simply have to make sure you call cursor.setinputsizes() before
> calling runquery().
Well, they are, but I don't have control over them.. :)
Since same call in code is used for different database backends (like
Postgres)
This is somewhat possible to fix in current situation since I know that
strings that are > 4k must be clobs...
--
Jani Tiainen
|
|
From: Anthony T. <ant...@gm...> - 2005-05-17 19:53:22
|
On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> Anthony Tuininga kirjoitti:
> > On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> >
> >>Charl P. Botha kirjoitti:
> >>
> >>>On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> >>>
> >>>
> >>>>When I'm trying to insert long text in CLOB column I get error
> >>>>ORA-01461. Something like LONG value can be only to be bind in LONG c=
olumn.
> >>>>
> >>>>It should be CLOB instead of LONG. Is there way to get around this pr=
oblem?
> >>>>
> >>>>When value is less than 4000 chars inserts goes well, but after break=
ing
> >>>>barrier problem occurs.
> >>>>
> >>>>Using cx_Oracle 4.1 with Python 2.3, Windows XP, Oracle 10g
> >>>
> >>>
> >>>It looks like you have to use bind variables. This is from an example
> >>>Anthony posted on this list to answer one of my questions:
> >>>
> >>>connection =3D cx_Oracle.Connection("user/pw@tns")
> >>>cursor =3D connection.cursor()
> >>>cursor.setinputsizes(value =3D cx_Oracle.CLOB)
> >>>cursor.execute("insert into table values (:value)",
> >>> value =3D "A very long string")
> >>
> >>Didn't saw that in list. It would solve my problem if I knew queries
> >>before, but...
> >>
> >>Problem is that I haven't got control over bindings since code is
> >>basically just code below:
> >>
> >>def runquery(cursor, query, args):
> >> return cursor.execute(query, args)
> >
> >
> > The args __are__ the bind variables so you definitely have control.
> > You simply have to make sure you call cursor.setinputsizes() before
> > calling runquery().
>=20
> Well, they are, but I don't have control over them.. :)
>=20
> Since same call in code is used for different database backends (like
> Postgres)
Ah, I see. That does make life a little more difficult.
> This is somewhat possible to fix in current situation since I know that
> strings that are > 4k must be clobs...
Not necessarily in general but that could be true in your application.
You also have long, long raw and BLOB to deal with. If your
application doesn't use them you can make this assumption.
BTW, if you __really__ have no control, you can always subclass
Connection and Cursor and do whatever you need to do. Subclassing has
been quite convenient and I use it myself for a number of situations
where the code is used by multiple database adapters.
> --
>=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
>
|
|
From: Jani T. <re...@lu...> - 2005-05-17 20:28:37
|
Anthony Tuininga kirjoitti:
> On 5/17/05, Jani Tiainen <re...@lu...> wrote:
>
>>Anthony Tuininga kirjoitti:
>>
>>>On 5/17/05, Jani Tiainen <re...@lu...> wrote:
>>>
>>>
>>>>Charl P. Botha kirjoitti:
>>>>
>>>>
>>>>>On 5/17/05, Jani Tiainen <re...@lu...> wrote:
>>>>>
>>>>>
>>>>>
>>>>>>When I'm trying to insert long text in CLOB column I get error
>>>>>>ORA-01461. Something like LONG value can be only to be bind in LONG column.
>>>>>>
>>>>>>It should be CLOB instead of LONG. Is there way to get around this problem?
>>>>>>
>>>>>>When value is less than 4000 chars inserts goes well, but after breaking
>>>>>>barrier problem occurs.
>>>>>>
>>>>>>Using cx_Oracle 4.1 with Python 2.3, Windows XP, Oracle 10g
>>>>>
>>>>>
>>>>>It looks like you have to use bind variables. This is from an example
>>>>>Anthony posted on this list to answer one of my questions:
>>>>>
>>>>>connection = cx_Oracle.Connection("user/pw@tns")
>>>>>cursor = connection.cursor()
>>>>>cursor.setinputsizes(value = cx_Oracle.CLOB)
>>>>>cursor.execute("insert into table values (:value)",
>>>>> value = "A very long string")
>>>>
>>>>Didn't saw that in list. It would solve my problem if I knew queries
>>>>before, but...
>>>>
>>>>Problem is that I haven't got control over bindings since code is
>>>>basically just code below:
>>>>
>>>>def runquery(cursor, query, args):
>>>> return cursor.execute(query, args)
>>>
>>>
>>>The args __are__ the bind variables so you definitely have control.
>>>You simply have to make sure you call cursor.setinputsizes() before
>>>calling runquery().
>>
>>Well, they are, but I don't have control over them.. :)
>>
>>Since same call in code is used for different database backends (like
>>Postgres)
>
>
> Ah, I see. That does make life a little more difficult.
>
And now you tell me that..? :)
>>This is somewhat possible to fix in current situation since I know that
>>strings that are > 4k must be clobs...
>
>
> Not necessarily in general but that could be true in your application.
> You also have long, long raw and BLOB to deal with. If your
> application doesn't use them you can make this assumption.
Well fortunately (and currently) this is situation...
> BTW, if you __really__ have no control, you can always subclass
> Connection and Cursor and do whatever you need to do. Subclassing has
> 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...
But this has helped me a bit...
--
Jani Tiainen
|
|
From: Anthony T. <ant...@gm...> - 2005-05-17 21:39:45
|
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 has > > been quite convenient and I use it myself for a number of situations > > where the code is used by multiple database adapters. >=20 > 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. :-) > But this has helped me a bit... Glad to hear it. > -- >=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 > |
|
From: Jani T. <re...@lu...> - 2005-05-17 22:10:32
|
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 has
>>>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 = "insert into foobar (id, name) values (:1, :2)"
values = {'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..?
|
|
From: Anthony T. <ant...@gm...> - 2005-05-17 23:03:43
|
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 has
> >>>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. :-)
>=20
> Well I have something like following:
>=20
> 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)
>=20
> But I'm getting error:
> Variable_TypeByPythonType(): unhandled data type
>=20
> 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
cursor.setinputsizes(value =3D cx_Oracle.CLOB)
and reference :value in your statement. Make sense?
> -------------------------------------------------------
> 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
>
|
|
From: Jani T. <re...@lu...> - 2005-05-18 05:49:42
|
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 has
>>>>>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 = "insert into foobar (id, name) values (:1, :2)"
>>values = {'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
I got this never working... It just says "cx_Oracle.ProgrammingError:
number of elements must be an integer"
> cursor.setinputsizes(value = cx_Oracle.CLOB)
>
> and reference :value in your statement. Make sense?
Yes, but... I think I pasted too short description of my problem.
In application code insert clause is in form:
sql = "INSERT INTO FOOBAR (ID, VALUE, CLOB_VALUE) VALUES (%s, %s, %s)"
params = (1, 'foobar', 'verylongfoobar')
This is converted to format:
sql = "INSERT INTO FOOBAR (ID, VALUE, CLOB_VALUE) VALUES (:val1, :val2,
:val3)"
params = {'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 = {}
for k, v in params.items():
if type(v) is str and len(v) > 4000:
inputsizes[k] = cx_Oracle.CLOB
else:
inputsizes[k] = 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.
--
Jani Tiainen
|
|
From: Jani T. <re...@lu...> - 2005-05-18 11:32:31
|
Jani Tiainen kirjoitti:
> In application code insert clause is in form:
>
> sql = "INSERT INTO FOOBAR (ID, VALUE, CLOB_VALUE) VALUES (%s, %s, %s)"
> params = (1, 'foobar', 'verylongfoobar')
>
> This is converted to format:
>
> sql = "INSERT INTO FOOBAR (ID, VALUE, CLOB_VALUE) VALUES (:val1, :val2,
> :val3)"
> params = {'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 = {}
> for k, v in params.items():
> if type(v) is str and len(v) > 4000:
> inputsizes[k] = cx_Oracle.CLOB
> else:
> inputsizes[k] = 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'}])
Any ideas why it always gives error except in case of named params?
--
Jani Tiainen
|
|
From: Anthony T. <ant...@gm...> - 2005-05-18 13:31:19
|
On 5/18/05, Jani Tiainen <re...@lu...> wrote:
> Jani Tiainen kirjoitti:
>=20
> > 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, :val=
2,
> > :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.
> >
>=20
> Only occassion when abowe works is when named parameters are passed.
>=20
> Even following doesn't seem to work:
>=20
> 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.
> Any ideas why it always gives error except in case of named params?
>=20
> --
>=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
>
|
|
From: Anthony T. <ant...@gm...> - 2005-05-18 13:29:44
|
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
>
|
|
From: Jani T. <re...@lu...> - 2005-05-18 15:59:24
|
Anthony Tuininga kirjoitti:
> On 5/18/05, Jani Tiainen <re...@lu...> wrote:
>
>>Jani Tiainen kirjoitti:
>>
>>
>>>In application code insert clause is in form:
>>>
>>>sql = "INSERT INTO FOOBAR (ID, VALUE, CLOB_VALUE) VALUES (%s, %s, %s)"
>>>params = (1, 'foobar', 'verylongfoobar')
>>>
>>>This is converted to format:
>>>
>>>sql = "INSERT INTO FOOBAR (ID, VALUE, CLOB_VALUE) VALUES (:val1, :val2,
>>>:val3)"
>>>params = {'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 = {}
>>>for k, v in params.items():
>>> if type(v) is str and len(v) > 4000:
>>> inputsizes[k] = cx_Oracle.CLOB
>>> else:
>>> inputsizes[k] = 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.
I saw that on some example and it seemed to work tough.. :)
Well finally I got it workin (felt a little stupid after all.. :)
But thak you very much for help and patience.
Final question: Is there plans to support c-format (%s) parameters in
execute query?
--
Jani Tiainen
|
|
From: Anthony T. <ant...@gm...> - 2005-05-18 20:41:23
|
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
>
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X