Data.Oracle.Entity.OracleEntityProviderServices.TypedNulls

MartinJJ · Post by **MartinJJ** » Mon 12 Apr 2010 14:00

Hi

When I run query :

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 · Post by **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 · Post by **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 · Post by **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.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 · Post by **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.

AndreyR · Post by **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?

MartinJJ · Post by **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.

AndreyR · Post by **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.

MartinJJ · Post by **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