Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Hibernate insert fails due to generate key failure

 
Nina Anderson
Ranch Hand
Posts: 148
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

My web application is able to read data from the database via Hibernate. However, it fails when it needs to do an INSERT with a generate key failure.

Has anyone come across this error? I've been bang my head for the past 1 week to figure it out and I'm getting frustrated.

I'm using the following:
Hibernate3
String
MS SQL Server 2005
Tomcat
Windows Vista

/////////////////////////////

Here's the error:

Hibernate: insert into RX.dbo.Busines (Place_Of_Business_Id, Business_Type_Id, Business_Name, Business_Email_Address, Business_Description) values (?, ?, ?, ?, ?)

Exception Ocurred: org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not insert: [com.RXCoreApp.datamodel.BusinessCbo]; uncategorized SQLException for SQL [insert into RX.dbo.Busines (Place_Of_Business_Id, Business_Type_Id, Business_Name, Business_Email_Address, Business_Description) values (?, ?, ?, ?, ?)]; SQL state [null]; error code [0]; The statement must be run before the generated keys are available.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be run before the generated keys are available.

Exception Ocurred (cause): com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be run before the generated keys are available.

Exception Ocurred (msg): Hibernate operation: could not insert: [com.RXCoreApp.datamodel.BusinessCbo]; uncategorized SQLException for SQL [insert into RX.dbo.Busines (Place_Of_Business_Id, Business_Type_Id, Business_Name, Business_Email_Address, Business_Description) values (?, ?, ?, ?, ?)]; SQL state [null]; error code [0]; The statement must be run before the generated keys are available.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be run before the generated keys are available.


///////////

Here's my applicationContext config:

<!-- Local DataSource that works in any environment -->

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">

<property name="driverClassName"><value>com.microsoft.sqlserver.jdbc.SQLServerDriver</value></property>

<property name="url"><value>jdbc:sqlserver://localhost:1433;databaseName=RX</value></property>

<property name="username"><value>sa</value></property>

<property name="password"><value>#something#</value></property>

</bean>


PLS HELP...
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you post your class mapping for the id field?

Thanks

Mark
 
Nina Anderson
Ranch Hand
Posts: 148
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My code was working perfectly (doing inserts) until I changed the server from websphere to Tomcat and Windows Vista OS.

here's my class mapping:

<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource">
<ref bean="dataSource" />
</property>
<property name="mappingResources">
<list>

<value>config/BusinessCbo.hbm.xml</value>
...

</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">com.PBCoreApp.hibernate.PBSQLServerDialect</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.max_fetch_depth">1</prop>
<prop key="hibernate.default_schema">PB</prop>

</props>

<bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate">
<property name="sessionFactory">
<ref bean="sessionFactory"/>
</property>
</bean>

<bean id="hibernateInterceptor" class="org.springframework.orm.hibernate3.HibernateInterceptor">
<property name="sessionFactory">
<ref bean="sessionFactory"/>
</property>
</bean>

<bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory"><ref local="sessionFactory"/></property>
</bean>

<bean id="businessBSTarget" class="com.PBCoreApp.business.impl.BusinessBSImpl">
<property name="businessDao"><ref local="businessDao" /></property></bean>
<bean id="businessBS" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
<property name="transactionManager"><ref bean="transactionManager"/></property>
<property name="target">
<ref local="businessBSTarget" />
</property>
<property name="transactionAttributes">
<props>
<prop key="accept*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="activate*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="add*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="decline*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="delete*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="generate*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="get*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="is*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="update*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="store*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="find*">PROPAGATION_REQUIRED,-Exception</prop>
</props>
</property>
</bean>

</property>
</bean>
<bean id="businessDao"
class="com.PBCoreApp.dataaccess.impl.BusinessDAOImpl">
<property name="hibernateTemplate">
<ref bean="hibernateTemplate"/>
</property>
</bean>
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That doesn't look like the Hibernate mapping for your object/class.

I wanted to see the hbm.xml file.



Thanks

Mark
 
Nina Anderson
Ranch Hand
Posts: 148
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
here is my hibernate class mapping:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
Mapping file autogenerated by MyEclipse - Hibernate Tools
-->
<hibernate-mapping>
<class name="com.RXCoreApp.datamodel.BusinessCbo" table="Busines" schema="dbo" catalog="RX">
<id name="businessId" type="java.lang.Integer">
<column name="Business_Id" />
<generator class="identity"></generator>
</id>
<many-to-one name="placeOfBusiness" class="com.PBCoreApp.datamodel.PlaceOfBusinessCbo" fetch="select">
<column name="Place_Of_Business_Id" />
</many-to-one>
<property name="businessTypeId" type="java.lang.Integer">
<column name="Business_Type_Id" />
</property>
<property name="businessName" type="java.lang.String">
<column name="Business_Name" />
</property>
<property name="businessEmailAddress" type="java.lang.String">
<column name="Business_Email_Address" not-null="true" />
</property>
<property name="businessDescription" type="java.lang.String">
<column name="Business_Description" />
</property>
<property name="lastUpdateTimestamp" type="java.util.Date" insert="false" update="false">
<column name="Last_Update_Timestamp"/>
</property>
<set name="businessPackages" inverse="true" cascade="all-delete-orphan">
<key>
<column name="Business_Id" />
</key>
<one-to-many class="com.PBCoreApp.datamodel.BusinessPackageCbo" />
</set>

...

</class>
</hibernate-mapping>


////////////////////////////
Save Method:

public void save(BusinessCbo businessCbo) {
log.debug("saving BusinessCbo instance");
try {

this.getHibernateTemplate().save(businessCbo);

log.debug("save successful");
} catch (RuntimeException re) {
log.error("save failed", re);
throw re;
}
}


public void saveOrUpdate(BusinessCbo businessCbo) {
log.debug("saving BusinessCbo instance");
try {

this.getHibernateTemplate().saveOrUpdate(businessCbo);

log.debug("save successful");
} catch (RuntimeException re) {
log.error("save failed", re);
throw re;
}
}


////Java method where it's invoked:

public void storeBusinessProfile(Integer businessId, BusinessDetailsDTO businessDetailDto)
{
AddressSecurityCbo addressSecurityCbo = new AddressSecurityCbo();
PhoneSecurityCbo phoneSecurityCbo = new PhoneSecurityCbo();
PlaceOfBusinessCbo placeOfBusinessCbo = new PlaceOfBusinessCbo();
PackageCbo packageCbo = new PackageCbo();
BusinessCbo businessCbo = new BusinessCbo();
BusinessPackageCbo businessPackageCbo = new BusinessPackageCbo();
AddressTypeCbo addrTypeCbo = new AddressTypeCbo();
AddressCbo addrCbo = new AddressCbo();
PhoneCbo businessPhoneCbo = new PhoneCbo();
PhoneCbo mobilePhoneCbo = new PhoneCbo();
PhoneCbo faxPhoneCbo = new PhoneCbo();

Set phoneSet = new HashSet();

//find addr type
List addrTypeList = addressTypeDao.findByAddressTypeName("Business");
Integer addrTypeId = null;
if(addrTypeList != null)
{
Iterator itr = addrTypeList.iterator();

while(itr.hasNext())
{
addrTypeCbo = (AddressTypeCbo) itr.next();
addrTypeId = addrTypeCbo.getAddressTypeId();
}
}

//find country
Integer countryId = null;
if( businessDetailDto.getCountryId() != null )
{
countryId = new Integer( businessDetailDto.getCountryId() );
}
CountryCbo countryCbo = countryDao.findById( countryId );

//save addr with type
addrCbo.setAddressLine1(businessDetailDto.getAddrLine1());
addrCbo.setAddressLine2(businessDetailDto.getAddrLine2());
addrCbo.setAddressTypeId(addrTypeCbo.getAddressTypeId());
addrCbo.setCityName(businessDetailDto.getCity());
addrCbo.setState( businessDetailDto.getState() );
addrCbo.setZipCode(businessDetailDto.getZipCode() );
addrCbo.setCountryId( countryCbo.getCountryId() );
AddressCbo savedAddressCbo = addressDao.save(addrCbo);

//save address security
storeOrUpdateAnonymousAddress( savedAddressCbo.getAddressId(), businessDetailDto.getMakeAddressAnonymous());

//save business package
packageCbo.setPackageId(businessDetailDto.getPackageId());
businessCbo.setBusinessId(businessId);
businessPackageCbo.setBusiness(businessCbo);
businessPackageCbo.setPackage(packageCbo);
businessPackageDao.save(businessPackageCbo);


//save phone
if( businessDetailDto.getBusinessPhone() != null )
{
PhoneTypeCbo phoneTypeCbo = new PhoneTypeCbo();
BusinessPhoneCbo bizPhoneCbo = new BusinessPhoneCbo();
PhoneCbo phoneCbo = new PhoneCbo();
Integer phoneTypeId = null;
List phoneTypeList = phoneTypeDao.findByPhoneTypeName("Business");
if(phoneTypeList != null)
{
Iterator itr = phoneTypeList.iterator();

while(itr.hasNext())
{
phoneTypeCbo = (PhoneTypeCbo) itr.next();
phoneTypeId = phoneTypeCbo.getPhoneTypeId();
}
}

//save phone
phoneCbo.setPhoneNumber(businessDetailDto.getBusinessPhone());
phoneCbo.setPhoneTypeId(phoneTypeId);
businessPhoneCbo = phoneDao.save(phoneCbo);

//save phone security
storeOrUpdateAnonymousPhone( businessPhoneCbo.getPhoneId(), businessDetailDto.getMakePhoneAnonymous() );

//save businessPhone
BusinessCbo bizCbo = new BusinessCbo();
bizCbo.setBusinessId(businessId);
bizPhoneCbo.setPhone(businessPhoneCbo);
bizPhoneCbo.setBusines(bizCbo);
phoneSet.add(bizPhoneCbo);

bizCbo.setBusinessPhones(phoneSet);
businessPhoneDao.save(bizPhoneCbo);

}

if( businessDetailDto.getFaxNumber() != null )
{
PhoneTypeCbo phoneTypeCbo = new PhoneTypeCbo();
BusinessPhoneCbo bizPhoneCbo = new BusinessPhoneCbo();
PhoneCbo phoneCbo = new PhoneCbo();
Integer phoneTypeId = null;
List phoneTypeList = phoneTypeDao.findByPhoneTypeName("Fax");
if(phoneTypeList != null)
{
Iterator itr = phoneTypeList.iterator();

while(itr.hasNext())
{
phoneTypeCbo = (PhoneTypeCbo) itr.next();
phoneTypeId = phoneTypeCbo.getPhoneTypeId();
}
}

phoneCbo.setPhoneNumber(businessDetailDto.getFaxNumber());
phoneCbo.setPhoneTypeId(phoneTypeId);
faxPhoneCbo = phoneDao.save(phoneCbo);

//save phone security
storeOrUpdateAnonymousPhone( faxPhoneCbo.getPhoneId(), businessDetailDto.getMakePhoneAnonymous() );

//save businessPhone
BusinessCbo bizCbo = new BusinessCbo();
bizCbo.setBusinessId(businessId);
bizPhoneCbo.setPhone(faxPhoneCbo);
bizPhoneCbo.setBusines(bizCbo);
phoneSet.add(bizPhoneCbo);

bizCbo.setBusinessPhones(phoneSet);
businessPhoneDao.save(bizPhoneCbo);


}

if( businessDetailDto.getMobilePhone() != null )
{
PhoneTypeCbo phoneTypeCbo = new PhoneTypeCbo();
BusinessPhoneCbo bizPhoneCbo = new BusinessPhoneCbo();
PhoneCbo phoneCbo = new PhoneCbo();
Integer phoneTypeId = null;
List phoneTypeList = phoneTypeDao.findByPhoneTypeName("Mobile");
if(phoneTypeList != null)
{
Iterator itr = phoneTypeList.iterator();

while(itr.hasNext())
{
phoneTypeCbo = (PhoneTypeCbo) itr.next();
phoneTypeId = phoneTypeCbo.getPhoneTypeId();
}
}

phoneCbo.setPhoneNumber(businessDetailDto.getMobilePhone());
phoneCbo.setPhoneTypeId(phoneTypeId);
mobilePhoneCbo = phoneDao.save(phoneCbo);

//save phone security
storeOrUpdateAnonymousPhone( mobilePhoneCbo.getPhoneId(), businessDetailDto.getMakePhoneAnonymous() );

//save businessPhone
BusinessCbo bizCbo = new BusinessCbo();
bizCbo.setBusinessId(businessId);
bizPhoneCbo.setPhone(mobilePhoneCbo);
bizPhoneCbo.setBusines(bizCbo);
phoneSet.add(bizPhoneCbo);

bizCbo.setBusinessPhones(phoneSet);
businessPhoneDao.save(bizPhoneCbo);


}

try {

//save biz details
Set bizAddresses = new HashSet();
BusinessAddressCbo businessAddressCbo = new BusinessAddressCbo();
addrTypeCbo = new AddressTypeCbo();

businessCbo.setBusinessId(businessId);
addrTypeCbo.setAddressTypeId(addrTypeId);

businessAddressCbo.setAddressType(addrTypeCbo);
businessAddressCbo.setAddress(savedAddressCbo);
businessAddressCbo.setBusines(businessCbo);
bizAddresses.add(businessAddressCbo);

businessCbo.setBusinessAddresses(bizAddresses);
savedAddressCbo.setBusinessAddresses(bizAddresses);
addrTypeCbo.setBusinessAddresses(bizAddresses);
businessAddressDao.save(businessAddressCbo);

businessCbo.setBusinessName( businessDetailDto.getBusinessName() );
businessCbo.setBusinessEmailAddress( businessDetailDto.getBusinessEmailAddress() );
//businessCbo.setBusinessAddresses(bizAddresses);
businessCbo.setBusinessId(businessId);
businessCbo.setBusinessTypeId( businessDetailDto.getBusinessTypeId() );
//businessCbo.setBusinessPhones(phoneSet);

placeOfBusinessCbo = placeOfBusinessDao.findById(new Integer(businessDetailDto.getPlaceOfBusiness()) );
//add the reference of businessCbo to the collection in placeOfBusinessCbo
Set businesses = placeOfBusinessCbo.getBusineses();
if (businesses == null)
{
businesses = new HashSet();

}

businesses.add(businessCbo);
placeOfBusinessCbo.setBusineses(businesses);
businessCbo.setPlaceOfBusiness(placeOfBusinessCbo);
businessDao.saveOrUpdate(businessCbo);

} catch (Exception e) {

System.out.println("Exception occured: " + e);

}

}
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hmm, can you re-post that using the CODE tags below, it will keep your indentation to make the code more readable.

Normally, I would edit the post for you, but what happens with xml and "<" ">" is that they change to ">"

Thanks

Mark
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ah,

<set name="businessPackages" inverse="true" cascade="all-delete-orphan">

There isn't a cascade option called "all-delete-orphan"

There is

"all, delete-orphan"



Mark
 
Nina Anderson
Ranch Hand
Posts: 148
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I got the "all-delete-orphan" option from Hiberate.org: http://www.hibernate.org/hib_docs/reference/en/html/example-parentchild.html

This code always used to work. The only thing I changed was:
1. From Windows Xp to Windows Vista
2. From IBM Websphere server to Tomcat server

So, I don't understand why the application can retrieve data and all of a sudden cannot Save data in the database.
 
Bougnon Kipre
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Nina Anderson:
I got the "all-delete-orphan" option from Hiberate.org: http://www.hibernate.org/hib_docs/reference/en/html/example-parentchild.html

This code always used to work. The only thing I changed was:
1. From Windows Xp to Windows Vista
2. From IBM Websphere server to Tomcat server

So, I don't understand why the application can retrieve data and all of a sudden cannot Save data in the database.


Although the syntax you are using is in correctly quited in the link you provided, if I were you I would just try Mark suggestion for that is also the syntax I am familiar with.
I am not saying your are wrong in using, I just want to eliminate this possibility.

Another suggestion to gradually change you environment.
Instead of changing both OS and Web server, I would do as follows:
1) first change OS: from XP to Vista.
2) if 1) works, then change Web server
 
Bougnon Kipre
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Bougnon Kipre:


Although the syntax you are using is in correctly quited in the link you provided, if I were you I would just try Mark suggestion for that is also the syntax I am familiar with.
I am not saying your are wrong in using the current syntax, I just want to eliminate this possibility.

Another suggestion to gradually change you environment.
Instead of changing both OS and Web server, I would do as follows:
1) First change OS: from XP to Vista.
2) if 1) works, then change Web server
 
Nina Anderson
Ranch Hand
Posts: 148
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I tried changing from cascade="all-delete-orphan" to "all, delete-orphan", but I'm still getting the same errors.

I bought a new laptop that's why I had the new installations, so going to a previous version is not an option for me.
 
Nina Anderson
Ranch Hand
Posts: 148
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Eureeka! Found the problem.

I had a MS SQL Trigger that invokes table updates whenever the java application performs an insert on my Business_Service table. My updates in the SQL Trigger were failing because the ID field of the table I was trying to do that update did not exist. As a result of this, I was getting the generated Key error.

To resolve this, I'm checking if the ID field of the result exists in the database before I do an update.

I'm at peace now... :roll:
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Nina Anderson:
Eureeka! Found the problem.

I had a MS SQL Trigger that invokes table updates whenever the java application performs an insert on my Business_Service table. My updates in the SQL Trigger were failing because the ID field of the table I was trying to do that update did not exist. As a result of this, I was getting the generated Key error.

To resolve this, I'm checking if the ID field of the result exists in the database before I do an update.

I'm at peace now... :roll:




I am really glad you found this piece to the puzzle. I doubt I would have ever guessed and figured that one out.

At least for now on, whenever anyone has a problem doing any crud, one of the first questions I will ask is "Do you have a trigger anywhere?"

Thanks for posting your solution when you found it.

Hope to see you around.

Mark
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic