Anyway,I did try all the suggestions but still it does not work and purging the same error as before.
Have anybody have ever had it working finely in MySQL. What version of mysql and ibatis are you guys using?
Anyway, thanks for all your help.
----- Original Message ----- From: "Tom Cassimon" <[EMAIL PROTECTED]>
To: <ibatis-user-java@incubator.apache.org>; "Siti Norhairatul Aishah" <[EMAIL PROTECTED]>
Sent: Wednesday, April 20, 2005 6:05 AM
Subject: Re: Problem with SQL Statement (preparation failed) during insert into MySQL
I've been using the selectkey statement myself today, and if i use my syntax on your problem i would look like this:
<insert id="addRole" parameterClass="com.ibt.dview.da.model.Role">
<selectKey resultClass="int" keyProperty="roleId" >
SELECT last_insert_id() AS roleId
</selectKey>
insert into da_role(ROLE_ID,ROLE_NAME,ROLE_DESC)
values (#roleId#,#roleName#, #roleDesc#)
</insert>Hopefully it helps.
Greetz,
Tom Cassimon
2005/4/18, Siti Norhairatul Aishah <[EMAIL PROTECTED]>:
Hi all,
I am having a problem when trying to insert into a MySQL database.I am
using Ibatis 2.0 on Spring framework. Here is the error I'm getting.
com.ibatis.common.jdbc.exception.NestedSQLException: --- The error occurred in /com/ibt/dview/da/model/Role.xml. --- The error occurred while executing update. --- Check the insert into da_role(ROLE_NAME,ROLE_DESC) values (?, ?) . --- Check the SQL Statement (preparation failed). --- Cause: java.lang.NullPointerException Caused by: java.lang.NullPointerException at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:89) at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDelegate.java:442) at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.java:85) at org.springframework.orm.ibatis.SqlMapClientTemplate$9.doInSqlMapClient(SqlMapClientTemplate.java:295) at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:165) at org.springframework.orm.ibatis.SqlMapClientTemplate.insert(SqlMapClientTemplate.java:293) at com.ibt.dview.da.dao.RoleDAOImpl.addRole(RoleDAOImpl.java:51) at com.ibt.dview.adaptor.db.DADBAdaptorImpl.addRole(DADBAdaptorImpl.java:94) at com.ibt.dview.da.service.DAServiceImpl.addRole(DAServiceImpl.java:188) at com.ibt.dview.da.bean.DARoleBean.addRole(DARoleBean.java:80) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:324) at com.sun.faces.el.MethodBindingImpl.invoke(MethodBindingImpl.java:126) at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:72) at javax.faces.component.UICommand.broadcast(UICommand.java:312) at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:267) at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:381) at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:75) at com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:200) at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:90) at javax.faces.webapp.FacesServlet.service(FacesServlet.java:197) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:284) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:204) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:257) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564) at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:245) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:199) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:195) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:164) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:149) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:156) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:564) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:972) at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:206) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:828) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:700) at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:584) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683) at java.lang.Thread.run(Thread.java:534) Caused by: java.lang.NullPointerException at com.mysql.jdbc.PreparedStatement.asSql(PreparedStatement.java:1845) at com.mysql.jdbc.PreparedStatement.toString(PreparedStatement.java:1770) at java.lang.String.valueOf(String.java:2131) at java.lang.StringBuffer.append(StringBuffer.java:370) at com.mysql.jdbc.trace.Tracer.printParameters(Tracer.aj:240) at com.mysql.jdbc.trace.Tracer.printEntering(Tracer.aj:167) at com.mysql.jdbc.trace.Tracer.entry(Tracer.aj:126) at com.mysql.jdbc.trace.Tracer.ajc$before$com_mysql_jdbc_trace_Tracer$1$f51c62b8(Tracer.aj:45) at com.mysql.jdbc.Connection.registerStatement(Connection.java) at com.mysql.jdbc.Statement.<init>(Statement.java:171) at com.mysql.jdbc.PreparedStatement.<init>(PreparedStatement.java:139) at com.mysql.jdbc.ServerPreparedStatement.toString(ServerPreparedStatement.java:851) at java.lang.String.valueOf(String.java:2131) at java.lang.StringBuffer.append(StringBuffer.java:370) at com.mysql.jdbc.trace.Tracer.printParameters(Tracer.aj:240) at com.mysql.jdbc.trace.Tracer.printEntering(Tracer.aj:167) at com.mysql.jdbc.trace.Tracer.entry(Tracer.aj:126) at com.mysql.jdbc.trace.Tracer.ajc$before$com_mysql_jdbc_trace_Tracer$1$f51c62b8(Tracer.aj:45) at com.mysql.jdbc.Connection.registerStatement(Connection.java) at com.mysql.jdbc.Statement.<init>(Statement.java:171) at com.mysql.jdbc.PreparedStatement.<init>(PreparedStatement.java:201) at com.mysql.jdbc.ServerPreparedStatement.<init>(ServerPreparedStatement.java:133) at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1370) at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1335) at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:216) at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:323) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:324) at com.ibatis.common.jdbc.logging.ConnectionLogProxy.invoke(ConnectionLogProxy.java:50) at $Proxy0.prepareStatement(Unknown Source) at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:77) at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate(GeneralStatement.java:195) at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:73) ... 46 more
My statement is as simple as follows
<resultMap id="role-result" class="com.ibt.dview.da.model.Role"> <result property="roleId" column="ROLE_ID" /> <result property="roleName" column="ROLE_NAME" /> <result property="roleDesc" column="ROLE_DESC" /> </resultMap>
<insert id="addRole" parameterClass="com.ibt.dview.da.model.Role"> insert into da_role(ROLE_NAME,ROLE_DESC) values (#roleName#, #roleDesc#) <selectKey resultClass="int" keyProperty="roleId" > SELECT last_insert_id() AS roleId </selectKey> </insert>
I've tried to look into this mailing list, and my code seems to look
fine.What am I doing wrong here.. Initially when I tried running the same
statement on oracle 8.. it was ok. Of course I had the selectKey argument to
suit oracle then. I changed my MySQL table from InnoDB to MyISAM but still
it does not work. I have also disabled the Transaction Manager in Spring to
let this work, but still it does not have any impact. It seems like the sql
statement supplied is incorrect but I doubt so.
Please if anyone can help me.. really appreciate it.
Thanks.
