[Zope-dev] [Zope] Zsql and oracle CLOB column

[harshad behere]

harshad behere wrote:
> Dear list,
>
> While inserting data into one of the oracle table's CLOB column using 
> zsql via python script, we observed that zope process (running in debug 
> mode) is getting terminated/killed if length of text data >= 4094 with 
> message on command prompt
>
> Input string too long, limit 4096
>
>   
Above one more looks like issue/bug with oracle odbc driver rather than 
zsql/zope issue.

http://164.100.150.68/downloads/oracle/Oracle_InstantClient/Oracle/instantclient10_1/ODBCRelnotesUS.htm
http://www.dbatools.net/doc/bug10203.html
http://mail.python.org/pipermail/python-list/2007-March/603644.html

> Whereas  for text data with length >=4001 and <=4093 - It throws 
> following error
> OperationalError on <Products.mxODBCZopeDA.ZopeDA.DatabaseConnection 
> "DSN=test_clob_oracle" thread 3672/3672 at 0x3ac3530>: ('HY000', 1704, 
> '[Oracle][ODBC][Ora]ORA-01704: string literal too long\n', 6113)
>
> python script details:
> print context.clob_zsql_test(i_description='A'*4095).
> return printed
>
> zsql details -
> INSERT INTO CLOB_TEST(description) VALUES(<dtml-sqlvar i_description 
> type="string">)
>
> Table/column details :
> id - number
> description - clob
>
> Where as if we use mxODBCZopeDA execute API with following insert query 
> syntax 
> string_value = 'A'*5000
> query = """ INSERT INTO CLOB_TEST(document) VALUES(?) """
> result = test_connection.execute(query, string_value)
>
> it works fine even with text data having length > 10000
>
> After googling on the same found that execute method uses/support 
> parameters binding (is it similar to oracle bind varibles ? thats may be 
> reason why it works well). Do we have similar support for zsql or is 
> there other workaround ?
>
> Configuration/other details are as follows :
>
> RDBMS : Oracle Database 10g Release 10.2.0.1.0
> ODBC used : Oracle ODBC which shipped with Oracle-Client-10g
> Python adapter : mxODBC Python ODBC Interface - 2.1
> Related Zope Product : mxODBCZopeDA-1.0.10
>
> Zope : Zope 2.11.1-final
> Python : python 2.4.4
> Operating system  : win32
>
> ZEO setup : Yes
> No of ZEO nodes : one
> Threads per ZEO node : 10
>
> Any kind of pointers/suggestions would be of great help.
>
> Thanking you,
> Harshad.
> _______________________________________________
> Zope maillist  -  Zope at zope.org
> http://mail.zope.org/mailman/listinfo/zope
> **   No cross posts or HTML encoding!  **
> (Related lists - 
>  http://mail.zope.org/mailman/listinfo/zope-announce
>  http://mail.zope.org/mailman/listinfo/zope-dev )
>
>