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 StevenF <st...@web2.filipowicz.com> on 2010/01/22 16:19:53 UTC
insert going wrong after going from Sybase to MySQL
Hi All,
I'm switching the database of an application from Sybase to MySQL, but the
insert statements aren't going well.
I have this which works for Sybase:
--------
<statement id = "insertChesser"
parameterClass="nl.chessone40.domain.Chesser" resultClass="java.lang.Long">
INSERT INTO Chesser
(login,
password,
firstName,
lastName,
credits)
values
(#login#,
#password#,
#firstName#,
#lastName#,
#credits#)
SELECT @@IDENTITY AS value
</statement>
----------
The above doesn't work for MySQL, what would I need to change to make it
work for MySQL?
Any help is very welcome!
Thanks!
--
View this message in context: http://old.nabble.com/insert-going-wrong-after-going-from-Sybase-to-MySQL-tp27274795p27274795.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: insert going wrong after going from Sybase to MySQL
Posted by Clinton Begin <cl...@gmail.com>.
Keep the selectKey after the statement for MySQL and other auto-increment
style key generators. Only sequences like those supported by Oracle should
come before the statement.
Also, a full stack trace might help.
Clinton
On Sat, Jan 23, 2010 at 3:20 PM, StevenF <st...@web2.filipowicz.com> wrote:
>
> Hi Guys,
>
> Still no luck here.
>
> I have this, but doesn't work:
> ----------------------------
> <statement id = "insertChesser"
> parameterClass="nl.chessone40.domain.Chesser" >
> <selectKey resultClass="int" keyProperty="chesserId">
> select last_insert_id() as value
> </selectKey>
> INSERT INTO Chesser
> (login,
> password,
> firstName,
> lastName,
> credits)
> values
> (#login#,
> #password#,
> #firstName#,
> #lastName#,
> #credits#)
> </statement>
> ----------------------------
>
> Also tried:
> ----------------------------
> <insert id = "insertChesser"
> parameterClass="nl.chessone40.domain.Chesser" >
> <selectKey resultClass="int" keyProperty="chesserId">
> select last_insert_id() as value
> </selectKey>
> INSERT INTO Chesser
> (login,
> password,
> firstName,
> lastName,
> credits)
> values
> (#login#,
> #password#,
> #firstName#,
> #lastName#,
> #credits#)
> </insert >
> ---------------------------
>
> This gives an error:
> SqlMapClient operation; uncategorized SQLException for SQL []; SQL state
> [null]; error code [0]; Insert statements cannot be executed as a query.;
> nested exception is java.sql.SQLException: Insert statements cannot be
> executed as a query.
>
> What is the difference between <statement id="...> and <insert id="...> ?
>
> Thanks for the help!!
>
>
> Niels Beekman-2 wrote:
> >
> > Your XML is simply invalid, use an editor that supports DTD validation.
> > The selectKey element can only be used inside select elements.
> >
> > I also think that resultClass="java.lang.Long" is obsolete, and should
> > be removed.
> >
> > Niels
> >
> >
> >
>
> --
> View this message in context:
> http://old.nabble.com/insert-going-wrong-after-going-from-Sybase-to-MySQL-tp27274795p27290482.html
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
Re: insert going wrong after going from Sybase to MySQL
Posted by Jeff Butler <je...@gmail.com>.
Use the <insert... form.
Then do this in Java:
Integer id = sqlMap.insert("insertChesser", parameter);
<statement> is a holdover from iBATIS 1.x I believe.
Jeff Butler
On Sat, Jan 23, 2010 at 4:20 PM, StevenF <st...@web2.filipowicz.com> wrote:
>
> Hi Guys,
>
> Still no luck here.
>
> I have this, but doesn't work:
> ----------------------------
> <statement id = "insertChesser"
> parameterClass="nl.chessone40.domain.Chesser" >
> <selectKey resultClass="int" keyProperty="chesserId">
> select last_insert_id() as value
> </selectKey>
> INSERT INTO Chesser
> (login,
> password,
> firstName,
> lastName,
> credits)
> values
> (#login#,
> #password#,
> #firstName#,
> #lastName#,
> #credits#)
> </statement>
> ----------------------------
>
> Also tried:
> ----------------------------
> <insert id = "insertChesser"
> parameterClass="nl.chessone40.domain.Chesser" >
> <selectKey resultClass="int" keyProperty="chesserId">
> select last_insert_id() as value
> </selectKey>
> INSERT INTO Chesser
> (login,
> password,
> firstName,
> lastName,
> credits)
> values
> (#login#,
> #password#,
> #firstName#,
> #lastName#,
> #credits#)
> </insert >
> ---------------------------
>
> This gives an error:
> SqlMapClient operation; uncategorized SQLException for SQL []; SQL state
> [null]; error code [0]; Insert statements cannot be executed as a query.;
> nested exception is java.sql.SQLException: Insert statements cannot be
> executed as a query.
>
> What is the difference between <statement id="...> and <insert id="...> ?
>
> Thanks for the help!!
>
>
> Niels Beekman-2 wrote:
>>
>> Your XML is simply invalid, use an editor that supports DTD validation.
>> The selectKey element can only be used inside select elements.
>>
>> I also think that resultClass="java.lang.Long" is obsolete, and should
>> be removed.
>>
>> Niels
>>
>>
>>
>
> --
> View this message in context: http://old.nabble.com/insert-going-wrong-after-going-from-Sybase-to-MySQL-tp27274795p27290482.html
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
RE: insert going wrong after going from Sybase to MySQL
Posted by StevenF <st...@web2.filipowicz.com>.
Hi Guys,
Still no luck here.
I have this, but doesn't work:
----------------------------
<statement id = "insertChesser"
parameterClass="nl.chessone40.domain.Chesser" >
<selectKey resultClass="int" keyProperty="chesserId">
select last_insert_id() as value
</selectKey>
INSERT INTO Chesser
(login,
password,
firstName,
lastName,
credits)
values
(#login#,
#password#,
#firstName#,
#lastName#,
#credits#)
</statement>
----------------------------
Also tried:
----------------------------
<insert id = "insertChesser"
parameterClass="nl.chessone40.domain.Chesser" >
<selectKey resultClass="int" keyProperty="chesserId">
select last_insert_id() as value
</selectKey>
INSERT INTO Chesser
(login,
password,
firstName,
lastName,
credits)
values
(#login#,
#password#,
#firstName#,
#lastName#,
#credits#)
</insert >
---------------------------
This gives an error:
SqlMapClient operation; uncategorized SQLException for SQL []; SQL state
[null]; error code [0]; Insert statements cannot be executed as a query.;
nested exception is java.sql.SQLException: Insert statements cannot be
executed as a query.
What is the difference between <statement id="...> and <insert id="...> ?
Thanks for the help!!
Niels Beekman-2 wrote:
>
> Your XML is simply invalid, use an editor that supports DTD validation.
> The selectKey element can only be used inside select elements.
>
> I also think that resultClass="java.lang.Long" is obsolete, and should
> be removed.
>
> Niels
>
>
>
--
View this message in context: http://old.nabble.com/insert-going-wrong-after-going-from-Sybase-to-MySQL-tp27274795p27290482.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
RE: insert going wrong after going from Sybase to MySQL
Posted by Niels Beekman <n....@wis.nl>.
Your XML is simply invalid, use an editor that supports DTD validation.
The selectKey element can only be used inside select elements.
I also think that resultClass="java.lang.Long" is obsolete, and should
be removed.
Niels
-----Original Message-----
From: StevenF [mailto:steven@web2.filipowicz.com]
Sent: Friday, January 22, 2010 11:32 PM
To: user-java@ibatis.apache.org
Subject: Re: insert going wrong after going from Sybase to MySQL
Hi Thanks for the reply!
Ok so I did this:
<statement id = "insertChesser"
parameterClass="nl.chessone40.domain.Chesser"
resultClass="java.lang.Long">
INSERT INTO Chesser
(login,
password,
firstName,
lastName,
credits)
values
(#login#,
#password#,
#firstName#,
#lastName#,
#credits#)
<selectKey resultClass="int" keyProperty="value">
select last_insert_id() as value
</selectKey>
</statement>
But this doesn't work. The project doesn't even start in Tomcat/Eclipse.
In eclipse it gives this error msg: "The content of element type must
match
(include|dynamic|and a whole lot more...)"
I'm no expert at Ibatis, but when I normally would do an insert, I would
use
<insert id="xxxx" parameterClass...etc> I don't really understand why
<statement id="xxxx" parameterClass...etc> is used.
I'm using Ibatis version 2.3.4
Any ideas what could be wrong here? Thanks a million!
CORRECTION
select last_insert_id()
Damn these fat fingers
--- On Fri, 2
--
View this message in context:
http://old.nabble.com/insert-going-wrong-after-going-from-Sybase-to-MySQ
L-tp27274795p27280982.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: insert going wrong after going from Sybase to MySQL
Posted by StevenF <st...@web2.filipowicz.com>.
Hi Thanks for the reply!
Ok so I did this:
<statement id = "insertChesser"
parameterClass="nl.chessone40.domain.Chesser" resultClass="java.lang.Long">
INSERT INTO Chesser
(login,
password,
firstName,
lastName,
credits)
values
(#login#,
#password#,
#firstName#,
#lastName#,
#credits#)
<selectKey resultClass="int" keyProperty="value">
select last_insert_id() as value
</selectKey>
</statement>
But this doesn't work. The project doesn't even start in Tomcat/Eclipse.
In eclipse it gives this error msg: "The content of element type must match
(include|dynamic|and a whole lot more...)"
I'm no expert at Ibatis, but when I normally would do an insert, I would use
<insert id="xxxx" parameterClass...etc> I don't really understand why
<statement id="xxxx" parameterClass...etc> is used.
I'm using Ibatis version 2.3.4
Any ideas what could be wrong here? Thanks a million!
CORRECTION
select last_insert_id()
Damn these fat fingers
--- On Fri, 2
--
View this message in context: http://old.nabble.com/insert-going-wrong-after-going-from-Sybase-to-MySQL-tp27274795p27280982.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: insert going wrong after going from Sybase to MySQL
Posted by charlie bird <ze...@yahoo.co.uk>.
MySql doesn't do @@IDENITY
Use this:
select last_insert_id
instead
--- On Fri, 22/1/10, StevenF <st...@web2.filipowicz.com> wrote:
> From: StevenF <st...@web2.filipowicz.com>
> Subject: insert going wrong after going from Sybase to MySQL
> To: user-java@ibatis.apache.org
> Date: Friday, 22 January, 2010, 15:19
>
> Hi All,
>
> I'm switching the database of an application from Sybase to
> MySQL, but the
> insert statements aren't going well.
>
> I have this which works for Sybase:
> --------
> <statement id = "insertChesser"
> parameterClass="nl.chessone40.domain.Chesser"
> resultClass="java.lang.Long">
> INSERT INTO Chesser
> (login,
> password,
> firstName,
> lastName,
> credits)
> values
> (#login#,
> #password#,
> #firstName#,
> #lastName#,
> #credits#)
> SELECT @@IDENTITY AS value
> </statement>
> ----------
>
> The above doesn't work for MySQL, what would I need to
> change to make it
> work for MySQL?
>
> Any help is very welcome!
>
> Thanks!
> --
> View this message in context: http://old.nabble.com/insert-going-wrong-after-going-from-Sybase-to-MySQL-tp27274795p27274795.html
> Sent from the iBATIS - User - Java mailing list archive at
> Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: insert going wrong after going from Sybase to MySQL
Posted by charlie bird <ze...@yahoo.co.uk>.
CORRECTION
select last_insert_id()
Damn these fat fingers
--- On Fri, 22/1/10, StevenF <st...@web2.filipowicz.com> wrote:
> From: StevenF <st...@web2.filipowicz.com>
> Subject: insert going wrong after going from Sybase to MySQL
> To: user-java@ibatis.apache.org
> Date: Friday, 22 January, 2010, 15:19
>
> Hi All,
>
> I'm switching the database of an application from Sybase to
> MySQL, but the
> insert statements aren't going well.
>
> I have this which works for Sybase:
> --------
> <statement id = "insertChesser"
> parameterClass="nl.chessone40.domain.Chesser"
> resultClass="java.lang.Long">
> INSERT INTO Chesser
> (login,
> password,
> firstName,
> lastName,
> credits)
> values
> (#login#,
> #password#,
> #firstName#,
> #lastName#,
> #credits#)
> SELECT @@IDENTITY AS value
> </statement>
> ----------
>
> The above doesn't work for MySQL, what would I need to
> change to make it
> work for MySQL?
>
> Any help is very welcome!
>
> Thanks!
> --
> View this message in context: http://old.nabble.com/insert-going-wrong-after-going-from-Sybase-to-MySQL-tp27274795p27274795.html
> Sent from the iBATIS - User - Java mailing list archive at
> Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org