You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Javier Leyba <xl...@gmail.com> on 2006/10/20 10:44:01 UTC
MySQL query problem
Hi
I've the following code defined:
----------------
<select id="insertTopic" parameterClass="topic"
resultClass="genericResult">
<![CDATA[
INSERT INTO topic (
topic.topic_id,
topic.topic_description
) VALUES (#id#, #description#);
SELECT ROW_COUNT() AS result;
]]>
</select>
----------------------
This insert was working in a stored procedure without problems. Now I
extracted from stored procedure and inserted as an iBatis select but
when I run it I got the error:
---------------------
2006-10-20 10:37:34,331 :DEBUG
:[http-0.0.0.0-8088-1]:taCommonsLoggingImpl: {conn-100000} Connection
2006-10-20 10:37:34,367 :DEBUG
:[http-0.0.0.0-8088-1]:taCommonsLoggingImpl: {conn-100000} Preparing
Statement: INSERT INTO topic ( topic.topic_id,
topic.topic_description ) VALUES (?, ?); SELECT
ROW_COUNT() AS result
2006-10-20 10:37:34,494 :DEBUG
:[http-0.0.0.0-8088-1]:taCommonsLoggingImpl: {pstm-100001} Executing
Statement: INSERT INTO topic ( topic.topic_id,
topic.topic_description ) VALUES (?, ?); SELECT
ROW_COUNT() AS result
2006-10-20 10:37:34,494 :DEBUG
:[http-0.0.0.0-8088-1]:taCommonsLoggingImpl: {pstm-100001} Parameters:
[mastres, Otro topic mas]
2006-10-20 10:37:34,496 :DEBUG
:[http-0.0.0.0-8088-1]:taCommonsLoggingImpl: {pstm-100001} Types:
[java.lang.String, java.lang.String]
2006-10-20 10:37:34,545 :ERROR
:[http-0.0.0.0-8088-1]:sPortSoapBindingImpl: insertTopic(TopicBean)
Exception en first try
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in Topic.xml.
--- The error occurred while applying a parameter map.
--- Check the insertTopic-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLException: 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 '; SELECT ROW_COUNT() AS
result' at line 1
Caused by: java.sql.SQLException: 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 '; SELECT ROW_COUNT() AS
result' at line 1
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:185)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:104)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:565)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:540)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:106)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientImpl.java:84)
at com.bs.proteo.notifications.service.NotificationsPortSoapBindingImpl.insertTopic(NotificationsPortSoapBindingImpl.java:972)
at jrockit.reflect.VirtualNativeMethodInvoker.invoke(Ljava.lang.Object;[Ljava.lang.Object;)Ljava.lang.Object;(Unknown
Source)
at java.lang.reflect.Method.invoke(Ljava.lang.Object;[Ljava.lang.Object;I)Ljava.lang.Object;(Unknown
Source)
at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397)
at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186)
at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323)
at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)
at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)
at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)
at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:453)
at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281)
at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:81)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrincipalValve.java:39)
Caused by: java.sql.SQLException: 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 '; SELECT ROW_COUNT() AS
result' at line 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2921)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2978)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:930)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:770)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:183)
at jrockit.reflect.VirtualNativeMethodInvoker.invoke(Ljava.lang.Object;[Ljava.lang.Object;)Ljava.lang.Object;(Unknown
Source)
------------------
Log show me that parameters arrives to query without problems. If I
put those querys in a MySQL it works ok, so..... I' ve no idea about
the origin of the error and how to fix it.
Could somebody give me a clue ?
Thanks in advance
J
--
Javier Leyba
Barcelona - Spain
http://blog.leyba.com.ar
Re: MySQL query problem
Posted by Javier Leyba <xl...@gmail.com>.
On 10/20/06, Larry Meadors <lm...@apache.org> wrote:
> Let me get this straight...you are doing an insert, and then selecting
> row count to see if it's one to make sure it works?
>
> That's, uhm, silly.
>
> If your driver doesn't throw an exception, it's pretty safe to assume
> that your single row insert successfully inserted one row.
>
> If not, do EVERYTHING IN YOUR POWER to switch databases, because that
> sort of failure is inexcusable from a database vendor.
>
Well, is a good point. :)
May be it was a paranoic practice I used in PHP... :) but what you say
sound logic.
I´ll change my code.
Nevertheless, I think there could exists situation where you'll need
to know rows affected by an insert to use the number for post related
process and is sad to see it didn´t work.
Thanks
J
J
Re: MySQL query problem
Posted by Jeff Butler <je...@gmail.com>.
I agree with Larry - this is silly.
However, it should work. I think the issue is all the session stuff.
openSession starts a different session - so you're doing transactional work
in one session, and executing queries in another session. Remove the the
call to openSession, then just do the transactional calls on the sqlMap
itself. There is hardly ever a reason to do openSession (which starts a
DIFFERENT session than the one used by the other SqlMapClient calls).
Also - you don't need the CDATA tags. It's not causing the problem, but
it's a bad habit to get into.
Jeff Butler
On 10/20/06, Larry Meadors <lm...@apache.org> wrote:
>
> Let me get this straight...you are doing an insert, and then selecting
> row count to see if it's one to make sure it works?
>
> That's, uhm, silly.
>
> If your driver doesn't throw an exception, it's pretty safe to assume
> that your single row insert successfully inserted one row.
>
> If not, do EVERYTHING IN YOUR POWER to switch databases, because that
> sort of failure is inexcusable from a database vendor.
>
> Larry
>
>
> On 10/20/06, Javier Leyba <xl...@gmail.com> wrote:
> > On 10/20/06, jamesst <s....@logicacmg.com> wrote:
> > > make sure you have an endTransaction(); That is what really does the
> > > commit this should be done in the finally clause unless you have auto
> > > commit true..
> > >
> >
> >
> > Well, I've it:
> >
> > -------------------------
> > try {
> > sqlMap =
> NotificationsSqlConfig.getSqlMapInstance();
> > session = sqlMap.openSession();
> > session.startTransaction();
> >
> >
> > sqlMap.insert("insertTopic", topicBean);
> >
> > gr = (GenericResult) sqlMap.queryForObject
> ("operationResult");
> >
> > session.commitTransaction();
> >
> > } catch (Exception e) {
> > logger.error("insertTopic(TopicBean) " +
> > "Exception en first try ", e);
> //$NON-NLS-1$
> > e.printStackTrace();
> > throw new RemoteException(e.getMessage(), e);
> > } finally {
> > try {
> > session.endTransaction();
> > } catch (SQLException e) {
> > logger.warn("updateTopic(String, String,
> " +
> > "String) - exception
> finally", e); //$NON-NLS-1$
> > } finally {
> > session.close();
> > }
> > }
> >
> > -------------------
> >
> >
> >
> > IBatis configured as:
> >
> > --------------
> > <transactionManager type="JDBC" commitRequired="true">
> > <dataSource type="JNDI">
> > <property name="
> context.java.naming.factory.initial"
> >
> > value="org.jnp.interfaces.NamingContextFactory" />
> > <property name="context.java.naming.provider.url
> "
> > value="jnp://localhost:2199" />
> > <property name="
> context.java.naming.factory.url.pkgs"
> > value="
> org.jboss.naming:org.jnp.interfaces" />
> > <property name="DataSource"
> > value="java:comp/env/jdbc/MySQLDB" />
> > <property name="JDBC.DefaultAutoCommit"
> value="false" />
> > </dataSource>
> > </transactionManager>
> > -------------
> >
> >
> >
> > And my query is:
> >
> > ----------------
> >
> > <insert id="insertTopic" parameterClass="topic">
> > <![CDATA[
> > INSERT INTO topic (
> > topic.topic_id,
> > topic.topic_description
> > ) VALUES (#id#, #description#);
> > ]]>
> > </insert>
> > -----------
> >
> > and
> >
> > ----------
> > <select id="operationResult" resultClass="genericResult">
> > <![CDATA[
> > SELECT ROW_COUNT() AS result;
> > ]]>
> > </select>
> > ---------
> >
> >
> > Data is correctly inserted, but result is -1... it´s driving me nuts
> !!!
> >
> > J
> >
>
Re: MySQL query problem
Posted by Larry Meadors <lm...@apache.org>.
Let me get this straight...you are doing an insert, and then selecting
row count to see if it's one to make sure it works?
That's, uhm, silly.
If your driver doesn't throw an exception, it's pretty safe to assume
that your single row insert successfully inserted one row.
If not, do EVERYTHING IN YOUR POWER to switch databases, because that
sort of failure is inexcusable from a database vendor.
Larry
On 10/20/06, Javier Leyba <xl...@gmail.com> wrote:
> On 10/20/06, jamesst <s....@logicacmg.com> wrote:
> > make sure you have an endTransaction(); That is what really does the
> > commit this should be done in the finally clause unless you have auto
> > commit true..
> >
>
>
> Well, I've it:
>
> -------------------------
> try {
> sqlMap = NotificationsSqlConfig.getSqlMapInstance();
> session = sqlMap.openSession();
> session.startTransaction();
>
>
> sqlMap.insert("insertTopic", topicBean);
>
> gr = (GenericResult) sqlMap.queryForObject("operationResult");
>
> session.commitTransaction();
>
> } catch (Exception e) {
> logger.error("insertTopic(TopicBean) " +
> "Exception en first try ", e); //$NON-NLS-1$
> e.printStackTrace();
> throw new RemoteException(e.getMessage(), e);
> } finally {
> try {
> session.endTransaction();
> } catch (SQLException e) {
> logger.warn("updateTopic(String, String, " +
> "String) - exception finally", e); //$NON-NLS-1$
> } finally {
> session.close();
> }
> }
>
> -------------------
>
>
>
> IBatis configured as:
>
> --------------
> <transactionManager type="JDBC" commitRequired="true">
> <dataSource type="JNDI">
> <property name="context.java.naming.factory.initial"
>
> value="org.jnp.interfaces.NamingContextFactory" />
> <property name="context.java.naming.provider.url"
> value="jnp://localhost:2199" />
> <property name="context.java.naming.factory.url.pkgs"
> value="org.jboss.naming:org.jnp.interfaces" />
> <property name="DataSource"
> value="java:comp/env/jdbc/MySQLDB" />
> <property name="JDBC.DefaultAutoCommit" value="false" />
> </dataSource>
> </transactionManager>
> -------------
>
>
>
> And my query is:
>
> ----------------
>
> <insert id="insertTopic" parameterClass="topic">
> <![CDATA[
> INSERT INTO topic (
> topic.topic_id,
> topic.topic_description
> ) VALUES (#id#, #description#);
> ]]>
> </insert>
> -----------
>
> and
>
> ----------
> <select id="operationResult" resultClass="genericResult">
> <![CDATA[
> SELECT ROW_COUNT() AS result;
> ]]>
> </select>
> ---------
>
>
> Data is correctly inserted, but result is -1... it´s driving me nuts !!!
>
> J
>
Re: MySQL query problem
Posted by Javier Leyba <xl...@gmail.com>.
On 10/20/06, jamesst <s....@logicacmg.com> wrote:
> make sure you have an endTransaction(); That is what really does the
> commit this should be done in the finally clause unless you have auto
> commit true..
>
Well, I've it:
-------------------------
try {
sqlMap = NotificationsSqlConfig.getSqlMapInstance();
session = sqlMap.openSession();
session.startTransaction();
sqlMap.insert("insertTopic", topicBean);
gr = (GenericResult) sqlMap.queryForObject("operationResult");
session.commitTransaction();
} catch (Exception e) {
logger.error("insertTopic(TopicBean) " +
"Exception en first try ", e); //$NON-NLS-1$
e.printStackTrace();
throw new RemoteException(e.getMessage(), e);
} finally {
try {
session.endTransaction();
} catch (SQLException e) {
logger.warn("updateTopic(String, String, " +
"String) - exception finally", e); //$NON-NLS-1$
} finally {
session.close();
}
}
-------------------
IBatis configured as:
--------------
<transactionManager type="JDBC" commitRequired="true">
<dataSource type="JNDI">
<property name="context.java.naming.factory.initial"
value="org.jnp.interfaces.NamingContextFactory" />
<property name="context.java.naming.provider.url"
value="jnp://localhost:2199" />
<property name="context.java.naming.factory.url.pkgs"
value="org.jboss.naming:org.jnp.interfaces" />
<property name="DataSource"
value="java:comp/env/jdbc/MySQLDB" />
<property name="JDBC.DefaultAutoCommit" value="false" />
</dataSource>
</transactionManager>
-------------
And my query is:
----------------
<insert id="insertTopic" parameterClass="topic">
<![CDATA[
INSERT INTO topic (
topic.topic_id,
topic.topic_description
) VALUES (#id#, #description#);
]]>
</insert>
-----------
and
----------
<select id="operationResult" resultClass="genericResult">
<![CDATA[
SELECT ROW_COUNT() AS result;
]]>
</select>
---------
Data is correctly inserted, but result is -1... it´s driving me nuts !!!
J
Re: MySQL query problem
Posted by jamesst <s....@logicacmg.com>.
make sure you have an endTransaction(); That is what really does the
commit this should be done in the finally clause unless you have auto
commit true..
Javier Leyba wrote:
> I guess my problem could be originated in the commit.
>
> I mean I'm doing an insert, then I call a rowcount and then the
> transcation is commited.
>
> --------------
> sqlMap = NotificationsSqlConfig.getSqlMapInstance();
> session = sqlMap.openSession();
> session.startTransaction();
>
> sqlMap.insert("insertTopic", topicBean);
>
> gr = (GenericResult) sqlMap.queryForObject("operationResult");
>
> session.commitTransaction();
> ------------
>
>
> If this is the case, how could I solve it ?
>
> Thanks in advance
>
> J
>
>
> On 10/20/06, Javier Leyba <xl...@gmail.com> wrote:
>> On 10/20/06, jamesst <s....@logicacmg.com> wrote:
>> > When i tried executing more than one statement with MySQL in a
>> <insert>
>> > or <select> tag it has never worked but i am using 1.4 so things may
>> > have changed.
>> > so try removing SELECT ROW_COUNT() AS result. Maybe use a SelectKey
>> may
>> > work.
>> >
>> > <selectKey resultClass="int" >
>> > select ROW_COUNT() ;
>> > </selectKey>
>> >
>>
>> Thanks for all replies.
>>
>> I did recommended changes but now I receive a -1 as row_count....
>>
>> I wonder how you all test a result of an insert to be sure it worked...
>>
>> Thanks in advance
>>
>> J
>>
>
>
This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.
Re: MySQL query problem
Posted by Javier Leyba <xl...@gmail.com>.
I guess my problem could be originated in the commit.
I mean I'm doing an insert, then I call a rowcount and then the
transcation is commited.
--------------
sqlMap = NotificationsSqlConfig.getSqlMapInstance();
session = sqlMap.openSession();
session.startTransaction();
sqlMap.insert("insertTopic", topicBean);
gr = (GenericResult) sqlMap.queryForObject("operationResult");
session.commitTransaction();
------------
If this is the case, how could I solve it ?
Thanks in advance
J
On 10/20/06, Javier Leyba <xl...@gmail.com> wrote:
> On 10/20/06, jamesst <s....@logicacmg.com> wrote:
> > When i tried executing more than one statement with MySQL in a <insert>
> > or <select> tag it has never worked but i am using 1.4 so things may
> > have changed.
> > so try removing SELECT ROW_COUNT() AS result. Maybe use a SelectKey may
> > work.
> >
> > <selectKey resultClass="int" >
> > select ROW_COUNT() ;
> > </selectKey>
> >
>
> Thanks for all replies.
>
> I did recommended changes but now I receive a -1 as row_count....
>
> I wonder how you all test a result of an insert to be sure it worked...
>
> Thanks in advance
>
> J
>
--
Javier Leyba
Barcelona - Spain
http://blog.leyba.com.ar
Re: MySQL query problem
Posted by Javier Leyba <xl...@gmail.com>.
On 10/20/06, jamesst <s....@logicacmg.com> wrote:
> When i tried executing more than one statement with MySQL in a <insert>
> or <select> tag it has never worked but i am using 1.4 so things may
> have changed.
> so try removing SELECT ROW_COUNT() AS result. Maybe use a SelectKey may
> work.
>
> <selectKey resultClass="int" >
> select ROW_COUNT() ;
> </selectKey>
>
Thanks for all replies.
I did recommended changes but now I receive a -1 as row_count....
I wonder how you all test a result of an insert to be sure it worked...
Thanks in advance
J
Re: MySQL query problem
Posted by jamesst <s....@logicacmg.com>.
When i tried executing more than one statement with MySQL in a <insert>
or <select> tag it has never worked but i am using 1.4 so things may
have changed.
so try removing SELECT ROW_COUNT() AS result. Maybe use a SelectKey may
work.
<selectKey resultClass="int" >
select ROW_COUNT() ;
</selectKey>
No idea if it will work but worth a shot... Works for select
LAST_INSERT_ID()..
Graeme J Sweeney wrote:
> On Fri, 20 Oct 2006, Javier Leyba wrote:
>
>> I've the following code defined:
>>
>> ----------------
>> <select id="insertTopic" parameterClass="topic"
>> resultClass="genericResult">
>> <![CDATA[
>> INSERT INTO topic (
>> topic.topic_id,
>> topic.topic_description
>> ) VALUES (#id#, #description#);
>> SELECT ROW_COUNT() AS result;
>> ]]>
>> </select>
>> ----------------------
>
> <snip/>
>
>> Log show me that parameters arrives to query without problems. If I
>> put those querys in a MySQL it works ok, so..... I' ve no idea about
>> the origin of the error and how to fix it.
>>
>> Could somebody give me a clue ?
>
> Create separate <insert/> and <select/> statements and run them as a
> transaction?
>
This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.
Re: MySQL query problem
Posted by Graeme J Sweeney <ib...@gjsweeney.com>.
On Fri, 20 Oct 2006, Javier Leyba wrote:
> I've the following code defined:
>
> ----------------
> <select id="insertTopic" parameterClass="topic"
> resultClass="genericResult">
> <![CDATA[
> INSERT INTO topic (
> topic.topic_id,
> topic.topic_description
> ) VALUES (#id#, #description#);
> SELECT ROW_COUNT() AS result;
> ]]>
> </select>
> ----------------------
<snip/>
> Log show me that parameters arrives to query without problems. If I
> put those querys in a MySQL it works ok, so..... I' ve no idea about
> the origin of the error and how to fix it.
>
> Could somebody give me a clue ?
Create separate <insert/> and <select/> statements and run them as
a transaction?
--
Graeme -