<sql-insert> binding parameters in wrong order.

wbrown338 · **Joined:** Fri Jan 26, 2007 10:37 am **Posts:** 6

Greetings:
I am using an <sql-insert> in my mappings for persisting and retrieving an object. Hibernate is binding the parameters in the wrong order (the email is being bound to the date field) and I am not sure how to fix this. Is it an issue with my configuration or an issue with the hibernate library? Any help would be greatly appreciated.

Hibernate version: = 3.2.1.ga

Mapping documents:
<class name="com.colorful.beans.Registration" table="registration">

<id name="email" column="email" type="java.lang.String" >
<generator class="assigned" />
</id>

<property name="password" type="java.lang.String" column="password" not-null="true"/>

<property name="regType" type="java.lang.String" column="regType" not-null="true" />

<property name="dateAdded" type="timestamp" column="dateAdded" />

<loader query-ref="registration"/>


<sql-insert>INSERT INTO registration (email,password,regType,dateAdded) VALUES (?,AES_ENCRYPT(?,'&key;'),?,?);</sql-insert>
<sql-update>UPDATE registration set email = ?, password = AES_ENCRYPT(?,'&key;'), regType = ?, dateAdded = ?;</sql-update>
</class>

Code between sessionFactory.openSession() and session.close():
Session session = Utilities.currentSession();

//save the registration to the database.
Transaction tx= session.beginTransaction();
session.saveOrUpdate(registration);
tx.commit();

Utilities.closeSession();

The generated SQL (show_sql=true):
Debug level Hibernate log excerpt:
Full stack trace of any exception that occurs:
eginning() (line:463)] - registering flush begin
26 Jan 2007 09:40:59,114 DEBUG [AbstractEntityPersister.insert() (line:2154)] - Inserting entity: [com.colorful.beans.Registration#email@yahoo.com]
26 Jan 2007 09:40:59,124 DEBUG [AbstractBatcher.logOpenPreparedStatement() (line:358)] - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
26 Jan 2007 09:40:59,124 DEBUG [AbstractBatcher.log() (line:393)] -
INSERT
INTO
registration
(email,password,regType,dateAdded)
VALUES
(?,AES_ENCRYPT(?,'secretKey'),?,?);
26 Jan 2007 09:40:59,124 DEBUG [AbstractBatcher.getPreparedStatement() (line:476)] - preparing statement
26 Jan 2007 09:40:59,124 DEBUG [AbstractEntityPersister.dehydrate() (line:1942)] - Dehydrating entity: [com.colorful.beans.Registration#email@yahoo.com]
26 Jan 2007 09:40:59,124 DEBUG [NullableType.nullSafeSet() (line:80)] - binding 'abc123' to parameter: 1
26 Jan 2007 09:40:59,124 DEBUG [NullableType.nullSafeSet() (line:80)] - binding 'listener' to parameter: 2
26 Jan 2007 09:40:59,124 DEBUG [NullableType.nullSafeSet() (line:80)] - binding '2007-01-26 09:40:59' to parameter: 3
26 Jan 2007 09:40:59,124 DEBUG [NullableType.nullSafeSet() (line:80)] - binding 'email@yahoo.com' to parameter: 4
26 Jan 2007 09:40:59,124 DEBUG [BatchingBatcher.doExecuteBatch() (line:44)] - Executing batch size: 1
26 Jan 2007 09:40:59,124 DEBUG [AbstractBatcher.logClosePreparedStatement() (line:366)] - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
26 Jan 2007 09:40:59,134 DEBUG [AbstractBatcher.closePreparedStatement() (line:525)] - closing statement
26 Jan 2007 09:40:59,134 DEBUG [JDBCExceptionReporter.logExceptions() (line:69)] - Could not execute JDBC batch update [INSERT INTO registration (email,password,regType,dateAdded) VALUES (?,AES_ENCRYPT(?,'secretKey'),?,?);]
java.sql.BatchUpdateException: Data truncation: Incorrect datetime value: 'email@yahoo.com' for column 'dateAdded' at row 1
at com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedStatement.java:647)
at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:294)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:242)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at com.colorful.dao.RegistrationDAO.persistRegistration(RegistrationDAO.java:33)
at com.colorful.servlet.RegisterServlet.doPost(RegisterServlet.java:50)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.colorful.filters.LoginUserFilter.doFilter(LoginUserFilter.java:49)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.colorful.filters.RegisterValidateFilter.doFilter(RegisterValidateFilter.java:108)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:462)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at com.gocsc.valve.CSCErrorReportValve.invoke(CSCErrorReportValve.java:30)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at com.gocsc.valve.CSCAccessLogValve5028.invoke(CSCAccessLogValve5028.java:473)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:595)
26 Jan 2007 09:40:59,134 WARN [JDBCExceptionReporter.logExceptions() (line:77)] - SQL Error: 0, SQLState: 01004
26 Jan 2007 09:40:59,134 ERROR [JDBCExceptionReporter.logExceptions() (line:78)] - Data truncation: Incorrect datetime value: 'email@yahoo.com' for column 'dateAdded' at row 1
26 Jan 2007 09:40:59,134 ERROR [AbstractFlushingEventListener.performExecutions() (line:301)] - Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:249)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at com.colorful.dao.RegistrationDAO.persistRegistration(RegistrationDAO.java:33)
at com.colorful.servlet.RegisterServlet.doPost(RegisterServlet.java:50)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.colorful.filters.LoginUserFilter.doFilter(LoginUserFilter.java:49)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.colorful.filters.RegisterValidateFilter.doFilter(RegisterValidateFilter.java:108)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:462)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at com.gocsc.valve.CSCErrorReportValve.invoke(CSCErrorReportValve.java:30)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at com.gocsc.valve.CSCAccessLogValve5028.invoke(CSCAccessLogValve5028.java:473)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.BatchUpdateException: Data truncation: Incorrect datetime value: 'email@yahoo.com' for column 'dateAdded' at row 1
at com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedStatement.java:647)
at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:294)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:242)
... 45 more
26 Jan 2007 09:40:59,134 DEBUG [ConnectionManager.flushEnding() (line:472)] - registering flush end
26 Jan 2007 09:40:59,134 ERROR [RegistrationDAO.persistRegistration() (line:38)] - error persisting registration to the DB.
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:249)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at com.colorful.dao.RegistrationDAO.persistRegistration(RegistrationDAO.java:33)
at com.colorful.servlet.RegisterServlet.doPost(RegisterServlet.java:50)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.colorful.filters.LoginUserFilter.doFilter(LoginUserFilter.java:49)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.colorful.filters.RegisterValidateFilter.doFilter(RegisterValidateFilter.java:108)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:462)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at com.gocsc.valve.CSCErrorReportValve.invoke(CSCErrorReportValve.java:30)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at com.gocsc.valve.CSCAccessLogValve5028.invoke(CSCAccessLogValve5028.java:473)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.BatchUpdateException: Data truncation: Incorrect datetime value: 'email@yahoo.com' for column 'dateAdded' at row 1
at com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedStatement.java:647)
at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:294)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:242)
... 45 more

Name and version of the database you are using:
mysql 5

Thanks.
Bill.

nathanmoon · **Posted:** Fri Jan 26, 2007 12:26 pm

You need to make sure you order your columns correctly. Here's an excerpt from the docs (http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#querysql-cud):

Quote:

The order of the positional parameters are currently vital, as they must be in the same sequence as Hibernate expects them.

You can see the expected order by enabling debug logging for the org.hibernate.persister.entity level. With this level enabled Hibernate will print out the static SQL that is used to create, update, delete etc. entities. (To see the expected sequence, remember to not include your custom SQL in the mapping files as that will override the Hibernate generated static sql.)

wbrown338 · **Joined:** Fri Jan 26, 2007 10:37 am **Posts:** 6

Greetings nathanmoon:

reordering my query parameters in my sql-insert statment resolved the issue. I did see the documentation previously but ignored the "(To see the expected sequence, remember to not include your custom SQL in the mapping files as that will override the Hibernate generated static sql.)". Doing that I was able to see the order hibernate expects.

Thanks for your help
Bill.