yes, i have it working fine.
<insert id="insert" parameterClass="Category">
INSERT INTO CATEGORY
(
title,
description,
parentCategoryId,
leftNode,
rightNode,
sequence,
ancestorTree
)
VALUES(
#title#,
#description#,
#parentCategoryId#,
#leftNode#,
#rightNode#,
#sequence#,
#ancestorTree#
)
<selectKey keyProperty="categoryId" resultClass="integer">
SELECT LAST_INSERT_ID()
</selectKey>
</insert>
That is the exact functioning syntax.
Brandon
On 4/20/05, Siti Norhairatul Aishah <[EMAIL PROTECTED]> wrote:
> from my understanding, for myql you'd have to put the selectKey argument
> after the insert statement.
> 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.
> >
>
>
