Data.Oracle.Entity.OracleEntityProviderServices.TypedNulls

Data.Oracle.Entity.OracleEntityProviderServices.TypedNulls

Postby MartinJJ » Mon 12 Apr 2010 14:00

Hi

When I run query :

Code: Select all
groups_query = Devart.Data.Linq.CompiledQuery.Compile(
                (GAORCL2.GAORCL2 ga, string language) =>
                    from grp in ga.GroupDefs
                    join label in ga.Labels on
                        new { LABEL = grp.GRD_NAME_LBL, LANGUAGE = language } equals
                        new { LABEL = label.LBL_LABEL, LANGUAGE = label.LBL_LANG } into labels
                    from label in labels.DefaultIfEmpty()
                    select Util.MakePair(grp.GRD_GROUP, new GroupDefData(label == null ? String.Empty : label.LBL_TEXT))
            );


sql looks like this :

Code: Select all
SELECT t1.GRD_GROUP,
    (CASE
        WHEN (t2.LBL_LABEL IS NULL) AND (t2.LBL_LANG IS NULL) THEN :p1
        ELSE t2.LBL_TEXT
     END) AS C1
FROM GRADS3_USER.GROUP_DEFS t1
LEFT OUTER JOIN GRADS3_USER.LABELS t2 ON ((t1.GRD_NAME_LBL = t2.LBL_LABEL) OR ((t1.GRD_NAME_LBL IS NULL) AND (t2.LBL_LABEL IS NULL))) AND (:p0 = t2.LBL_LANG)


and if I place this sql in sqlplus with 'sometext' replacing :p1 and :p0 I get the same error. ORA-12704. The charset mismatch. There is much talk about resolving this with TypedNulls set to True. Though this attribute seems to be not available to me with the trial version. Is it old way? Is there another to resolve it now? Or do i have bad setup with db?

    SQL> desc labels;
    Name Null? Type
    ----------------------------------------- -------- -----------------

    LBL_LABEL NOT NULL NVARCHAR2(32)
    LBL_LANG NOT NULL NVARCHAR2(2)
    LBL_TEXT NOT NULL NVARCHAR2(2000)

    SQL> desc group_defs;
    Name Null? Type
    ----------------------------------------- -------- --------------

    GRD_GROUP NOT NULL NUMBER(38)
    GRD_NAME_LBL NOT NULL NVARCHAR2(32)


    SQL> select * from nls_database_parameters;

    PARAMETER VALUE
    ------------------------------ -------------------------------
    NLS_LANGUAGE AMERICAN
    NLS_TERRITORY AMERICA
    NLS_CURRENCY $
    NLS_ISO_CURRENCY AMERICA
    NLS_NUMERIC_CHARACTERS .,
    NLS_CHARACTERSET WE8MSWIN1252
    NLS_CALENDAR GREGORIAN
    NLS_DATE_FORMAT DD-MON-RR
    NLS_DATE_LANGUAGE AMERICAN
    NLS_SORT BINARY
    NLS_TIME_FORMAT HH.MI.SSXFF AM

    PARAMETER VALUE
    ------------------------------ -------------------------------
    NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
    NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
    NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
    NLS_DUAL_CURRENCY $
    NLS_COMP BINARY
    NLS_LENGTH_SEMANTICS BYTE
    NLS_NCHAR_CONV_EXCP FALSE
    NLS_NCHAR_CHARACTERSET AL16UTF16
    NLS_RDBMS_VERSION 10.2.0.1.0

    20 rows selected.
MartinJJ
 
Posts: 14
Joined: Thu 08 Apr 2010 10:55

Postby MartinJJ » Tue 13 Apr 2010 02:47

Hi,

With regard to the NVARCHAR2 cols and the SQL statement. To get this to work N'' ie: N'en' to cast to 2 byte. Is dbConnect Oracle going to do that for me or is there a setting to support? Or is it a bug?

Regards

Martin
MartinJJ
 
Posts: 14
Joined: Thu 08 Apr 2010 10:55

Postby MartinJJ » Tue 13 Apr 2010 03:14

dbMontior shows the inputs as varchar ???? so thats the problem. The data type is not being detected
MartinJJ
 
Posts: 14
Joined: Thu 08 Apr 2010 10:55

Postby MartinJJ » Tue 13 Apr 2010 04:55

I found Devart...TypedNulls in the end. Its a little buried as to its usage in the docs :)

But still no good either way true or false for me. Varchar only inputs in sql to oracle. Seems enough people have had the issue but generally worked out. Though I'm using LINQ query and trying to maintain the portability here. Which i'm hoping is a goal for the product. So as far as I know things like

TOraSql.Params[i].AsWideString and Net Mode ?? are not practical solutions for me. I can't see in CRM sample app example of consideration of 2 byte and nvarchar2 and linq and translation of parameter type conversion.
MartinJJ
 
Posts: 14
Joined: Thu 08 Apr 2010 10:55

Postby MartinJJ » Wed 14 Apr 2010 00:17

Hi

Cmon, there must be some experiance of it Devart land. How do i support multi char from LINQ. Either I'm doing something stupid or incorrect but i have poured over the docs as best I can. I cant see anything thats going to give me abiltiy to control what is emitted to get passed this.
MartinJJ
 
Posts: 14
Joined: Thu 08 Apr 2010 10:55

Postby AndreyR » Wed 14 Apr 2010 11:37

The TypedNulls property is intended for handling some specific Entity Framework situations in ExpressionTree parsing.
I'm not sure this is the functionality you need.
Could you please provide some more information about the situation you get the error in?
And one simple suggestion: have you set Unicode=true in your connection string?
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby MartinJJ » Wed 14 Apr 2010 13:34

Thanks for the reply.

Unicode=true? Where do i refs to these so i dont bother you. Anyway I tried it. No it didn't work. I'm hoping you mean the connection string in the config the model generation creates.

So the linq query extract from above

Code: Select all
select Util.MakePair(grp.GRD_GROUP, new GroupDefData(label == null ? String.Empty : label.LBL_TEXT))


emits as

Code: Select all
WHEN (t2.LBL_LABEL IS NULL) AND (t2.LBL_LANG IS NULL) THEN :p1
        ELSE t2.LBL_TEXT


:p1 being String.Empty I'd say.

:p0 (the other parameter in the call) is the language string passed into the function used here
Code: Select all
new { LABEL = grp.GRD_NAME_LBL, LANGUAGE = language }

and emits here
Code: Select all
(:p0 = t2.LBL_LANG)


when you look at the ORA-12704 error within dbMonitor and look at the Parameters tab you see 'varchar' not 'nvarchar' inputs used to pass in what should be nvarchar types and then hence the error reported by oracle.
MartinJJ
 
Posts: 14
Joined: Thu 08 Apr 2010 10:55

Postby AndreyR » Thu 15 Apr 2010 15:45

I've made a simple nvarchar parameter test and succeeded.
Could you please send me a small project reproducing the error?
It will speed up our investigation greatly.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby MartinJJ » Mon 19 Apr 2010 04:20

Hi,

Ok thanks. I have prepared a sample that shows the issue I'm seeing.

i will send to the support address LINQ: Empty Type again.

Regards

Martin
MartinJJ
 
Posts: 14
Joined: Thu 08 Apr 2010 10:55


Return to dotConnect for Oracle