-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 
Author Message
 Post subject: HELP - Oracle, Char columns and optimistic-lock (dirty)
PostPosted: Thu Apr 17, 2008 6:44 pm 
Newbie

Joined: Tue Apr 08, 2008 6:24 am
Posts: 8
PLEASE Can someone please help me? I'm desperate!!!!!

NHibernate keeps returning 0 row count on an updated when I have done a previos update.

There is a oracle char(60) column in my database called in_callers_adr

The first update sql is as follows:

Replace '_' with a space.

UPDATE in_cidents SET in_callers_adr = :p0 WHERE in_ref = :p1 AND in_callers_adr=:p2

:p0 - 'EX TEL Aas'
:p1 - 432699
:p2 - 'EX TEL A______________________________'

Then the second is:

UPDATE in_cidents SET in_callers_adr = :p0 WHERE in_ref = :p1 AND in_callers_adr=:p2

:p0 - 'EX TEL A'
:p1 - 432699
p2: - 'EX TEL Aas'

which fails because I'm assuming the where clause needs to contain a padded version of the current value e.g.

:p2 = 'EX TEL Aas___________________________'

Do I need to write a usertype or something

HELP


Top
 Profile  
 
 Post subject: Direct SQL hack
PostPosted: Fri Apr 18, 2008 7:19 am 
Newbie

Joined: Tue Apr 08, 2008 6:24 am
Posts: 8
Futher to my issue I have looked at the nhibernate code.

I couldn't see see anyway to change the way it generates the column names in the where clause via its extendibility architecture (e.g. dialect or driver).

Therefore on the oracle client driver I override the generatecommand method on the oracle driver and directly hack the sql generated to use a where .... AND rtrim(columnname)=:p0 .... etc

This is nasty, but it fixes the issue of nhibernate not finding a row to update.

Is there not any other way to do this?


Top
 Profile  
 
 Post subject:
PostPosted: Fri Apr 18, 2008 5:23 pm 
Expert
Expert

Joined: Fri Oct 28, 2005 5:38 pm
Posts: 390
Location: Cedarburg, WI
See this post
http://forum.hibernate.org/viewtopic.php?t=984281
Let me know if you need help creating the custom types.


Top
 Profile  
 
 Post subject: Some solutions?
PostPosted: Sat Apr 19, 2008 9:14 am 
Newbie

Joined: Tue Apr 08, 2008 6:24 am
Posts: 8
Nels_P_Olsen wrote:
See this post
http://forum.hibernate.org/viewtopic.php?t=984281
Let me know if you need help creating the custom types.


My investigation has lead me through these solutions

1) The problems I am having is that rtrim(column)=:pn will not work on a initial save because the value used for the initial dirty comparison will be padded and so that solution won't work.

2) My next attempt was to do a rtrim(column)=rtrim(:pn) for any string parameters, but this will not work for rtrim(null)=rtrim(null) as this isn't equal in oracle.

3) My final two options was to:

- Use a custom usertype to trim the initial value from the db (but this won't be driver specific) so I can use the 'rtrim(column)=:pn or (column is null and :pn is null)' solution.

- Or use 'rtrim(column)=rtrim(:pn) or (column is null and :pn is null)'

Anyone with any better ideas of how to solve my issue.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Apr 21, 2008 9:39 am 
Expert
Expert

Joined: Fri Oct 28, 2005 5:38 pm
Posts: 390
Location: Cedarburg, WI
The point of using the custom type is that NCHAR comparisons will work as expected. There will be no need to use rtrim(). You simply do
Code:
SomeEntity.SomeProperty = :someParameter

and it will compare properly whether someParameter is trimmed or padded.

There is one other issue with using NCHAR columns, it surfaces when you use NCHAR-based keys and ISession.Get() or ISession.Load(). See his post:
http://forum.hibernate.org/viewtopic.php?t=975743


Top
 Profile  
 
 Post subject:
PostPosted: Mon Apr 21, 2008 9:46 am 
Newbie

Joined: Tue Apr 08, 2008 6:24 am
Posts: 8
Nels_P_Olsen wrote:
The point of using the custom type is that NCHAR comparisons will work as expected. There will be no need to use rtrim(). You simply do
Code:
SomeEntity.SomeProperty = :someParameter

and it will compare properly whether someParameter is trimmed or padded.

There is one other issue with using NCHAR columns, it surfaces when you use NCHAR-based keys and ISession.Get() or ISession.Load(). See his post:
http://forum.hibernate.org/viewtopic.php?t=975743


Thanks for your reply.

But the problem is that I'm not using HQL or define any this is just and Update() call on the object that is causing this issue. So hibernate is generating a update sql query that will never get a matching row because it's using dirty optimistic-lock (due to legecy reasons).


Top
 Profile  
 
 Post subject:
PostPosted: Mon Apr 21, 2008 4:11 pm 
Expert
Expert

Joined: Fri Oct 28, 2005 5:38 pm
Posts: 390
Location: Cedarburg, WI
Try this and see if it resolves your problem
(change MyCompany.MyProduct appropriately ...)

Code:
using System;
using System.Collections;
using System.Data;

using NH = NHibernate;

namespace MyCompany.MyProduct.NHibernate.CustomTypes
{
    public class StringFixedLengthType : NH::Type.CustomType
    {
        private static StringFixedLengthType _instance;

        public static StringFixedLengthType Instance
        {
            get { return _instance; }
        }

        static StringFixedLengthType()
        {
            _instance = new StringFixedLengthType();
        }

        protected StringFixedLengthType() : base(typeof(MyCompany.MyProduct.NHibernate.UserTypes.StringFixedLengthType), new Hashtable())
        {
        }
    }
}

namespace MyCompany.MyProduct.NHibernate.UserTypes
{
    /// <summary>
    /// An <see cref="NH.UserTypes.IUserType"/> that reads a <c>null</c> value from an <c>StringFixedLength</c>
    /// column in the database as a <see cref="String.Empty">String.Empty</see>
    /// and writes a <see cref="String.Empty">String.Empty</see> to the database
    /// as <c>null</c>.  Trailing blanks are left as is when getting and setting
    /// from and to the database, but are ignored in comparison for equality.
    /// </summary>
    /// <remarks>
    /// This is intended to help with Windows Forms DataBinding and the problems associated
    /// with binding a null value.  See <a href="http://jira.nhibernate.org/browse/NH-279">
    /// NH-279</a> for the origin of this code.
    /// </remarks>
    public class StringFixedLengthType : NH::UserTypes.IUserType
    {
        #region Fields

        private static NH::Type.StringType _stringType;
        private static NH::SqlTypes.StringFixedLengthSqlType _stringFixedLengthSqlType;
        private static NH::SqlTypes.SqlType[] _sqlTypes;
        private static System.Data.DbType[] _dbTypes;

        #endregion

        #region Properties

        protected static NH::Type.StringType StringType
        {
            get { return _stringType; }
        }

        #endregion

        #region Constructors

        static StringFixedLengthType()
        {
            _stringType = (NH.Type.StringType)NH::NHibernateUtil.String;
            _stringFixedLengthSqlType = new NH::SqlTypes.StringFixedLengthSqlType();
            _sqlTypes = new NH::SqlTypes.SqlType[] { _stringFixedLengthSqlType };
            _dbTypes = new System.Data.DbType[] { System.Data.DbType.StringFixedLength };
        }

        public StringFixedLengthType()
        {
        }

        #endregion

        #region IUserType Members

        bool NH::UserTypes.IUserType.Equals(object x, object y)
        {
            if (object.ReferenceEquals(x, y))
            {
                return true;
            }

            string stringX =
                (x == null || x == DBNull.Value ? String.Empty : x.ToString().TrimEnd());

            string stringY =
                (y == null || y == DBNull.Value ? String.Empty : y.ToString().TrimEnd());

            bool result = (string.Compare(stringX, stringY) == 0);

            return result;
        }

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

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

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

        public NH::SqlTypes.SqlType[] SqlTypes
        {
            get
            {
                return _sqlTypes;
            }
        }

        public System.Data.DbType[] DbTypes
        {
            get
            {
                return _dbTypes;
            }
        }

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

        public virtual void NullSafeSet(IDbCommand cmd, object value, int index)
        {
            _stringType.NullSafeSet(cmd, value, index);
        }

        public System.Type ReturnedType
        {
            get
            {
                return typeof(string);
            }
        }

        public virtual object NullSafeGet(System.Data.IDataReader rs, string[] names, object owner)
        {
            return _stringType.NullSafeGet(rs, names);
        }

        public bool IsMutable
        {
            get
            {
                return false;
            }
        }

        public virtual int GetHashCode(object obj)
        {
            return obj.GetHashCode();
        }

        #endregion
    }
}


Then whenever you set a query parameter, instead of doing just this:

Code:
query.SetParameter(parameter.Name, parameter.Value);


You have to do this:

Code:
if (parameter.Type == typeof(string))
{
    query.SetParameter(parameter.Name, parameter.Value,
        MyCompany.MyProduct.NHibernate.CustomTypes.StringFixedLengthType.Instance);
}
else
{
    query.SetParameter(parameter.Name, parameter.Value);
}


Fortunately we have front-ends to NHibernate query objects and parameters, so this is encapsulated for us ...

I guess the question is then, since you're having a problem with parameters in SQL statements that NHibernate automatically generates behind the scenes, does take the mapped custom type into account when it sets parameters on these internally generated SQL statements or not. If not, then someone else will need to look into this further. Ultimately what needs to happen is the SQL command parameters must be built like this:

Code:
OracleParameter parameter = new Oracle.DataAccess.Client.OracleParameter(parameterName, Oracle.DataAccess.Client.OracleDbType.NChar);


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.