|
|
|
DataNucleus Forum » List all forums » » Forum: Queries : JDOQL » » » Thread: Problem with queries on unicode fields in Oracle 10g |
|
| Print at Mar 17, 2010 5:38:15 AM | View all posts in this thread on one page |
| Posted by khomikh at Sep 4, 2006 7:26:07 AM |
|
Problem with queries on unicode fields in Oracle 10g Hi. I have a problem with queries on unicode fields in Oracle 10g. Queries work correct on this table: CREATE TABLE OE_ORGANIZATIONS_LOOKUP_NAMES ( ORGANIZATION_ID CHAR(36), ORDINAL_ID NUMBER(10), LOOKUP_NAME VARCHAR2(240) ) but don't work on this table: CREATE TABLE OE_ORGANIZATIONS_LOOKUP_NAMES ( ORGANIZATION_ID CHAR(36), ORDINAL_ID NUMBER(10), LOOKUP_NAME NVARCHAR2(240) ) I have received error: JDOQL Query : "SELECT count(this) FROM test.Organization WHERE this.lookupNames.contains(v_0) && v_0.matches(p_0) VARIABLES java.lang.String v_0 PARAMETERS java.lang.String p_0" SELECT COUNT(THIS.ID) FROM OE_ORGANIZATIONS THIS WHERE EXISTS (SELECT 1 FROM OE_ORGANIZATIONS_LOOKUP_NAMES THIS_LOOKUPNAMES_V_0 WHERE THIS_LOOKUPNAMES_V_0.ORGANIZATION_ID = THIS.ID AND THIS_LOOKUPNAMES_V_0.ORGANIZATION_ID = THIS.ID AND THIS_LOOKUPNAMES_V_0.LOOKUP_NAME LIKE <'company%'> ESCAPE '\' ) : ORA-01425: escape character must be character string of length 1 The following query works correct: SELECT COUNT(THIS.ID) FROM OE_ORGANIZATIONS THIS WHERE EXISTS (SELECT 1 FROM OE_ORGANIZATIONS_LOOKUP_NAMES THIS_LOOKUPNAMES_V_0 WHERE THIS_LOOKUPNAMES_V_0.ORGANIZATION_ID = THIS.ID AND THIS_LOOKUPNAMES_V_0.ORGANIZATION_ID = THIS.ID AND THIS_LOOKUPNAMES_V_0.LOOKUP_NAME LIKE 'company%' ESCAPE '\\' ) May be better use ESCAPE statement only when it is necessary (when match text contains SQL specific wildcard characters (in my case '%' and '_')). |
| Posted by andy at Sep 4, 2006 7:58:06 AM |
|
Re: Problem with queries on unicode fields in Oracle 10g Queries work correct on this table: LOOKUP_NAME VARCHAR2(240) but not with LOOKUP_NAME NVARCHAR(240) Which query works correct ? The same one you quoted below? i.e with the same ESCAPE char ? i.e what is the JDOQL and SQL that works on the first and not on the second. May be better use ESCAPE statement only when it is necessary (when match text contains SQL specific wildcard characters (in my case '%' and '_')). You dont know what the parameter text is going to include when compiling it. ---------------------------------------- -Andy   |
| Posted by khomikh at Sep 4, 2006 11:01:00 AM |
|
Re: Problem with queries on unicode fields in Oracle 10g Which query works correct ? The same one you quoted below? i.e with the same ESCAPE char ? i.e what is the JDOQL and SQL that works on the first and not on the second. 1. ESCAPE char same. 2. JDOQL same, but SQL should be other: with ESCAPE '\' -- for VARCHAR field with ESCAPE '\\' -- for NVARCHAR field Second query works when it's running manually from SQL-Plus. |
| Posted by andy at Sep 4, 2006 11:36:32 AM |
|
Re: Problem with queries on unicode fields in Oracle 10g 1. ESCAPE char same. 2. JDOQL same, but SQL should be other: with ESCAPE '\' -- for VARCHAR field with ESCAPE '\\' -- for NVARCHAR field Some RDBMSs are just weird (if you have an earlier version of Oracle (8, or 9) it would be nice to know if this is the case there too). Raise a JIRA with testcase please. ---------------------------------------- -Andy |
|
Current timezone is GMT Mar 17, 2010 5:38:15 AM |