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 Chris Marshall <ch...@campsbayterrace.com> on 2008/04/18 14:10:05 UTC
iBATIS insert does not execute
I have spent a couple of days on this and have read the FAQ and
googled without success, so hope I can get some help:
I am trying to persist an instance of the class:
public class Role {
private Integer roleid;
private Integer parentid;
private String name;
private String notes;
public Integer getRoleid() {
return roleid;
}
.......&c
}
with role_SqlMap.xml the snipprt:
<insert id="insert" parameterClass="net.mycompany.Role">
insert into role (RoleID, ParentID, Name, Notes)
values (#roleid:INTEGER#, #parentid:INTEGER#, #name:VARCHAR#,
#notes:VARCHAR#)
</insert>
and the java statement:
sqlMap.insert("role.insert", role);
which generates the folloing log entry:
DEBUG [http-8888-Processor4] - {conn-100009} Preparing Statement:
insert into role (RoleID, ParentID, Name, Notes) values (?, ?, ?,
?)
but no Executing Statement: or Parameters: entries.
Also no exception is thrown nor is anything writtent to the (MySQL) database.
Re: iBATIS insert does not execute
Posted by Larry Meadors <la...@gmail.com>.
I pass null in all the time for that, so that seems kind of unlikely
(of course mine is M$SQL-2K5).
Maybe you could try jdbc? If that doesn't work, we know it's not
ibatis. If it does, then we may need to step through the code and see
where it's failing...
Larry
On Fri, Apr 18, 2008 at 8:29 AM, Chris Marshall
<ch...@campsbayterrace.com> wrote:
> Sorry to bug you again but I think that the problem is related to
> passing a null primary key in the insert statement when the key is
> autogenerated (by MySQL).
> What is the best practice here? I have tried the following MySQL
> specific SqlMap snippet with success, but was hoping for a solution
> portable among databases:
>
>
> <insert id="insert" parameterClass="net.mycompany.Role">
> <selectKey resultClass="java.lang.Integer" keyProperty="roleid">
> select LAST_INSERT_ID() AS value
> </selectKey>
>
> insert into role (RoleID, ParentID, Name, Notes)
> values (#roleid:INTEGER#, #parentid:INTEGER#, #name:VARCHAR#,
> #notes:VARCHAR#)
> </insert>
>
> Thanks Chris
>
>
> On 18/04/2008, Brandon Goodin <br...@gmail.com> wrote:
>
>
> > Is this the only insert that is not working for you? Do you have others that
> > are working? What version of iBATIS are you using?
> >
> > Brandon Goodin
>
Re: iBATIS insert does not execute
Posted by Chris Marshall <ch...@campsbayterrace.com>.
Thanks for the tip Brandon,
That will certainly solve the portability issue and fits well with the
way we have structured our system. The selectKey approach is suitable
given your suggestion.
Thanks Chris
On 18/04/2008, Brandon Goodin <br...@gmail.com> wrote:
> What is your overall plan for deployment. Do you have sql scripts that get
> run against the target database to build the tables and such? How will you
> configure the connection url? What i've done in the past is to simply have a
> property file that contains the select key sql in it. I pass this property
> file in when my sqlmap is loaded and use the ${selectQuery} notation in my
> sqlmap.
>
> Brandon
>
>
> On Fri, Apr 18, 2008 at 9:29 AM, Chris Marshall <ch...@campsbayterrace.com>
> wrote:
> > Sorry to bug you again but I think that the problem is related to
> > passing a null primary key in the insert statement when the key is
> > autogenerated (by MySQL).
> > What is the best practice here? I have tried the following MySQL
> > specific SqlMap snippet with success, but was hoping for a solution
> > portable among databases:
> >
> >
> > <insert id="insert" parameterClass="net.mycompany.Role">
> > <selectKey resultClass="java.lang.Integer" keyProperty="roleid">
> > select LAST_INSERT_ID() AS value
> > </selectKey>
> >
> > insert into role (RoleID, ParentID, Name, Notes)
> > values (#roleid:INTEGER#, #parentid:INTEGER#, #name:VARCHAR#,
> > #notes:VARCHAR#)
> > </insert>
> >
> > Thanks Chris
> >
> >
> > On 18/04/2008, Brandon Goodin <br...@gmail.com> wrote:
> >
> >
> >
> > > Is this the only insert that is not working for you? Do you have others
> that
> > > are working? What version of iBATIS are you using?
> > >
> > > Brandon Goodin
> >
>
>
Re: iBATIS insert does not execute
Posted by Chris Marshall <ch...@campsbayterrace.com>.
Hi Niels,
Thanks for your patience.
I have a relational-service mapping layer which acts on multiple
tables within a transaction - a very common pattern - but the insert()
did not appear to return the autogenerated id within the transaction
scope. It worked OK when I removed the startTransaction statement but
failed with it in place. However I have refactored my code and it is
now working as expected - unfortunately I do not know why it was not
working before - but was obviously not related to iBATIS (btw I never
thought it was!). What I meant by the DTD being correct is that I
checked that I am using a version that includes the selectKey stanza.
Many thanks, Chris.
RE: iBATIS insert does not execute
Posted by Niels Beekman <n....@wis.nl>.
I don't understand what you're trying to do:
a) Do you want to get rid of the separate selectKey definition, if so,
why?
b) What do you mean by the DTD is correct?
c) Integer id = (Integer) sqlMap.insert("statementId"); should just
plain work, if not, try debugging an insert by setting breakpoints in
SqlMapExecutorDelegate.insert() and
SqlMapExecutorDelegate.executeSelectKey()
d) What does this have to do with transactions? Is it because the select
key query executes in a separate statement and you have auto-commit on?
Niels
-----Original Message-----
From: Chris Marshall [mailto:chris@campsbayterrace.com]
Sent: Saturday, April 19, 2008 5:24 PM
To: user-java@ibatis.apache.org
Subject: Re: iBATIS insert does not execute
Thanks for your input Niels,
The DTD is correct and I have no problem with the return from
selectKey, but I would like to return the generated key to the
application as follows:
Integer id = (Integer) sqlMap.insert(".....&c);
by defining the insert xml as follows:
<insert id="insert" parameterClass="MyParameterClass"
resultClass="Integer">
.........&c including selectKey
The selectKey stanza works fine, but I would like to have the id in
the application before committing the transaction for various reasons
(mainly because nested transactions are not supported).
I have seen the example of this that I included in my previous post at:
http://opensource.atlassian.com/confluence/oss/pages/viewpage.action?pag
eId=407
Regards Chris
Re: iBATIS insert does not execute
Posted by Chris Marshall <ch...@campsbayterrace.com>.
Thanks for your input Niels,
The DTD is correct and I have no problem with the return from
selectKey, but I would like to return the generated key to the
application as follows:
Integer id = (Integer) sqlMap.insert(".....&c);
by defining the insert xml as follows:
<insert id="insert" parameterClass="MyParameterClass" resultClass="Integer">
.........&c including selectKey
The selectKey stanza works fine, but I would like to have the id in
the application before committing the transaction for various reasons
(mainly because nested transactions are not supported).
I have seen the example of this that I included in my previous post at:
http://opensource.atlassian.com/confluence/oss/pages/viewpage.action?pageId=407
Regards Chris
RE: iBATIS insert does not execute
Posted by Niels Beekman <n....@wis.nl>.
That should work with the Java version. You are using unsupported
attributes, relevant DTD-piece:
<!ELEMENT selectKey (#PCDATA | include)*>
<!ATTLIST selectKey
resultClass CDATA #IMPLIED
keyProperty CDATA #IMPLIED
type (pre|post) #IMPLIED
>
I don't know if that helps, try enabling debug to see if the
selectKey-query gets executed, and if so, what results are returned.
HTH,
Niels
-----Original Message-----
From: Chris Marshall [mailto:chris@campsbayterrace.com]
Sent: Saturday, April 19, 2008 10:17 AM
To: user-java@ibatis.apache.org
Subject: Re: iBATIS insert does not execute
Hopefully the final question on this issue, which may be because of my
ignorance:
It appears that iBATIS .NET insert statement returns the generated key
as in:
<insert id="InsertOrganization" parameterClass="Organization"
resultClass="int">
<selectKey property="Id" type="post" resultClass="int">
SELECT LAST_INSERT_ID() AS value
</selectKey>
INSERT INTO Organizations
(Org_Code, Org_Name)
VALUES
(#Code#, #Name#)
</insert>
but that this is not supported in the Java version.
Is this correct, and if so, is there any intention to support it in
future?
Regards Chris
On 18/04/2008, Brandon Goodin <br...@gmail.com> wrote:
> What is your overall plan for deployment. Do you have sql scripts that
get
> run against the target database to build the tables and such? How will
you
> configure the connection url? What i've done in the past is to simply
have a
> property file that contains the select key sql in it. I pass this
property
> file in when my sqlmap is loaded and use the ${selectQuery} notation
in my
> sqlmap.
>
> Brandon
Re: iBATIS insert does not execute
Posted by Chris Marshall <ch...@campsbayterrace.com>.
Hopefully the final question on this issue, which may be because of my
ignorance:
It appears that iBATIS .NET insert statement returns the generated key as in:
<insert id="InsertOrganization" parameterClass="Organization" resultClass="int">
<selectKey property="Id" type="post" resultClass="int">
SELECT LAST_INSERT_ID() AS value
</selectKey>
INSERT INTO Organizations
(Org_Code, Org_Name)
VALUES
(#Code#, #Name#)
</insert>
but that this is not supported in the Java version.
Is this correct, and if so, is there any intention to support it in future?
Regards Chris
On 18/04/2008, Brandon Goodin <br...@gmail.com> wrote:
> What is your overall plan for deployment. Do you have sql scripts that get
> run against the target database to build the tables and such? How will you
> configure the connection url? What i've done in the past is to simply have a
> property file that contains the select key sql in it. I pass this property
> file in when my sqlmap is loaded and use the ${selectQuery} notation in my
> sqlmap.
>
> Brandon
Re: iBATIS insert does not execute
Posted by Brandon Goodin <br...@gmail.com>.
What is your overall plan for deployment. Do you have sql scripts that get
run against the target database to build the tables and such? How will you
configure the connection url? What i've done in the past is to simply have a
property file that contains the select key sql in it. I pass this property
file in when my sqlmap is loaded and use the ${selectQuery} notation in my
sqlmap.
Brandon
On Fri, Apr 18, 2008 at 9:29 AM, Chris Marshall <ch...@campsbayterrace.com>
wrote:
> Sorry to bug you again but I think that the problem is related to
> passing a null primary key in the insert statement when the key is
> autogenerated (by MySQL).
> What is the best practice here? I have tried the following MySQL
> specific SqlMap snippet with success, but was hoping for a solution
> portable among databases:
>
> <insert id="insert" parameterClass="net.mycompany.Role">
> <selectKey resultClass="java.lang.Integer" keyProperty="roleid">
> select LAST_INSERT_ID() AS value
> </selectKey>
> insert into role (RoleID, ParentID, Name, Notes)
> values (#roleid:INTEGER#, #parentid:INTEGER#, #name:VARCHAR#,
> #notes:VARCHAR#)
> </insert>
>
> Thanks Chris
>
> On 18/04/2008, Brandon Goodin <br...@gmail.com> wrote:
> > Is this the only insert that is not working for you? Do you have others
> that
> > are working? What version of iBATIS are you using?
> >
> > Brandon Goodin
>
Re: iBATIS insert does not execute
Posted by Chris Marshall <ch...@campsbayterrace.com>.
Sorry to bug you again but I think that the problem is related to
passing a null primary key in the insert statement when the key is
autogenerated (by MySQL).
What is the best practice here? I have tried the following MySQL
specific SqlMap snippet with success, but was hoping for a solution
portable among databases:
<insert id="insert" parameterClass="net.mycompany.Role">
<selectKey resultClass="java.lang.Integer" keyProperty="roleid">
select LAST_INSERT_ID() AS value
</selectKey>
insert into role (RoleID, ParentID, Name, Notes)
values (#roleid:INTEGER#, #parentid:INTEGER#, #name:VARCHAR#,
#notes:VARCHAR#)
</insert>
Thanks Chris
On 18/04/2008, Brandon Goodin <br...@gmail.com> wrote:
> Is this the only insert that is not working for you? Do you have others that
> are working? What version of iBATIS are you using?
>
> Brandon Goodin
Re: iBATIS insert does not execute
Posted by Chris Marshall <ch...@campsbayterrace.com>.
Thanks for you rapid reply Brandon.
It appears to be on all tables, but read and delete operate perfectly.
Versions are:
ibatis 2.3.0.677
mysql connector 5.0.8
mysql 5.0.51
jre jdk 1.6.0_04
Thanks, Chris
On 18/04/2008, Brandon Goodin <br...@gmail.com> wrote:
> Is this the only insert that is not working for you? Do you have others that
> are working? What version of iBATIS are you using?
>
> Brandon Goodin
>
>
> On Fri, Apr 18, 2008 at 7:10 AM, Chris Marshall <ch...@campsbayterrace.com>
> wrote:
> > I have spent a couple of days on this and have read the FAQ and
> > googled without success, so hope I can get some help:
> > I am trying to persist an instance of the class:
> >
> > public class Role {
> >
> > private Integer roleid;
> > private Integer parentid;
> > private String name;
> > private String notes;
> >
> > public Integer getRoleid() {
> > return roleid;
> > }
> > .......&c
> > }
> >
> > with role_SqlMap.xml the snipprt:
> >
> > <insert id="insert" parameterClass="net.mycompany.Role">
> > insert into role (RoleID, ParentID, Name, Notes)
> > values (#roleid:INTEGER#, #parentid:INTEGER#, #name:VARCHAR#,
> > #notes:VARCHAR#)
> > </insert>
> >
> > and the java statement:
> >
> > sqlMap.insert("role.insert", role);
> >
> > which generates the folloing log entry:
> >
> > DEBUG [http-8888-Processor4] - {conn-100009} Preparing Statement:
> > insert into role (RoleID, ParentID, Name, Notes) values (?, ?, ?,
> > ?)
> >
> > but no Executing Statement: or Parameters: entries.
> > Also no exception is thrown nor is anything writtent to the (MySQL)
> database.
> >
>
>
Re: iBATIS insert does not execute
Posted by Chris Marshall <ch...@campsbayterrace.com>.
Further to my previous reply - I have checked all field values to be
correct, with only the primary autogenerated key being null.
Regards Chris
On 18/04/2008, Brandon Goodin <br...@gmail.com> wrote:
> Is this the only insert that is not working for you? Do you have others that
> are working? What version of iBATIS are you using?
>
> Brandon Goodin
>
>
> On Fri, Apr 18, 2008 at 7:10 AM, Chris Marshall <ch...@campsbayterrace.com>
> wrote:
> > I have spent a couple of days on this and have read the FAQ and
> > googled without success, so hope I can get some help:
> > I am trying to persist an instance of the class:
> >
> > public class Role {
> >
> > private Integer roleid;
> > private Integer parentid;
> > private String name;
> > private String notes;
> >
> > public Integer getRoleid() {
> > return roleid;
> > }
> > .......&c
> > }
> >
> > with role_SqlMap.xml the snipprt:
> >
> > <insert id="insert" parameterClass="net.mycompany.Role">
> > insert into role (RoleID, ParentID, Name, Notes)
> > values (#roleid:INTEGER#, #parentid:INTEGER#, #name:VARCHAR#,
> > #notes:VARCHAR#)
> > </insert>
> >
> > and the java statement:
> >
> > sqlMap.insert("role.insert", role);
> >
> > which generates the folloing log entry:
> >
> > DEBUG [http-8888-Processor4] - {conn-100009} Preparing Statement:
> > insert into role (RoleID, ParentID, Name, Notes) values (?, ?, ?,
> > ?)
> >
> > but no Executing Statement: or Parameters: entries.
> > Also no exception is thrown nor is anything writtent to the (MySQL)
> database.
> >
>
>
Re: iBATIS insert does not execute
Posted by Brandon Goodin <br...@gmail.com>.
Is this the only insert that is not working for you? Do you have others that
are working? What version of iBATIS are you using?
Brandon Goodin
On Fri, Apr 18, 2008 at 7:10 AM, Chris Marshall <ch...@campsbayterrace.com>
wrote:
> I have spent a couple of days on this and have read the FAQ and
> googled without success, so hope I can get some help:
> I am trying to persist an instance of the class:
>
> public class Role {
>
> private Integer roleid;
> private Integer parentid;
> private String name;
> private String notes;
>
> public Integer getRoleid() {
> return roleid;
> }
> .......&c
> }
>
> with role_SqlMap.xml the snipprt:
>
> <insert id="insert" parameterClass="net.mycompany.Role">
> insert into role (RoleID, ParentID, Name, Notes)
> values (#roleid:INTEGER#, #parentid:INTEGER#, #name:VARCHAR#,
> #notes:VARCHAR#)
> </insert>
>
> and the java statement:
>
> sqlMap.insert("role.insert", role);
>
> which generates the folloing log entry:
>
> DEBUG [http-8888-Processor4] - {conn-100009} Preparing Statement:
> insert into role (RoleID, ParentID, Name, Notes) values (?, ?, ?,
> ?)
>
> but no Executing Statement: or Parameters: entries.
> Also no exception is thrown nor is anything writtent to the (MySQL)
> database.
>