[davidsos]

I have an application on ME 6.5 using Spring 2.0 and Hibernate 3.1 with MySQL 5.0.51b and their 5.1.6 JDC connector which returns an error when updates (using DAO merge) are made to a specific table that has a many to many relation. I created a simple JAVA console test program and reverse engineered the database to test the problem such as to eliminate all the Web, etc. baggage. I get the same result. Here’s the trace, I’d appreciate some much needed help.

Exception in thread “main” org.springframework.dao.InvalidDataAccessResourceUsageException: Could not execute JDBC batch update; nested exception is org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
Caused by: org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:202)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:140)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:297)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:985)
at org.springframework.orm.hibernate3.HibernateAccessor.flushIfNecessary(HibernateAccessor.java:390)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:373)
at org.springframework.orm.hibernate3.HibernateTemplate.merge(HibernateTemplate.java:762)
at tgs.alu.blacklist.dao.BlacklistSourceDAO.merge(BlacklistSourceDAO.java:171)
at tgs.alu.test1.Test1.doBlacklistSource(Test1.java:82)
at tgs.alu.test1.Test1.main(Test1.java:30)
Caused by: java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘Interval=10, Retries=2 where BlacklistSourceID=2’ at line 1
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1666)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1082)
at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:58)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:195)
… 11 more

[davidsos]

I wrote another test, eliminating spring and still have the same error. I used ME 6.5 with Hibernate 3.1 with MySQL 5.0.51b and their 5.1.6 JDC connector. I’ll copy the stack trace below and follow that with the test code. Remember the only table I’m having this problem appears to be the one with a many to many relation.

======================================
Save failed: Could not execute JDBC batch update
org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:202)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:140)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:297)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:985)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:333)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at tgs.alu.test2.Test2.doBlacklistSource(Test2.java:43)
at tgs.alu.test2.Test2.main(Test2.java:18)
Caused by: java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘Interval=10, Retries=2 where BlacklistSourceID=2’ at line 1
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1666)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1082)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:58)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:195)
… 9 more

=============================================
public static void doBlacklistSource()
{
BlacklistSource blacklistSource = null;
BlacklistSourceDAO blacklistSourceDAO = new BlacklistSourceDAO();
Integer id = new Integer(2);
blacklistSource = blacklistSourceDAO.findById(id);
if (blacklistSource == null)
{
System.out.println(“No records found for BlackSourceId = ” + id);
return;
}
String webSiteName = blacklistSource.getWebSiteName();
System.out.println(“Found WebSiteName: ” + webSiteName);
webSiteName = webSiteName + “s”;
blacklistSource.setWebSiteName(webSiteName);
Transaction tx = null;
try
{
Session session = blacklistSourceDAO.getSession();
tx = session.beginTransaction();
blacklistSourceDAO.merge(blacklistSource);
// blacklistSourceDAO.attachDirty(blacklistSource); <— Same problem too
tx.commit();
BlacklistSource blacklistSourceX = blacklistSourceDAO.findById(id);
webSiteName = blacklistSourceX.getWebSiteName();
System.out.println(“Updated WebSiteName: ” + webSiteName);
}
catch (HibernateException e) {
System.out.println(“Save failed: ” + e.getMessage());
e.printStackTrace();
tx.rollback();
}
finally
{
try
{
blacklistSourceDAO.getSession().close();
}
catch (Exception e)
{
System.out.println(“Close failed: ” + e.getMessage());
e.printStackTrace();
}
}

}

[Loyal Water]

Your using the Keyword interval in your update statement. Thats the reason why your getting this . You need to change the name of your column.

[davidsos]

@support-nipun wrote:

Your using the Keyword interval in your update statement. Thats the reason why your getting this . You need to change the name of your column.

Bingo, that’s it, I must be getting blind. Thank you. Now I’ll try it on my fullscale program.

[Loyal Water]

Your welcome.

[Author]

Posts