Hi,

I am using Hibernate 3.3. I am trying to connect to two different databases, Oracle10g and Sybase 5.5.

I have a table in Oracle and the primary key in the table is generated using a sequence. I am using annotations for most of the configurations. I am using the following two annotations in the model class:
1. At the class level - @SequenceGenerator(name = "chewie_pricing_stag_seq", sequenceName = "chewie_pricing_stag_seq", allocationSize = 1)

2. At the variable level - @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "chewie_pricing_stag_seq")

And my dialect is Oracle10gDialiect

Using these annotations when I try to insert data into this table by configuring only the Oracle datasource the row is inserted successfully.

But if I have the configuration for Sybase in the same applicationContext.xml, then it throws an exception saying 'Dialect does not support Sequences'

Below is the applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/b ... ns-2.5.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/t ... tx-2.5.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/a ... op-2.5.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/c ... xt-2.5.xsd">

<!-- bean id="sybasedataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
<property name="jndiName">
<value>lpa.pricing</value>
</property>
</bean> -->

<bean id="sybasedataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="oracle.jdbc.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@localhost:1521:XE"/>
<property name="username" value="system"/>
<property name="password" value="manager"/>
</bean>


<bean id="sybasetransactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="dataSource">
<ref local="sybasedataSource"/>
</property>
<property name="sessionFactory">
<ref local="sybasesessionFactory"/>
</property>
</bean>

<bean id="sybasesessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource">
<ref local="sybasedataSource"/>
</property>
<property name="configLocations">
<value>hibernate1.cfg.xml</value>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">
org.hibernate.dialect.Oracle10gDialect
</prop>
<prop key="hibernate.query.substitutions">
true=1 false=0
</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.format_sql">true</prop>
<prop key="hibernate.cache.provider_class">
org.hibernate.cache.EhCacheProvider
</prop>
<prop key="hibernate.cache.use_query_cache">true</prop>
<prop
key="hibernate.cache.provider_configuration_file_resource_path">
./hibernate/ehcache.xml
</prop>
<prop key="hibernate.jdbc.batch_size">20</prop>
<prop key="hibernate.cache.use_second_level_cache">false</prop>
</props>
</property>

<property name="configurationClass">
<value>org.hibernate.cfg.AnnotationConfiguration</value>
</property>
</bean>



<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.sybase.jdbc2.jdbc.SybDriver"/>
<property name="url" value="jdbc:sybase:Tds:ldsd4112.wellsfargo.com:5400"/>
<property name="username" value="fetoprc"/>
<property name="password" value="tstgate"/>
</bean>


<bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager" >

<property name="sessionFactory">
<ref local="sessionFactory"/>
</property>
</bean>
<!-- bean id="pricingDAO" class="com.wfsc.lpa.chewie.dao.hbn.impl.PricingDetailsDAOImpl" >
<property name="sybasesessionFactory">
<ref local="sybasesessionFactory"/>
</property>
</bean> -->
<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean" autowire-candidate="false">
<property name="dataSource">
<ref local="dataSource"/>
</property>

<property name="configLocations">
<value>hibernate.cfg.xml</value>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">
org.hibernate.dialect.SybaseDialect
</prop>
<prop key="hibernate.query.substitutions">
true=1 false=0
</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.format_sql">true</prop>
<prop key="hibernate.cache.provider_class">
org.hibernate.cache.EhCacheProvider
</prop>
<prop key="hibernate.cache.use_query_cache">true</prop>
<prop
key="hibernate.cache.provider_configuration_file_resource_path">
./hibernate/ehcache.xml
</prop>
<prop key="hibernate.jdbc.batch_size">20</prop>
<prop key="hibernate.cache.use_second_level_cache">false</prop>
</props>
</property>

<property name="configurationClass">
<value>org.hibernate.cfg.AnnotationConfiguration</value>
</property>
</bean>
<context:annotation-config />
<aop:aspectj-autoproxy />
<context:component-scan base-package="com.wfsc.lpa.chewie.sybase.dao.hbn.impl">
</context:component-scan>
<context:component-scan base-package="com.wfsc.lpa.chewie.sybase.business.impl">
</context:component-scan>

<tx:annotation-driven transaction-manager="sybasetransactionManager"/>

</beans>

Below is the exception being thrown:

Caused by: org.hibernate.MappingException: could not instantiate id generator [entity-name=com.wfsc.lpa.chewie.sybase.model.domain.PricingDetails]
at org.hibernate.id.IdentifierGeneratorFactory.create(IdentifierGeneratorFactory.java:109)
at org.hibernate.mapping.SimpleValue.createIdentifierGenerator(SimpleValue.java:152)
at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:192)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1300)
at org.hibernate.cfg.AnnotationConfiguration.buildSessionFactory(AnnotationConfiguration.java:867)
at org.springframework.orm.hibernate3.LocalSessionFactoryBean.newSessionFactory(LocalSessionFactoryBean.java:814)
at org.springframework.orm.hibernate3.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:732)
at org.springframework.orm.hibernate3.AbstractSessionFactoryBean.afterPropertiesSet(AbstractSessionFactoryBean.java:211)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1369)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1335)
... 28 more
Caused by: org.hibernate.MappingException: Dialect does not support sequences
at org.hibernate.dialect.Dialect.getSequenceNextValString(Dialect.java:596)
at org.hibernate.id.SequenceGenerator.configure(SequenceGenerator.java:65)
at org.hibernate.id.SequenceHiLoGenerator.configure(SequenceHiLoGenerator.java:43)
at org.hibernate.id.IdentifierGeneratorFactory.create(IdentifierGeneratorFactory.java:104)

Thanks
Usha

I have no experience with either Oracle or Sybase, but this seems to be exactly as documented about ID generator: http://www.hibernate.org/hib_docs/v3/re ... -generator

With Sybase you should use the 'identity' generator. Or better, use the 'native' generator to let Hibernate select either the 'sequence' or 'identity' generator automatically.

According to the configuration that you have posted, your sybase beans are using the Oracle driver and configuration options, and your Oracle beans are using the sybase driver and options.

Try correcting that :)


--
Stephen Souness

Thank you for your responses.
As suggested if I use 'native' or with annotations 'strategy = GenerationType.AUTO' it works fine.
But the problem is my dao's use different session factories and each session factory is configured to use their respective datasources. Infact in my test application I dont have a DAO which used Sybase. But the minute I add the sybase configuration in the applicationContext.xml, the strategy 'strategy = GenerationType.SEQUENCE' in my Oracle model object fails.

I can go ahead and use the AUTO strategy but there is already code deployed using the SEQUENCE strategy. Now we may have to change all the code although those DAOs are not connecting to Sybase.

Is this the only solution?