Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:
3.1
Mapping documents:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 1.1//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="testHib3.tstDACmain" table="tst_main" proxy="testHib3.tstDACmain">
<id column="id" name="id" type="long">
<generator class="assigned"></generator>
</id>
<property column="des" length="50" name="des" not-null="false" type="string"/>
<many-to-one name="externStd" column="id_extern_std" class="testHib3.tstDACExternStd"
cascade="none" outer-join="true" not-null="false"/>
<many-to-one name="externStatic" column="id_extern_static"
class="testHib3.tstDACExternStaticView" cascade="none"/>
</class>
</hibernate-mapping>
Code between sessionFactory.openSession() and session.close():
Connection con = hibSession.connection();

tstDACmain m1 = new tstDACmain();
m1.setId(new Long(-18));
m1.setDes("Test");

con.setAutoCommit(false); //This can be or cannot and nothing changes
Transaction t = hibSession.beginTransaction();

CallableStatement cs2 = con.prepareCall("SET IDENTITY_INSERT tst_main ON");
cs2.execute();
cs2.close();

hibSession.save(m1);
hibSession.flush(); //Here starts the error

Full stack trace of any exception that occurs:
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert explicit value for identity column in table 'tst_main' when IDENTITY_INSERT is set to OFF.
[omissis]
Name and version of the database you are using:
Ms-SQL Server 2005
The generated SQL (show_sql=true):
Hibernate: insert into tst_main (des, id_extern_std, id_extern_static, id) values (?, ?, ?, ?)

The problem is this:
- the CallableStatement is executed on different session in which is executed the final insert. I can see this from SQL Profiler, in which I can abserve that the "spid" changes from 55 to 56.


This appens only with Ms-SQL Server 2005, and all the version of JDBC drivers (sqljdbc.jar) I've tried: 1.0 and 1.1 (still in beta). The same program with MS-SQL Server 2000 and old JDBC drivers (msbase.jar+mssqlserver.jar+msutil.jar) works perfecly.

Are there any other way to mix Hibernate code (i.e. session.save();) with direct JDBC calls (i.e. "SET IDENTITY_INSERT tst_main ON")?

I've seen from MS-SQL Profiler that in 2005 there is a "set transaction isolation level read committed set implicit_transactions off" implicity added by JDBC driver (or who?) ... which where not present in 2000, doeas anyone knows how to disable it?

Any idea, suggestion, or help will be greatly appreciated ... this problem prevent us to use SQL 2005 and we have customers who wants this one ...

Regards
Alessandro Rizzi

Did you try to execute the same query Hibernate generates through your SQL client? Such as "insert into tst_main (des, id_extern_std, id_extern_static, id) values (1, 1, 1, 1)". It seems that your DB column is identity (meaning auto-generated), but your mapping says you are assigning a value to the ID property.

Suggests conflict with this:

Change the generator class for "identity" if you want an auto-generated value from the database.

Yes, and it works perfectly (with the "SET IDENTITY_INSERT tst_main ON")


Maybe I do not well explained my problem:
I have 2 mapping, the first is "identity" and it's used in the most part of the application. The second (the one in this example) must be "assigned" and is used in certain circumstances only ... so I have the identity on the table, but I must disable it some times ...
By the way the problem is more general for what I see in SQL Profiler: Hibernate code, and direct JDBC calls are in different sessions ...