-
Type:
Bug
-
Status: Resolved
-
Priority:
Blocker
-
Resolution: Fixed
-
Affects Version/s: 2021.40
-
Component/s: Core VCS
-
Release Notes Summary:A new property is added to use setClob method in PreparedStatement for Oracle.
-
Release Notes Description:
-
Tags:
-
Backlog priority:1,000
-
Sprint:nxplatform #92
-
Story Points:1
After fixing NXP-31955 to use setClob for CLOB column, it appears that Oracle throws an error when using a CLOB in a WHERE clause with the following stacktrace
SyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB
org.nuxeo.ecm.core.api.NuxeoException: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB
at org.nuxeo.ecm.core.storage.sql.kv.SQLKeyValueStore.compareAndSet(SQLKeyValueStore.java:918) ~[nuxeo-core-storage-sql-2021.40-SUPNXP-45992.jar:?]
at org.nuxeo.ecm.core.storage.sql.kv.SQLKeyValueStore.compareAndSet(SQLKeyValueStore.java:815) ~[nuxeo-core-storage-sql-2021.40-SUPNXP-45992.jar:?]
at org.nuxeo.ecm.core.transientstore.keyvalueblob.KeyValueBlobTransientStore.atomicUpdate(KeyValueBlobTransientStore.java:381) ~[nuxeo-core-cache-2021.37.4.jar:?]
at org.nuxeo.ecm.core.transientstore.keyvalueblob.KeyValueBlobTransientStore.putParameter(KeyValueBlobTransientStore.java:417) ~[nuxeo-core-cache-2021.37.4.jar:?]
at org.nuxeo.ecm.automation.server.jaxrs.batch.Batch.addFile(Batch.java:187) ~[nuxeo-automation-server-2021.37.4.jar:?]
at org.nuxeo.ecm.restapi.server.jaxrs.BatchUploadObject.addBlob(BatchUploadObject.java:309) ~[nuxeo-rest-api-server-2021.37.4.jar:?]
(...)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
(...)
at org.nuxeo.ecm.core.storage.sql.kv.SQLKeyValueStore.compareAndSet(SQLKeyValueStore.java:909) ~[nuxeo-core-storage-sql-2021.40-SUPNXP-45992.jar:?]
We tested a workaround with the customer : if the value's length is less than 8192, we continue to use setString. If above, we use setClob based on the hypothesis that the WHERE clause will never test a very long STRING
