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

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

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