I have a table systesttab
that contains a few columns. One of those columns is of type CLOB and it's supposed to hold a string of a base64 encoded image.
CREATE TABLE systesttab(
...
f_picture CLOB DEFAULT ' ' NOT NULL,
...
)
However, when I try to update the table with a large base64 string (over 100k character), it fails and my python application crashes (even when put in a try...except block).
UPDATE systesttab SET f_picture = 'data:image/png;base64,iVBORw0KGgoASU ...'
I have even tried to cast the value to clob:
UPDATE systesttab SET f_picture = TO_CLOB('data:image/png;base64,iVBORw0KGgoASU ...')
But all I get is this error:
Input string too long, limit 8192
Now, I guess that this is trying to tell me something about the chunk size, but it's not really helpful to me.
How can I update the table with a single statement?
Do I have to declare the table's create statement differently?
If there is a way to get this done in a single statement, it should also work when updating multiple columns on the same table.
Environment: python 3.4 & pyodbc
I have solved this by using SQL Bindings. It seems the character limit does not apply then.
In python the statement now looks like this:
pic = 'data:image/png;base64,iVBORw0KGgoASU ...'
sql = "UPDATE systesttab SET f_picture = ?"
cursor.execute( sql, [pic] )
This also works fine when updating multiple fields at the same time.