2

I've tried save a Euro Symbol € using NHibernate and FluentNHibernate in a database Oracle 11g.

I have checked a log from NHibernate and see the generated sql statement:

UPDATE CURRENCY
SET    DESCRIPTION = 'Euro',
       SYMBOL = '€',
WHERE  ID = 63

When the query from table CURRENCY execute, the column SYMBOL returns ¿

I've tried change the FluentNHibernate Mapping for the column SYMBOL using AnsiString, like this:

Map((x) => x.Symbol).Column("SYMBOL").CustomType("AnsiString").Not.Nullable();

But it doesn't work.

I tried too change type of column for NVARCHAR2 and change FluentNHibernate Mapping for:

Map((x) => x.Symbol).Column("SYMBOL").CustomSqlType("NVARCHAR2").Not.Nullable();

But it doesn't work too.

How can I get it working?

Felipe Oriani
  • 37,948
  • 19
  • 131
  • 194
Douglas Cinto
  • 21
  • 3
  • I may not have expressed myself well, but my problem occurs the moment I save in the Symbol column, in the table the character ¿ is saved this way and I would like you to save the € character. – Douglas Cinto Dec 07 '17 at 15:58
  • .NET Strings are Unicode. You are trying tyo save a *Unicode* string to an ASCII field with some unknown codepage. Unicode characters that can't be mapped to that codepage will be replaced with an error character. Change the *field's* type to NARCHAR2 and don't specify a CustomType. – Panagiotis Kanavos Jan 04 '18 at 13:47

1 Answers1

0

You could use NVARCHAR2 for all strings in your app if they are all NVARCHAR2 in the DB (otherwise you might get performance problems because of conversions to VARCHAR2) - but you need at least NHibernate 5.0 for it. Set this prop in NHibernate config to true: oracle.use_n_prefixed_types_for_unicode

nHibernateConfig.SetProperty("oracle.use_n_prefixed_types_for_unicode", "true");

A better solution, if you have mixed VARCHAR2 and NVARCHAR2 columns, is to use a custom type for NVARCHAR2 columns/properties:

public class UnicodeStringType : IUserType
{
    public bool Equals(object x, object y)
    {
        if (ReferenceEquals(x, y)) return true;
        if (x == null || y == null) return false;
        return x.Equals(y);
    }

    public int GetHashCode(object x)
    {
        return x.GetHashCode();
    }

    public object NullSafeGet(IDataReader rs, string[] names, object owner)
    {
        var value = NHibernateUtil.String.NullSafeGet(rs, names[0]) as string;
        return value;
    }

    public void NullSafeSet(IDbCommand cmd, object value, int index)
    {
        var parameter = (IDataParameter)cmd.Parameters[index];
        ((OracleParameter)parameter).OracleDbType = OracleDbType.NVarchar2;
        parameter.Value = value;
    }

    public object DeepCopy(object value)
    {
        return value;
    }

    public object Replace(object original, object target, object owner)
    {
        return original;
    }

    public object Assemble(object cached, object owner)
    {
        return DeepCopy(cached);
    }

    public object Disassemble(object value)
    {
        return DeepCopy(value);
    }

    public SqlType[] SqlTypes => new[] { new SqlType(DbType.String) };
    public Type ReturnedType => typeof(string);
    public bool IsMutable => false;
}

You use it like this in mappings:

Map((x) => x.Symbol).Column("SYMBOL").CustomType<UnicodeStringType>().Not.Nullable();
Robert J
  • 704
  • 10
  • 8