-->
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.  [ 3 posts ] 
Author Message
 Post subject: Problem with inserting or updating decimal in MS Access
PostPosted: Thu Apr 17, 2008 3:50 am 
Newbie

Joined: Thu Apr 17, 2008 2:03 am
Posts: 2
NHibernate version: 1.2.1
Name and version of the database you are using: MS Access

Hello!

We have problem when updating or inserting a decimal field
in Microsoft Access database. The problem only happens if the decimal
symbol is "," in Windows Regional settings.

Error stack:

NHibernate.ADOException: could not update: [RIT.AUC.BLL.Interface.TangibleItem#5f23443f-c7fb-4e55-b4f8-47bdeeb14e76][SQL: UPDATE Items SET ItemQuantity = ?, InventoryNumber = ?, Location = ?, ProductionYear = ?, UnitID = ?, Name = ?, SellPrice = ?, EstimatePrice = ?, BalancePrice = ?, ItemNote = ?, IsSold = ?, AuctionId = ?, LotId = ?, AMAManagerProcedureId = ?, AMAItemId = ?, AMAGroupId = ? WHERE Id = ?] ---> System.Data.OleDb.OleDbException: Data type mismatch in criteria expression
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at NHibernate.JetDriver.JetDbCommand.ExecuteNonQuery()
at NHibernate.Impl.NonBatchingBatcher.AddToBatch(IExpectation expectation)
at NHibernate.Persister.Entity.AbstractEntityPersister.Update(Object id, Object[] fields, Object[] oldFields, Boolean[] includeProperty, Int32 j, Object oldVersion, Object obj, SqlCommandInfo sql, ISessionImplementor session)
--- End of inner exception stack trace ---
at NHibernate.Persister.Entity.AbstractEntityPersister.Update(Object id, Object[] fields, Object[] oldFields, Boolean[] includeProperty, Int32 j, Object oldVersion, Object obj, SqlCommandInfo sql, ISessionImplementor session)
at NHibernate.Persister.Entity.AbstractEntityPersister.Update(Object id, Object[] fields, Int32[] dirtyFields, Boolean hasDirtyCollection, Object[] oldFields, Object oldVersion, Object obj, ISessionImplementor session)
at NHibernate.Impl.ScheduledUpdate.Execute()
at NHibernate.Impl.SessionImpl.Execute(IExecutable executable)
at NHibernate.Impl.SessionImpl.ExecuteAll(IList list)
at NHibernate.Impl.SessionImpl.Execute()
at NHibernate.Impl.SessionImpl.Flush()
at NHibernate.Transaction.AdoTransaction.Commit()



The same error also exists in .Net Oledb when using command parameters without types. Example code of this:

string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"test.mdb\";";
OleDbConnection connection = new OleDbConnection(ConnectionString);

string insertcommand = "INSERT INTO Test (DecimalField, Id) VALUES (?, ?)";
OleDbCommand command = new OleDbCommand(insertcommand, connection);
command.Parameters.Add(new OleDbParameter("@DecimalField", 999.99M));
command.Parameters.Add(new OleDbParameter("@Id", Guid.NewGuid()));

connection.Open();
command.ExecuteNonQuery();


But in this case problem solved when using decimal parameter with OleDbType.Currency like:

OleDbParameter param = new OleDbParameter("@DecimalField", OleDbType.Currency);
param.Value = 999.99M;
command.Parameters.Add(param);


Can the problem be solved without changing nhibernate source codes?

Thanks for your help.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 24, 2008 7:11 am 
Beginner
Beginner

Joined: Tue Jan 22, 2008 11:33 am
Posts: 46
I face the same problem. Have you found a solution for that behavior?
Thanks fro your feedback antoschka


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 24, 2008 8:34 am 
Newbie

Joined: Thu Apr 17, 2008 2:03 am
Posts: 2
Hello!

To solve the problem we create class which implement interface NHibernate.UserTypes.IUserType for money type. Then all decimal fields in hbm files are maped to this class.

Code of the class:

Code:
   
    public class Money : IUserType
    {
        public bool IsMutable
        {
            get { return false; }
        }

        public Type ReturnedType
        {
            get { return typeof(Money); }
        }

        public SqlType[] SqlTypes
        {
            get
            {
                global::NHibernate.SqlTypes.SqlType[] types = new global::NHibernate.SqlTypes.SqlType[1];
                types[0] = new global::NHibernate.SqlTypes.SqlType(DbType.Currency);
                return types;
            }

        }

        public object NullSafeGet(IDataReader rs, string[] names, object owner)
        {
            object val = NHibernateUtil.Decimal.NullSafeGet(rs, names[0]);
            if (val == null) return null;
            else return val;
        }

        public void NullSafeSet(IDbCommand cmd, object value, int index)
        {
            if (value == null)
            {
                ((IDataParameter)cmd.Parameters[index]).Value = DBNull.Value;
            }
            else
            {
                ((IDataParameter)cmd.Parameters[index]).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 cached;
        }

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

        public new 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();
        }
    }


sample of mapping in hbm:

<property name="DecimalField" type="Money" />


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 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.