|
|
|
|
Welcome
Guest
| Register
| Login
|
| Index | Recent Threads | Unanswered Threads | Who's Online | User List | Help |
DataNucleus Forum
DataNucleus Support
Queries : JDOQL
Thread: Problem with queries on unicode fields in Oracle 10g
|
| No member browsing this thread |
|
Thread Status: Active Total posts in this thread: 4 |
|
| Author |
|
khomikh
Newbie Joined: Jun 15, 2006 Post Count: 16 Status: Offline |
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 '_')). |
||
|
|
andy
Expert UK Joined: Mar 13, 2004 Post Count: 5214 Status: Offline |
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   |
||
|
|
khomikh
Newbie Joined: Jun 15, 2006 Post Count: 16 Status: Offline |
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. |
||
|
|
andy
Expert UK Joined: Mar 13, 2004 Post Count: 5214 Status: Offline |
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 Feb 9, 2010 9:50:18 AM |
