Failed to UPDATE or INSERT using NHibernate

Question

I'm using NHibernate on a legacy database with Oracle 8i client. I can perform Get and Delete on the database table, but can't save or update entries. The exception is as follow, the sqlString consists of question marks and I don't know why.

Nhibernate.Exceptions.GenericADOException:

    {"could not update: [MIAP.Domain.Entities.MITFORG#3][SQL: UPDATE MITFORG SET TREELEVEL = ?, PARTENTID = ?, FORGNAME = ?, FORGINFO = ?, ACTIVE = ?, MUTATOR = ?, INPDATETIME = ?, UPDDATETIME = ? WHERE FORGID = ?]"}

Here are my entity class and mapping:

public class MITFORG {
    private long fORGID;
    private long? tREELEVEL;
    private long? pARTENTID;
    private string fORGNAME;
    private string fORGINFO;
    private string aCTIVE;
    private long? mUTATOR;
    private DateTime? iNPDATETIME;
    private DateTime? uPDDATETIME;
    public MITFORG() { }
    public virtual long FORGID {
        get {
            return this.fORGID;
        }
        set {
            this.fORGID = value;
        }
    }
    public virtual long? TREELEVEL
    {
        get {
            return this.tREELEVEL;
        }
        set {
            this.tREELEVEL = value;
        }
    }
    public virtual long? PARTENTID
    {
        get {
            return this.pARTENTID;
        }
        set {
            this.pARTENTID = value;
        }
    }
    public virtual string FORGNAME {
        get {
            return this.fORGNAME;
        }
        set {
            this.fORGNAME = value;
        }
    }
    public virtual string FORGINFO {
        get {
            return this.fORGINFO;
        }
        set {
            this.fORGINFO = value;
        }
    }
    public virtual string ACTIVE {
        get {
            return this.aCTIVE;
        }
        set {
            this.aCTIVE = value;
        }
    }
    public virtual long? MUTATOR
    {
        get {
            return this.mUTATOR;
        }
        set {
            this.mUTATOR = value;
        }
    }
    public virtual DateTime? INPDATETIME
    {
        get {
            return this.iNPDATETIME;
        }
        set {
            this.iNPDATETIME = value;
        }
    }
    public virtual DateTime? UPDDATETIME
    {
        get {
            return this.uPDDATETIME;
        }
        set {
            this.uPDDATETIME = value;
        }
    }
}



<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping assembly="MIAP.Domain" namespace="MIAP.Domain.Entities" xmlns="urn:nhibernate-mapping-2.2">
  <class name="MITFORG" table="MITFORG" lazy="true" >
    <id name="FORGID">
      <generator class="assigned" />
    </id>
    <property name="TREELEVEL"></property>
    <property name="PARTENTID"></property>
    <property name="FORGNAME"></property>
    <property name="FORGINFO"></property>
    <property name="ACTIVE"></property>
    <property name="MUTATOR"></property>
    <property name="INPDATETIME"></property>
    <property name="UPDDATETIME"></property>
  </class>
</hibernate-mapping>

I've checked property names and table column names. Since the FORGID is assigned by the application, I changed the generator class to "assigned". It doesn't work with "identity" either. Could someone please point me the direction to debug this?

Edited: Code to save entries

    Dim mitforgRepository As New MITFORGRepository
    Dim mitforg As MITFORG = mitforgRepository.GetById(3)
    mitforg.FORGINFO = "T"
    mitforg.ACTIVE = "Y"
    mitforg.FORGINFO = "T"
    mitforg.INPDATETIME = Now
    mitforg.MUTATOR = 324
    mitforg.PARTENTID = 335
    mitforg.TREELEVEL = 1
    mitforg.UPDDATETIME = Now
    mitforgRepository .Save(mitforg)

And here is the Repository class:

using System;
using System.Collections.Generic;
using System.Text;
using NHibernate;
using MIAP.Domain.Entities;

namespace MIAP.Domain.Repositories
{
    public class MITFORGRepository : IRepository<MITFORG, Int64?>
    {
        private static ISession GetSession()
        {
            return SessionProvider.SessionFactory.OpenSession();
        }

        public MITFORG GetById(Int64? id)
        {
            using (ISession session = GetSession())
            {
                return session.Get<MITFORG>(id);
            }
        }

        public void Save(MITFORG saveObj)
        {
            using (ISession session = GetSession())
            {
                using (ITransaction trans = session.BeginTransaction())
                {
                    session.SaveOrUpdate(saveObj);
                    trans.Commit();
                }
            }
        }

        public void Delete(MITFORG delObj)
        {
            using (ISession session = GetSession())
            {
                using (ITransaction trans = session.BeginTransaction())
                {
                    session.Delete(delObj);
                    trans.Commit();
                }
            }
        }
    }
}

The InnerException is System.Data.OracleClient.OracleException, ORA-12571

And here's the stack trace:

   於 System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
   於 System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
   於 System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
   於 System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
   於 NHibernate.AdoNet.AbstractBatcher.ExecuteNonQuery(IDbCommand cmd) 於 c:\Users\oskar.berggren\Documents\Projects\nhibernate-core-3\src\NHibernate\AdoNet\AbstractBatcher.cs: 行 203
   於 NHibernate.AdoNet.NonBatchingBatcher.AddToBatch(IExpectation expectation) 於 c:\Users\oskar.berggren\Documents\Projects\nhibernate-core-3\src\NHibernate\AdoNet\NonBatchingBatcher.cs: 行 40
   於 NHibernate.Persister.Entity.AbstractEntityPersister.Update(Object id, Object[] fields, Object[] oldFields, Object rowId, Boolean[] includeProperty, Int32 j, Object oldVersion, Object obj, SqlCommandInfo sql, ISessionImplementor session) 於 c:\Users\oskar.berggren\Documents\Projects\nhibernate-core-3\src\NHibernate\Persister\Entity\AbstractEntityPersister.cs: 行 2799

the question marks in the SQL are placeholders for parameters. The parameter values are usually at the end of the query. Could you add the code that tries to save the object? — Dirk Trilsbeek, Jun 22 '12 at 06:53
It's easier when you are posting an exception to just post the Exception.ToString(). — Cole W, Jun 22 '12 at 20:35

score 0 · Answer 1 · answered Jun 22 '12 at 07:56

0

If you're using assigned id's you can't use SaveOrUpdate as NHibernate won't know whether it's a new instance (ie. to Save/do an insert) or an existing instance(ie. to Update).

You need to then be explicit about whether you're inserting a new entity (session.Save) or updating an existing entity (session.Update).

answered Jun 22 '12 at 07:56

Martin Ernst

5,629
2
17
14

Thank you! I replaced session.SaveOrUpdate() with session.Update() in this case. But the same exception occurred as well at trans.Commit(). With session.Save(), it hangs at the same line and I'm still waiting. – begeeben Jun 22 '12 at 08:12
Do you have any anti-virus software running? – Martin Ernst Jun 22 '12 at 08:40
Yes, Trend OfficeScan client. I tried stopping it by ending NTRtScan.exe and TmListen.exe in taskmgr. But the code still doesn't seem to work. – begeeben Jun 22 '12 at 09:23
ORA-12571 happens if the connection gets interrupted - see http://www.dba-oracle.com/t_ora_12571_tns_packet_writer_failure.htm and http://garethhooper.com/articles/information-technology/oracle/163-ora-12571-tnspacket-writer-failure-one-of-the-hardest-problems-ive-had-to-resolve.html and http://stackoverflow.com/questions/2655666/what-is-the-difference-between-ora-12571-tns-packet-writer-failure-and-ora-0 – Martin Ernst Jun 22 '12 at 09:43
I'm trying the sqlnet.ora approach. But still wondering why get and delete work but save and update don't. What's the difference between them...? – begeeben Jun 22 '12 at 10:03
Thank you so much! Actually the cause isn't either one of them but you pointed me a correct direction. I'll post how I solve it shortly. – begeeben Jun 22 '12 at 11:52

score 0 · Answer 2 · answered Jun 22 '12 at 10:03

From the looks of it, you want to create a new object and save it into the database but what you are doing is loading the object using the Nhibernate Session and then updating the properties of it.. what this tells the Nhibernate session is that you have an object associated in the db with the given id and now you want to update certain properties when infact you want an insert statement to run.

Thus the right way is to create a new MitForg object and then call Session.SaveOrUpdate() on it and Nhibernate should take care of the insertion thereafter.

You could also try using Session.Merge().

Let me know if this works out for you..

score 0 · Accepted Answer · answered Jun 22 '12 at 15:18

Turns out it's a Unicode to ASCII conversion problem. I followed the solution in the links below. What's needed is adding a type attribute to all string type properties in the mapping file like this:

<property name="FORGNAME"  type="AnsiString" ></property>

Recently my colleague also encountered some kind of Unicode/ASCII conversion problem but I never thought it would have been the answer. The exception message is just misleading...

Thank Martin for the inspiring suggestion!

NHibernate and The Case of the Crappy Oracle NHibernate and ORA-12571 Errors

Failed to UPDATE or INSERT using NHibernate

3 Answers3