ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brandon Goodin <brandon.goo...@gmail.com>
Subject Re: Problem with SQL Statement (preparation failed) during insert into MySQL
Date Tue, 19 Apr 2005 22:09:06 GMT
Tom:

you would need to place the selectKey AFTER your insert. This would
give you the appropriate insert id. The order that you are placing it
would provide you with the id from the previous insert.

Siti:

I would recommend turning logging on and see what you can discover.
The other thing to do is to debug in your ide. If we can see the sql
and parameters that are passed to the prepared statement we can likely
give you better advice.

Brandon



On 4/19/05, Tom Cassimon <tom.cassimon@gmail.com> wrote:
> 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 <siti@ibtech.com.my>:
> >
> > 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.
> >
>

Mime
View raw message