Session.Save problem

Radikal · **Joined:** Mon Jan 08, 2007 10:04 am **Posts:** 5

Hi everyone,

I've got a problem with NHibernate when i try to insert an object into the database.

My "Session.Load" works, so I don't think it comes from a problem of NHibernate configuration.

Here is the mapping file :

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   namespace="NH_Initiation" assembly="NH_Initiation">
  <class name="Service, App_Code" table="e_service">
    <id name="serviceID" column="no" type="Int32">
      <generator class="assigned"/>
    </id>
    <property name="serviceNOM" column="nom" type="string"/>
    <set name="Employes" cascade="all">
      <key column="service_no"/>
      <one-to-many class="Employe, App_Code"/>
    </set>
  </class>
</hibernate-mapping>

And here is the C# code :

Code:

Service newserv = new Service();
newserv.serviceID = 4;
newserv.serviceNOM = "Commercial";

ISession session = NHibernateHelper.GetCurrentSession();
ITransaction tx = session.BeginTransaction();
session.Save(newserv);
tx.Commit();
NHibernateHelper.CloseSession();

As you can see, it is pretty simple.
But I always get this error :

Code:

could not insert: [NH_Initiation.Service#4][SQL: INSERT INTO e_service (nom, no) VALUES (?, ?)]

As if NHibernate did not see this two lines :
newserv.serviceID = 4;
newserv.serviceNOM = "Commercial";

The database is an Oracle 8i Database but I don't think the problem is here.

Thank you for your help !

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

Radikal wrote:

.
But I always get this error :

Code:

could not insert: [NH_Initiation.Service#4][SQL: INSERT INTO e_service (nom, no) VALUES (?, ?)]

As if NHibernate did not see this two lines :
newserv.serviceID = 4;
newserv.serviceNOM = "Commercial";

The question mark represents a parameter. The [xx.Service#4] implies that it is using 4 as id. So, if You try to execute the statement by hand and subsitute the oarameters with corresponding values, can You insert the data?

Gert

Radikal · **Joined:** Mon Jan 08, 2007 10:04 am **Posts:** 5

gert wrote:

The question mark represents a parameter. The [xx.Service#4] implies that it is using 4 as id. So, if You try to execute the statement by hand and subsitute the oarameters with corresponding values, can You insert the data?

You mean insert the data directly with a classic "Insert" statement ?
If so, it works fine yes.

I dont know if it could be the reason of my problem but I noticed that the HQL insert statement is "INSERT INTO e_service(nom, no) VALUES ...".

I think it should be "INSERT INTO e_service(no,nom) VALUES ..."; but why would NHibernate change the parameters orders ?

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

Radikal wrote:

You mean insert the data directly with a classic "Insert" statement ?
If so, it works fine yes.

I dont know if it could be the reason of my problem but I noticed that the HQL insert statement is "INSERT INTO e_service(nom, no) VALUES ...".

I think it should be "INSERT INTO e_service(no,nom) VALUES ..."; but why would NHibernate change the parameters orders ?

So, propably the field order is incorrect. You can verify it by enabling logging so that You can verify parameter values...

If the order is incorrect, then there are two possibilities: You have discovered a bug in NHibernate, or You have error in Your mapping file. Make sure that NHibernate picks up latest mapping file (rebuild - not just build - the project if mapings are embedded as resources)

Gert

ChristianWendt · **Joined:** Mon Jan 29, 2007 12:13 pm **Posts:** 3

I am having exactly the same problem, and exactly the same erraneous INSERT statement with inverse columns (Name, Id) and the question marks for values.

I am also getting an "Error in CREATE Statement" when I am using the hbm2ddl.auto option.

While the initial poster uses Oracle, I get the error with both Jet and SQL Server, so I wouldn't think it has to do with database connection but that it must be something else.

Can it have to do with me using Visual C# Express 2005?

This is the hibernate.cfg.xml I have tried using lately. It is loaded from source code implicitly pointing to the file, cause I have yet to find a definite information where NHibernate looks for which file, in fact I am unable to find any useful documentation at all.

I have tried a multitude of configs, configuration from code as well, and it never seems to work.

Code:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
   <!-- Add this element -->
   <configSections>
      <section
      name="hibernate-configuration"
      type="NHibernate.Cfg.ConfigurationSectionHandler, NHibernate"
/>
   </configSections>
   <!-- Add this element -->
   <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
      <session-factory>
         <property name="dialect">NHibernate.Dialect.MsSql2000Dialect</property>
         <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
         <property name="connection.connection_string">Data Source=.\SQLEXPRESS;AttachDbFilename=C:\CS_PROJECTS\SQLServerApplication\SQLServerApplication\Data.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True</property>
      </session-factory>
   </hibernate-configuration>
</configuration>

Mapping config in User.hbm.xml :

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
    <class name="HibernateApp.User, HibernateApp" table="User">
        <id name="Id" column="Id"> 
            <generator class="assigned" /> 
        </id> 
        <property name="Name" column="Name"/>
    </class>
</hibernate-mapping>

It would be much easier to get started if one could download a basic project that was working. The samples that come with the installation are pretty much useless for a beginner, because every single one has a different way of setting up the project (App.config, Web.config, hibernate.cfg.xml), and none of them will compile.

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

"User" is reserved word in MS SQL server. Either change the table name or quote the name in mapping file (not sure what character was used for quoting.. "`" maybe. I tend to not use reserved words.)

Gert

samueljob · **Joined:** Mon Jan 08, 2007 11:59 pm **Posts:** 31

yes the character to quote is ` (back-tick)

ChristianWendt · **Joined:** Mon Jan 29, 2007 12:13 pm **Posts:** 3

Thanks for the replies but I don't think that's a problem because I could create table "USER" in SQLServer just fine.

Up to now I always copied NHibernate.dll and only the other necessary references into a subfolder of my project, referenced them from there, and I always got the aforementioned errors, no matter which DBMS I used.

I have now managed to use NHibernate for the first time by copying the whole content of NHibernate/bin into my project folder, not just the referenced dll's.

I still don't understand how this should be connected to the INSERT Statement not working but at least it means my problems can be reproduced and hopefully avoided in the future.

As I said, a setup example or "empty" project would be nice for new users, hopefully the community can supply NHibernate with such resources in the future.

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

ChristianWendt wrote:

Thanks for the replies but I don't think that's a problem because I could create table "USER" in SQLServer just fine.

Maybe.

But you can not execute following stement in SQLServer:
insert into user values (1, 'test');
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'user'.

Following statements can be executed
insert into "user" values (1, 'test');
insert into [user] values (1, 'test');

But You must tell NHibernate to quote the table name, it does not have keyword list built in.

Gert

ChristianWendt · **Joined:** Mon Jan 29, 2007 12:13 pm **Posts:** 3

That is very helpful information Gert, thank you very much. I had problems with keywords (TABLE etc) in other situations (ADO.NET) before, so I will remember your warning.

I was just more concerned with my inability to get past the first Commit, and I was close to giving up on NHibernate for good.

It looks now like it was definitely a case of NHibernate not finding a dll / manifest (and not telling me). Since I am using the whole bin stuff my programs / mappings work fine no matter if I use SQL Server, Jet, hbm2ddl.auto, etc. I don't know if it was the problem of the topic starter but it definitely was the the problem in my case.

ensoft · **Joined:** Thu Dec 21, 2006 11:38 am **Posts:** 30

By the way, the reason why "Nom" is in the insert statement before "No" is directly due to the ordering of the mapping file.

This can become very important when dealing with sprocs. Ensure the ordering of the mapping lines is exactly how you want it. If you're using sprocs, ensure the ordering of the mapped properties exactly matches the ordering of any procs used by NHibernate/Hibernate.

HTH!