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

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

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