SELECT causes INSERT?!

GrønnDemon · **Joined:** Thu Dec 01, 2005 1:09 pm **Posts:** 33

I'm experiencing very weird behaviour.
Whilst my code generally works well, it suddenly executes an INSERT statement when I actually want it to do a SELECT.

What can be causing this?

Hibernate version:

NHibernate 1.0

Mapping documents:

I've omitted everything I didn't consider relevant and shortened all names for privacy reasons.

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">

  <class name="VData" table="V">
    <id name="ID" column="V_ID" type="Int32" unsaved-value="-1">
      <generator class="MySerialGenerator" />
    </id>

    <!-- some more properties and relations -->
    <!-- some more properties like ChangedAt etc. -->

  </class>

  <class name="OData" table="O">

    <id name="ID" column="O_ID" unsaved-value="-1">
      <generator class="MySerialGenerator"></generator>
    </id>

    <property name="Name" column="DESCRIPTION" type="String" />

    <!-- some more properties like ChangedAt etc. -->

  </class>

  <class name="VOData" table="VO">

    <id name="ID" column="VO_PK" unsaved-value="-1">
      <generator class="MyPrimaryKeyGenerator"></generator>
    </id>

    <property name="VID" column="V_ID" type="Int32" />
    <property name="Name" column="DESCRIPTION" type="String" />

    <!-- some more properties -->

    <many-to-one name="O" class="OData"   column="O_ID" cascade="none" />
    <many-to-one name="OC" class="OCData" column="OC_FK" cascade="none" />
    <many-to-one name="OT" class="OTData" column="OT_FK" cascade="none" />
         
    <!-- some more properties like ChangedAt etc. -->

  </class>
   
  <class name="OCData" table="OC">

    <id name="ID" column="OC_PK" unsaved-value="-1">
      <generator class="assigned"></generator>
    </id>

    <property name="Name" column="DESCRIPTION" type="String" />

    <!-- another property -->
    <!-- some more properties like ChangedAt etc. -->

  </class>
   
  <class name="OTData" table="OT">

    <id name="ID" column="OT_PK" unsaved-value="-1">
      <generator class="assigned"></generator>
    </id>

    <property name="Name" column="BEZEICHNUNG" type="String" />

    <!-- another property -->
    <!-- some more properties like ChangedAt etc. -->

  </class>
  
</hibernate>

Code between sessionFactory.openSession() and session.close():

Again, simplified:

Code:

public void Foo( int vID, IList vOs )
{
  ISession session = SessionFactory.OpenSession();
  ITransaction tx = null;
  try
  {
    tx = session.BeginTransaction( );
    Foo( session, vID, vOs );
    tx.Commit( );
  }
  catch ( Exception e )
  {
    if ( tx != null )
      tx.Rollback( );
      log.Error( e.Message, e );   
      throw e;
  }
  finally
  {
    session.Close( );
    session.Dispose( );
  }
}

public void Foo(ISession session, int vID, IList vOs )
{
  foreach ( IVOData vO in vOs )
  {
    IOData o = vO.O;

    if (o == null)
      throw new Exception("O must not be null!");
            
    if ( o.ID == -1 )
    {
   //not saved yet

        //try to get the ID from the name
        int oID = GetOIDByName(session, o.Name);
            
        if ( oID == -1 )
          session.SaveOrUpdate( o );
        else
          ((OData) o ).ID = oID;   
    }

    VOData vtemp = (VOData)vO;

    vtemp.VID = vID;
            
    session.SaveOrUpdate( vtemp );
  }
}

public int GetOIDByName( ISession session, string name )
{
  IQuery query = session.CreateQuery( "[color=red]select[/color] od.ID from OData od where od.Name = :name " );
  query.SetString( "name", name );

  [color=red]IList list = query.List( ); //this is where INSERT is executed[/color]

  if (list.Count > 0 )
    return (int)list[0];

  return -1;
}

Full stack trace of any exception that occurs:

Irrelevant as it only says the INSERT failed - which shouldn't have occured anyway.

Name and version of the database you are using:

Gupta SQLBase 9.0.1

Debug level Hibernate log excerpt:

Code:

2006-08-02 18:47:10,373 [4384] DEBUG NHibernate.Impl.SessionImpl - find: select od.ID from OData od where od.Name = :name 
2006-08-02 18:47:10,373 [4384] DEBUG NHibernate.Engine.QueryParameters - named parameters: {name=SomeDescription}
2006-08-02 18:47:10,373 [4384] DEBUG NHibernate.Impl.SessionImpl - flushing session
2006-08-02 18:47:10,373 [4384] DEBUG NHibernate.Impl.SessionImpl - Flushing entities and processing referenced collections
2006-08-02 18:47:10,373 [4384] DEBUG NHibernate.Persister.AbstractEntityPersister - VOData.AnotherProperty is dirty
2006-08-02 18:47:10,373 [4384] DEBUG NHibernate.Impl.SessionImpl - Updating entity: [VOData#105228]
2006-08-02 18:47:10,373 [4384] DEBUG NHibernate.Persister.AbstractEntityPersister - VOData.AnotherProperty is dirty
2006-08-02 18:47:10,373 [4384] DEBUG NHibernate.Impl.SessionImpl - Updating entity: [VOData#105229]
2006-08-02 18:47:10,373 [4384] DEBUG NHibernate.Persister.AbstractEntityPersister - VOData.AnotherProperty is dirty
2006-08-02 18:47:10,373 [4384] DEBUG NHibernate.Impl.SessionImpl - Updating entity: [VOData#105230]
2006-08-02 18:47:10,373 [4384] DEBUG NHibernate.Persister.AbstractEntityPersister - VOData.AnotherProperty is dirty
2006-08-02 18:47:10,373 [4384] DEBUG NHibernate.Impl.SessionImpl - Updating entity: [VOData#105231]
2006-08-02 18:47:10,373 [4384] DEBUG NHibernate.Impl.SessionImpl - Processing unreferenced collections
2006-08-02 18:47:10,373 [4384] DEBUG NHibernate.Impl.SessionImpl - scheduling collection removes/(re)creates/updates
2006-08-02 18:47:10,373 [4384] DEBUG NHibernate.Impl.SessionImpl - Flushed: 5 insertions, 4 updates, 0 deletions to 5 objects
2006-08-02 18:47:10,383 [4384] DEBUG NHibernate.Impl.SessionImpl - Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
2006-08-02 18:47:10,383 [4384] DEBUG NHibernate.Impl.Printer - listing entities:
2006-08-02 18:47:10,383 [4384] DEBUG NHibernate.Impl.Printer - OData{ID=851, CreatedBy=1, ChangedBy=-1, Name=SomeString, Description=null, CreatedAt=02.08.2006, ChangedAt=01.01.0001}
2006-08-02 18:47:10,383 [4384] DEBUG NHibernate.Impl.Printer - VOData{VID=3, O=OData#851, OT=OTData#100040, ID=105231, OC=OCData#2147483647, some more properties...}
2006-08-02 18:47:10,383 [4384] DEBUG NHibernate.Impl.Printer - VOData{VID=3, O=OData#24, OType=OTData#100040, Name=Some Stuff, ID=105230, OC=OCData#2147483647, more properties....}
2006-08-02 18:47:10,383 [4384] DEBUG NHibernate.Impl.Printer - VOData{VID=3, O=OData#24, OT=OTData#100040, Name=Something, ID=105229, OC=OCData#2147483647, more properties....}
2006-08-02 18:47:10,383 [4384] DEBUG NHibernate.Impl.Printer - VOData{VID=3, O=OData#23, OT=OTData#100040, Name=Unimportant, ID=105228, OC=OCData#2147483647, more properties...}
2006-08-02 18:47:10,383 [4384] DEBUG NHibernate.Impl.SessionImpl - changes must be flushed to space: O
2006-08-02 18:47:10,383 [4384] DEBUG NHibernate.Impl.SessionImpl - Need to execute flush
2006-08-02 18:47:10,383 [4384] DEBUG NHibernate.Impl.SessionImpl - executing flush
2006-08-02 18:47:10,383 [4384] DEBUG NHibernate.Persister.EntityPersister - Inserting entity: [VOData#105228]
2006-08-02 18:47:10,383 [4384] DEBUG NHibernate.Impl.BatcherImpl - Opened new IDbCommand, open IDbCommands :1
2006-08-02 18:47:10,383 [4384] DEBUG NHibernate.Impl.BatcherImpl - Building an IDbCommand object for the SqlString: INSERT INTO VO (V_ID, VO_ID, OT_FK, DESCRIPTION, OC_FK, VO_PK, [more columns]) VALUES (:V_ID, :VO_ID, :OT_FK, :DESCRIPTION, :OC_FK, :VO_PK, [more parameters])
2006-08-02 18:47:10,383 [4384] DEBUG NHibernate.Persister.EntityPersister - Dehydrating entity: [VOData#105228]

[some more debugging stuff]

2006-08-02 18:47:11,675 [4384] DEBUG NHibernate.Util.ADOExceptionReporter - could not insert: [VOData#105230]
Gupta.SQLBase.Data.SQLBaseException: Failed to execute sql statement: INSERT INTO VO (V_ID, VO_ID, OT_FK, DESCRIPTION, OC_FK, DESCRIPTION, VO_PK, [more columns]) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16). Please check additional information below.
Additional SQLBase message:
00805 ROW NUD Insert/update of unique constrained columns with duplicate data
      Reason: The target table of an INSERT or UPDATE operation is constrained
         (by a CREATE UNIQUE INDEX statement) to have unique values in
         certain columns.

[above message is repeated for several other VOData objects]

gert · **Joined:** Thu Jan 19, 2006 4:29 pm **Posts:** 348

GrønnDemon wrote:

I'm experiencing very weird behaviour.
Whilst my code generally works well, it suddenly executes an INSERT statement when I actually want it to do a SELECT.

What can be causing this?

NHibernate tries to ensure that database conents is up-to-date before making any queries. You can chnage this by changing the value of session.FlushMode;

Or, if You flush the session manually before the select, then, of course, there will be no additional inserts made :)

You problem seems to be that NHibernate thinks about some saved objects that they are unsaved and tries to insert them into database. SaveOrUpdate uses AFAIK two unsaved-value properties to determine if make an insret or not: <id> and <version>(<timesptamp>) Make sure that neither of them have unsaved-value at the moment You call SaveOrUpdate

Gert

GrønnDemon · **Joined:** Thu Dec 01, 2005 1:09 pm **Posts:** 33

Ok! But the VOData object must not be saved yet, for I am selecting data I want to assign one of its properties.

I'll try setting the flush mode for the query and I'm going to post the results here.

Thanks a lot for your help.

GrønnDemon · **Joined:** Thu Dec 01, 2005 1:09 pm **Posts:** 33

The following did the trick:

Code:

//save the old FlushMode
FlushMode fm = session.FlushMode;

//prevent unwanted INSERTs
session.FlushMode = FlushMode.Never;

//execute query
IList list = query.List( );

//restore the old FlushMode
session.FlushMode = fm;

In Hibernate (i.e. Java), you can set the flush mode for a single query.
Is this implemented in NHibernate > 1.0?

Again, thank you for your help.