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

Hibernate version: 3

We are using SQL Server 2000

We are trying to update/insert new row into a table that is reference in an Indexed View.

Default Setting for ARITHABORT on an Connection is OFF.

Any table that is reference in an indexed view with the connection setting for ARITHABORT OFF gives an exception saying:
INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'

How can I set this attribute on an hibernate session obejct before executing a save to insert/update rows into a table.


This is what I am looking for:

SET ARITHABORT ON
session.save(..)
SET ARITHABORT OFF


Thanks,
Malini

I have done this successfully -- at least setting ARITHABORT to ON. I haven't tried turning it off, but I don't know why it would ever be necessary to do so. We had the same need to enable ARITHABORT so that we can use indexed views... indexed views are about three times faster for our most common queries so turning on ARITHABORT is very useful!

I did it by including "SET ARITHABORT ON;" in the validation query on my connection pool. That ensures that every time a connection is added to the pool, it has ARITHABORT set to on. Because the settings of ARITHABORT apply for the rest of the session, all queries run with ARITHABORT on.

Exactly how do you do it? It depends on how you connect to the database. Here is what I have:
- Hibernate, uses
- DBCP Connection Pool, uses
- jDTS Driver, connects to
- SQL Server 2000

So I set ARITHABORT in the validation query for DBCP, like this:

You would need to change the column and table names in the SELECT query to something in your database.

Hi ,

I have a table with a computed indexed column. When the hibernate tries to update the rest of the columns, the following error is thrown.
My web application uses hibernate, spring and com.mchange.v2.c3p0.ComboPooledDataSource.

Hibernate: update gnp_main_number set std=?, cli=?, port=?, cease=?, remain=?, inService=? where id=?
16:12:27,330 WARN [JDBCExceptionReporter] SQL Error: 1934, SQLState: S0001
16:12:27,330 ERROR [JDBCExceptionReporter] UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
16:12:27,361 ERROR [AbstractFlushingEventListener] Could not synchronize database state with session
org.hibernate.exception.SQLGrammarException: could not update: [com.lifecycle.gnp.domain.GnpMainNumber#3687]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
----------
-----------

I found this post about setting "validationQuery" to "SET ARITHABORT ON". But I am unable to find where it should be set ? whether in the connection properties, or hibernate properties.

the connection conf is like
<bean id="commonDataSource" abstract="true" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${db.sqldriver}" />
<property name="minPoolSize" value="${db.minpoolsize}" />
<property name="maxPoolSize" value="${db.maxpoolsize}" />
<property name="testConnectionOnCheckout" value="true" />
<property name="preferredTestQuery" value="select name from sysobjects with (nolock) where name='sysobjects'" />
</bean>

<bean id="gnpDataSource" parent="commonDataSource">
<property name="jdbcUrl" value="${db.gnp.url}" />
<property name="user" value="${db.gnp.username}" />
<property name="password" value="${db.gnp.password}" />
</bean>

and the hibernate conf is
<bean id="gnpSessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="mappingDirectoryLocations">
<list>
<value>classpath:config/hibernate/gnp</value>
<value>classpath:config/hibernate/framework</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.use_outer_join">true</prop>
<prop key="hibernate.max_fetch_depth">4</prop>
<prop key="hibernate.lazy">false</prop>
<prop key="hibernate.query.substitutions">true 1, false 0</prop>
</props>
</property>
<property name="dataSource">
<ref bean="gnpDataSource" />
</property>
</bean>

Please advice me how to fix this issue? Thanks for your help in advance.

Regards
Kumaran