[
https://issues.apache.org/jira/browse/CAY-2894?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Nikita Timofeev updated CAY-2894:
---------------------------------
Fix Version/s: 4.2.3
5.0-M2
> Issue with Oracle NCLOB Search in Version 4.2.2
> -----------------------------------------------
>
> Key: CAY-2894
> URL: https://issues.apache.org/jira/browse/CAY-2894
> Project: Cayenne
> Issue Type: Bug
> Components: Core Library
> Affects Versions: 4.2.2
> Reporter: lallemand
> Priority: Major
> Fix For: 4.2.3, 5.0-M2
>
>
> Dear,
>
> In the latest stable version (4.2.2), we are encountering an issue when
> performing a search on Oracle *{{NCLOB}}* fields.
> Previously, this functionality worked correctly, and it still behaves as
> expected on SQL Server using *{{{}nvarchar(max){}}}.*
> h3. The Issue
> The problem arises when executing a query with a {{LIKE}} expression, such as:
>
> {code:java}
> public static KycRuleCommon getFromNameAndQuery(String pm_name, String
> pm_query) throws BaseException
> {
> try {
> if (pm_name == null || pm_name.compareTo("")==0 || pm_query ==
> null || pm_query.compareTo("")==0) return null;
> Map<String, Object> parameters = new HashMap<>();
> parameters.put(NAME.getName(), pm_name);
> parameters.put(QUERY_SQL.getName(), pm_query);
> ObjectSelect<KycRuleCommon> query =
> ObjectSelect.query(KycRuleCommon.class, ExpressionFactory.exp(
> NAME.getName() + " like $" + NAME.getName()
> + " and " + QUERY_SQL.getName() + " like $" +
> QUERY_SQL.getName()).params(parameters));
> ObjectContext context = BaseContext.getThreadObjectContext();
> return context.selectFirst(query);
> } catch (Exception e) {
> throw new EntityException(new EventSysm1023(e));
> }
> } {code}
> We understand that using *{{LIKE}}* on *{{NCLOB}}* fields is generally
> discouraged, especially if the value exceeds 4000 characters, as this can
> lead to runtime errors. However, even when the value is well under that
> limit, it now fails on Oracle, while still working on SQL Server.
> This suggests that a recent change in parameter binding may be forcing Oracle
> to treat the field as an *NCLOB* explicitly, causing the failure regardless
> of content size.
> Normally, in Oracle, such queries should use *{{DBMS_LOB.INSTR()}}* for safe
> processing.
> h3. Working Queries in Oracle
> The following native SQL queries work correctly on our database and bring one
> record:
>
> {code:java}
> SELECT * FROM TKYCRULECOMMON t WHERE t.name LIKE 'name' AND t.QUERY_SQL LIKE
> '%test%';
> SELECT * FROM TKYCRULECOMMON t WHERE t.name LIKE 'name' AND
> DBMS_LOB.INSTR(t.QUERY_SQL, 'test') > 0; {code}
> h3. Attempt with {{FunctionExpressionFactory}}
> I attempted to adapt the logic using
> {*}{{FunctionExpressionFactory.functionCall()}}{*}, but I cannot get it to
> return any results. The query doesn't crash and seems to be generated
> correctly, but always yields no records.
> Here is the updated code and the resulting SQL:
>
> {code:java}
> public static KycRuleCommon getFromNameAndQuery(String pm_name, String
> pm_query) throws BaseException
> {
> try {
> if (pm_name == null || pm_name.compareTo("")==0 || pm_query ==
> null || pm_query.compareTo("")==0) return null;
> Map<String, Object> parameters = new HashMap<>();
> parameters.put(NAME.getName(), pm_name);
> parameters.put(QUERY_SQL.getName(), pm_query);
> Expression dbmsExp =
> FunctionExpressionFactory.functionCall("DBMS_LOB.INSTR",QUERY_SQL.getName(),"$"
> + QUERY_SQL.getName());
> Expression compareExp = ExpressionFactory.greaterExp(dbmsExp, 0);
> Expression nameExp = ExpressionFactory.exp(NAME.getName() + " like $"
> + NAME.getName());
> ObjectSelect<KycRuleCommon> query =
> ObjectSelect.query(KycRuleCommon.class,
> compareExp.andExp(nameExp).params(parameters));
> query.cacheStrategy(QueryCacheStrategy.NO_CACHE);
> ObjectContext context = BaseContext.getThreadObjectContext();
> return context.selectFirst(query);
> } catch (Exception e) {
> throw new EntityException(new EventSysm1023(e));
> }
> } {code}
> Generated SQL:
> {code:java}
> SELECT t0.created_by c0, t0.date_created c1, t0.date_modified c2,
> t0.description c3, t0.dynfield_identifier c4, t0.modified_by c5, t0.name
> c6, t0.query_sql c7, RTRIM(t0.standard) c8, t0.version c9, t0.id c10 FROM
> tKycRuleCommon t0 WHERE (DBMS_LOB.INSTR(: 1,: 2) >: 3) AND t0.name LIKE: 4
> {code}
>
> Despite the query looking correct, no results are returned. Using SQL
> Template we do retrieve correctly information.
> Could you please clarify:
> * Am I using *{{FunctionExpressionFactory.functionCall()}}* correctly in
> this context?
> * Is this a known limitation or a potential bug introduced in version 4.2.2?
> * Is there an alternative/best-practice approach when working with NCLOBs
> and expressions like this?
> Thank you in advance for your help.
> Best regards,
> Anton
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
