Latest version causes "ORA-12704: character set mismatch"

Latest version causes "ORA-12704: character set mismatch"

Postby Bonsahib » Tue 27 Sep 2011 06:07

Hi there,

we're using the entity framework 4.1 and devart with oracle.
Recently we tried to update our devart provider from version 6.10.126 to 6.5.
After that update we receive the "ORA-12704: character set mismatch" exception in some situations.
Our datamodel is a little more complex and contains at maximum a three level deep inheritance. I tried to reduce its complexity as far as possible but it's still too complex to explain in detail. I can send you my sample project which causes that bug.
The bug occurs, when I execute the following statement:
Code: Select all
var foreigners = (from s in storage.Person.OfType() select s).ToList();


That statement results in the following sql statement:

Code: Select all
SELECT
1 AS C1,
CASE WHEN (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)) AND ( NOT (("UnionAll2".C3 = 1) AND ("UnionAll2".C3 IS NOT NULL))) THEN '0X0X0X' WHEN ("UnionAll2".C2 = 1) AND ("UnionAll2".C2 IS NOT NULL) THEN '0X1X' WHEN ("UnionAll2".C3 = 1) AND ("UnionAll2".C3 IS NOT NULL) THEN '0X0X0X0X' ELSE '0X2X0X' END AS C2,
"Extent1".DBKEY AS DBKEY,
CASE WHEN (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)) AND ( NOT (("UnionAll2".C3 = 1) AND ("UnionAll2".C3 IS NOT NULL))) THEN "Project1".CON_JOBDESCRIPTION WHEN ("UnionAll2".C2 = 1) AND ("UnionAll2".C2 IS NOT NULL) THEN TO_NCHAR(NULL) WHEN ("UnionAll2".C3 = 1) AND ("UnionAll2".C3 IS NOT NULL) THEN "Project1".CON_JOBDESCRIPTION END AS C3,
CASE WHEN (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)) AND ( NOT (("UnionAll2".C3 = 1) AND ("UnionAll2".C3 IS NOT NULL))) THEN TO_NUMBER(NULL) WHEN ("UnionAll2".C2 = 1) AND ("UnionAll2".C2 IS NOT NULL) THEN TO_NUMBER(NULL) WHEN ("UnionAll2".C3 = 1) AND ("UnionAll2".C3 IS NOT NULL) THEN TO_NUMBER(NULL) ELSE "UnionAll2".C4 END AS C4
FROM   DOMAINOBJECTBASE "Extent1"
LEFT OUTER JOIN  (SELECT
   "Extent2".CON_JOBDESCRIPTION AS CON_JOBDESCRIPTION,
   "Extent2".DBKEY AS DBKEY,
   1 AS C1
   FROM CONTAINER "Extent2" ) "Project1" ON "Extent1".DBKEY = "Project1".DBKEY
LEFT OUTER JOIN  (SELECT
   "UnionAll1".DBKEY AS C1,
   "UnionAll1".C1 AS C2,
   "UnionAll1".C2 AS C3,
   "UnionAll1".SPC_ASS_CONTAINER AS C4
   FROM  (SELECT
      "Extent3".DBKEY AS DBKEY,
      0 AS C1,
      0 AS C2,
      "Extent3".SPC_ASS_CONTAINER AS SPC_ASS_CONTAINER
      FROM SPECIMEN "Extent3"
   UNION ALL
      SELECT
      "Extent4".DBKEY AS DBKEY,
      0 AS C1,
      1 AS C2,
      TO_NUMBER(NULL) AS C3
      FROM REAGENTCONTAINER "Extent4") "UnionAll1"
UNION ALL
   SELECT
   "Extent5".DBKEY AS DBKEY,
   1 AS C1,
   0 AS C2,
   TO_NUMBER(NULL) AS C3
   FROM KIT "Extent5") "UnionAll2" ON "Extent1".DBKEY = "UnionAll2".C1
WHERE CASE WHEN (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)) AND ( NOT (("UnionAll2".C3 = 1) AND ("UnionAll2".C3 IS NOT NULL))) THEN '0X0X0X'
WHEN ("UnionAll2".C2 = 1) AND ("UnionAll2".C2 IS NOT NULL) THEN '0X1X'
WHEN ("UnionAll2".C3 = 1) AND ("UnionAll2".C3 IS NOT NULL) THEN '0X0X0X0X' ELSE '0X2X0X' END LIKE '0X2X%'


The "case when" part in the where clause causes the problem.

If you need any additional information or the sample project, please let me know.

Andi
Bonsahib
 
Posts: 10
Joined: Fri 04 Mar 2011 13:48

Postby Shalex » Wed 28 Sep 2011 11:44

Please send us:
1) a small test project with the corresponding DDL/DML script so that we can reproduce the issue in our environment;
2) your connection string (roughly, without credentials);
3) NLS_LANGUAGE, NLS_CHARACTERSET, and NLS_NCHAR_CHARACTERSET of your Oracle server.
Shalex
Devart Team
 
Posts: 7699
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Tue 04 Oct 2011 14:35

Thank you for your test project. We have reproduced the "ORA-12704: character set mismatch" error. We will investigate the problem and post here about the results as soon as possible.
Shalex
Devart Team
 
Posts: 7699
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Wed 05 Oct 2011 14:05

We have investigated the problem.

The processing of string types in Entity Framework was changed totally starting from the 6.30 version of dotConnect for Oracle. As a result, we have solved the "character set mismatch" problem for the case when the CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, and NCLOB columns are used in the same query.

All models, which were created with the pre-6.30 versions, should work with the newer (6.30 and higher) versions of dotConnect for Oracle. But we have found and FIXED the bug with compatibility of old model and new provider. Thank you for your test project. The next public build of dotConnect for Oracle will work correctly with your current model. We will post here when the new public build of dotConnect for Oracle is available for download.

You can modify your model to make it work with current (6.50.228) build.
Here is DDL for the EMPLOYEE table:
-- Table "EMPLOYEE"
CREATE TABLE "EMPLOYEE" (
"EP_JOBDESCRIPTION" VARCHAR2(8) NULL,
"DBKEY" NUMBER(18) NOT NULL,
PRIMARY KEY ("DBKEY")
)
/
So, EP_JOBDESCRIPTION is VARCHAR2.
This column has Unicode="true" in the CSDL part of the EDMX-model. It was correct for pre-6.30 version, but it is wrong for 6.30 and post-6.30. If you set Unicode="false", the model will work with dotConnect for Oracle v 6.50.
Shalex
Devart Team
 
Posts: 7699
Joined: Thu 14 Aug 2008 12:44

Postby Bonsahib » Tue 11 Oct 2011 05:23

Hi Shalex,

thank you for your help. We will use your workaround and include your latest release as soon as it is available.

Andi
Bonsahib
 
Posts: 10
Joined: Fri 04 Mar 2011 13:48

Postby jared » Wed 12 Oct 2011 22:16

I also have the same issue with using EF 4.1 Code First... It will be great to have this fixed...
jared
 
Posts: 10
Joined: Wed 12 Oct 2011 22:12

Postby Shalex » Tue 18 Oct 2011 14:20

We are going to release the new public build of dotConnect for Oracle this week.
Shalex
Devart Team
 
Posts: 7699
Joined: Thu 14 Aug 2008 12:44

Postby Bonsahib » Mon 24 Oct 2011 15:17

Hi Shalex,

thanks for the new release. Unfortunately it's still not working for us. The test project I sent to you now works perfectly fine.
So I tried to change the Unicode attribute as you mentioned in out productive code but that lead to another exception:
"The specified value is not an instance of type 'Edm.Int64'
Parameter name: value"

So I investigated a little further and came up with the suspicion that 0..1 to 0..1 relations dont work anymore. And indeed that's the problem. When you change the Main method of my test project to:
Code: Select all
static void Main(string[] args)
        {
            using (DomainStorage storage = new DomainStorage())
            {
                storage.Person.AddObject(new Employee { JobDescription = "Boss", Foreigner = new Diplomat() });
                storage.Person.AddObject(new Employee { JobDescription = "Nerd", Foreigner = new Diplomat() });
                storage.Person.AddObject(new Employee { JobDescription = "Tester" });
                storage.SaveChanges();
            }

            using (DomainStorage storage = new DomainStorage())
            {
                foreach (Citizen inventoryItem in storage.Person.OfType())
                {
                    storage.Person.DeleteObject(inventoryItem);
                }
                storage.SaveChanges();
            }
        }


it will fail with the above exception. i also tried to set "Unicode" to false. Didn't change anything.

So basically there is still the character missmatch problem and now the 0..1 to 0..1 relations are not resolved properly anymore.

Andi[/code]
Bonsahib
 
Posts: 10
Joined: Fri 04 Mar 2011 13:48

Postby jared » Mon 24 Oct 2011 21:29

I still get the "ORA-12704: character set mismatch" error. Please see http://www.devart.com/forums/viewtopic.php?p=74751#74751 for details.
jared
 
Posts: 10
Joined: Wed 12 Oct 2011 22:12

Postby Shalex » Tue 25 Oct 2011 15:37

We are investigating the issue.
Shalex
Devart Team
 
Posts: 7699
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Thu 27 Oct 2011 10:45

Bonsahib, the problem you have described in your previous post is not related to character set mismatch. It is the bug with determining correct data types in columns of descendants in TPT and TPC hierarchies. We have fixed this bug. We will post here when the corresponding build of dotConnect for Oracle is available for download.
Shalex
Devart Team
 
Posts: 7699
Joined: Thu 14 Aug 2008 12:44

Postby hweidle » Fri 28 Oct 2011 08:34

We have the same error: "ORA-12704 Character mismatch" since we are using the latest DevArt Oracle drivers (5.0.336.0 and 6.50.237.0) the version before (5.0.327.0 and 6.50.228.0) did not have the problem.

Our Linq-Statement:

var result = context.Elements
.Include(e => e.Texts)
.Include(e => e.Texts.Select(t => t.Language)) .Where(e => e.Code.StartsWith(pattern) &&
e.ElementClassConfiguration.ElementClassId > 500)
.OrderBy(e => e.Code)
.ThenBy(e => e.Index)
.ThenBy(e => e.Variant);

SQL-Statement:

SELECT
"Project2".ID1 AS ID,
"Project2".ID AS ID1,
"Project2".ID2 AS ID2,
"Project2".C1 AS C1,
"Project2".CLUSD_ID AS CLUSD_ID,
"Project2".USERD_ID AS USERD_ID,
"Project2".CODE AS CODE,
"Project2".INDX AS INDX,
"Project2".VARIANT AS VARIANT,
"Project2".DARED_ID AS DARED_ID,
"Project2".TYPE_S AS TYPE_S,
"Project2".MASTA_ID AS MASTA_ID,
"Project2".COSTC_ID AS COSTC_ID,
"Project2".INCOMPLETE_F AS INCOMPLETE_F,
"Project2".TYPE_SI AS TYPE_SI,
"Project2".C2 AS C2,
"Project2".C3 AS C3,
"Project2".C4 AS C4,
"Project2".C5 AS C5,
"Project2".C6 AS C6,
"Project2".C7 AS C7,
"Project2".MASTR_ID AS MASTR_ID,
"Project2".LANGU_ID AS LANGU_ID,
"Project2".HEADER_DESCR AS HEADER_DESCR,
"Project2".SOURCE_LANGU_ID AS SOURCE_LANGU_ID,
"Project2".ID3 AS ID3,
"Project2".LANGUAGE_CODE AS LANGUAGE_CODE,
"Project2".LANGUAGE_DESCR_T AS LANGUAGE_DESCR_T,
"Project2".COUNTRY_CODE AS COUNTRY_CODE,
"Project2".COUNTRY_DESCR_T AS COUNTRY_DESCR_T,
"Project2".INSTALLED_F AS INSTALLED_F,
"Project2".METRIC_F AS METRIC_F
FROM ( SELECT
"Extent1".ID AS ID,
"Extent1".CLUSD_ID AS CLUSD_ID,
"Extent1".USERD_ID AS USERD_ID,
"Extent1".CODE AS CODE,
"Extent1".INDX AS INDX,
"Extent1".VARIANT AS VARIANT,
"Extent1".DARED_ID AS DARED_ID,
"Extent1".TYPE_S AS TYPE_S,
"Extent1".MASTA_ID AS MASTA_ID,
"Extent1".COSTC_ID AS COSTC_ID,
"Extent1".INCOMPLETE_F AS INCOMPLETE_F,
"Extent1".TYPE_SI AS TYPE_SI,
"Project1".ID AS ID1,
CASE WHEN NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)) THEN '0X' ELSE '0X0X' END AS C1,
CASE WHEN NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)) THEN TO_NCHAR(NULL) ELSE "Project1".SHORT_CODE END AS C2,
CASE WHEN NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)) THEN TO_NUMBER(NULL) ELSE "Project1".ANMET_ID END AS C3,
CASE WHEN NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)) THEN TO_NUMBER(NULL) ELSE "Project1".VACRM1_VACRI_ID END AS C4,
CASE WHEN NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)) THEN TO_NUMBER(NULL) ELSE "Project1".VACRM2_VACRI_ID END AS C5,
CASE WHEN NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)) THEN TO_NUMBER(NULL) ELSE "Project1".VACRM3_VACRI_ID END AS C6,
"Extent3".ID AS ID2,
"Join3".MASTR_ID AS MASTR_ID,
"Join3".LANGU_ID AS LANGU_ID,
"Join3".HEADER_DESCR AS HEADER_DESCR,
"Join3".SOURCE_LANGU_ID AS SOURCE_LANGU_ID,
"Join3".ID AS ID3,
"Join3".LANGUAGE_CODE AS LANGUAGE_CODE,
"Join3".LANGUAGE_DESCR_T AS LANGUAGE_DESCR_T,
"Join3".COUNTRY_CODE AS COUNTRY_CODE,
"Join3".COUNTRY_DESCR_T AS COUNTRY_DESCR_T,
"Join3".INSTALLED_F AS INSTALLED_F,
"Join3".METRIC_F AS METRIC_F,
CASE WHEN "Join3".MASTR_ID IS NULL THEN TO_NUMBER(NULL) ELSE 1 END AS C7
FROM TICON_4_20111013.MASTR "Extent1"
LEFT OUTER JOIN (SELECT
"Extent2".ID AS ID,
"Extent2".SHORT_CODE AS SHORT_CODE,
"Extent2".ANMET_ID AS ANMET_ID,
"Extent2".VACRM1_VACRI_ID AS VACRM1_VACRI_ID,
"Extent2".VACRM2_VACRI_ID AS VACRM2_VACRI_ID,
"Extent2".VACRM3_VACRI_ID AS VACRM3_VACRI_ID,
1 AS C1
FROM TICON_4_20111013.TELEM0E "Extent2" ) "Project1" ON "Extent1".ID = "Project1".ID
INNER JOIN TICON_4_20111013.CLUSD "Extent3" ON "Extent1".CLUSD_ID = "Extent3".ID
LEFT OUTER JOIN (SELECT
"Extent4".MASTR_ID AS MASTR_ID,
"Extent4".LANGU_ID AS LANGU_ID,
"Extent4".HEADER_DESCR AS HEADER_DESCR,
"Extent4".SOURCE_LANGU_ID AS SOURCE_LANGU_ID,
"Extent5".ID AS ID,
"Extent5".LANGUAGE_CODE AS LANGUAGE_CODE,
"Extent5".LANGUAGE_DESCR_T AS LANGUAGE_DESCR_T,
"Extent5".COUNTRY_CODE AS COUNTRY_CODE,
"Extent5".COUNTRY_DESCR_T AS COUNTRY_DESCR_T,
"Extent5".INSTALLED_F AS INSTALLED_F,
"Extent5".METRIC_F AS METRIC_F
FROM TICON_4_20111013.MASTR0T "Extent4"
INNER JOIN TICON_4_20111013.LANGU "Extent5" ON "Extent4".LANGU_ID = "Extent5".ID ) "Join3" ON "Extent1".ID = "Join3".MASTR_ID
WHERE ("Extent3".CLMOD_ID > 500) AND ("Extent1".CODE LIKE :p__linq__0 ESCAPE '/')
) "Project2"
ORDER BY "Project2".CODE ASC, "Project2".INDX ASC, "Project2".VARIANT ASC, "Project2".ID1 ASC, "Project2".ID ASC, "Project2".ID2 ASC, "Project2".C7 ASC
hweidle
 
Posts: 3
Joined: Fri 28 Oct 2011 08:26

Postby Shalex » Mon 31 Oct 2011 16:15

hweidle, if you are using the Code-First approach, send us (or post here) your classes. Or send us your model in case of Database-First.

The issue described by you is rather fixed. You can wait till the next public build of dotConnect for Oracle to check this. Or give us the mentioned information, and we will try to reproduce the problem in our environment.
Shalex
Devart Team
 
Posts: 7699
Joined: Thu 14 Aug 2008 12:44

Postby hweidle » Thu 10 Nov 2011 14:51

What information do you need? All the source code and the complete database? It is both CodeFirst DbContext classes mapped to an existing database. When the next version will be available?
hweidle
 
Posts: 3
Joined: Fri 28 Oct 2011 08:26

Postby Shalex » Fri 11 Nov 2011 14:54

hweidle wrote:When the next version will be available?

The new public build of dotConnect for Oracle is available for download. Please try using it. If the problem persists with the new build as well, send us a small complete test project.

hweidle wrote:What information do you need? All the source code and the complete database? It is both CodeFirst DbContext classes mapped to an existing database.

We need the project that reproduces the issue. If possible, try localize the problem not to send us your whole database and all project code.
Shalex
Devart Team
 
Posts: 7699
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle