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 Edwin Lukaweski <se...@rogers.com> on 2006/07/22 19:01:53 UTC
insert sql exception how to
Hi:
I need some hints as to how to handle a situation with iBatis.
I would like to perform an INSERT, then catch the sqlexception and determine if it is caused by a duplicate key. If so, I will want to perform an UPDATE to the record.
I realize that DAO throws a daoexception, but I am not quite sure how to handle it.
Does anybody have any ideas?
Thanks in advance,
Edwin
Re: Re-Post:: Same Connection Guaranteed during a Transaction?
Posted by Brandon Goodin <br...@gmail.com>.
iBATIS 1.x source code is still available for you to explore. iBATIS 1.x is
no longer supported. You will likely not get any assistance with this issue.
Brandon
On 7/30/06, Fred Janon <fj...@yahoo.com> wrote:
>
> Sorry for the repost, but I am atill looking for an answer to my
> question. Basically I need to do an INSERT and get the ID (PK) of the
> insert in mySQL. MySQL has the "last_insert_id()" function to do that
> but it has to be executed in the same connection as the INSERT,
> otherwise it returns 0. I have 2 mapped statements in my XML maps, one
> to do the insert and one to do the last_insert_id(). I need to find a
> way to execute them in the same connection. I tried to include the 2
> stements in a transaction but they are still executed in 2 connections.
>
> Thanks,
>
> Fred
>
> --- Fred Janon <fj...@yahoo.com> wrote:
>
> > I am trying to get the last_insert_id() in mySQL after an insert. I
> > read
> > in a forum that during a transaction, the same connection was
> > guaranteed
> > to be used for the duration of the transaction. That's not my
> > experience
> > using Spring and iBatis 1.3.1. Using the code below, I get 2
> > different
> > connection ids. I am a bit lost with the concept of a transaction if
> > the
> > connection is not maintained.
> >
> > Any comments?
> >
> > Thanks
> >
> > Fred
> >
> > getSqlMap().startTransaction();
> > connectionId =
> >
> (Integer)getSqlMapTemplate().executeQueryForObject("ConnectionId",null);
> > System.out.println("iBatisTasks createTask connectionId: " +
> > connectionId);
> >
> > getSqlMapTemplate().executeUpdate("createTask", task);
> >
> > Integer taskId = new Integer(0);
> > taskId =
> >
> (Integer)getSqlMapTemplate().executeQueryForObject("lastTaskId",null);
> > System.out.println(">>>>>>>>>> iBatisTasks createTask id: " +
> > taskId);
> >
> > connectionId =
> >
> (Integer)getSqlMapTemplate().executeQueryForObject("ConnectionId",null);
> > System.out.println("iBatisTasks createTask connectionId: " +
> > connectionId);
> >
> > getSqlMap().commitTransaction();
> >
> > > begin:vcard
> > fn:fjanon
> > n:Janon;Fred
> > email;internet:fjanon@yahoo.com
> > title:Sr S/W Engineer & Architect
> > x-mozilla-html:TRUE
> > url:http://www.geocities.com/fjanon
> > version:2.1
> > end:vcard
> >
> >
>
>
Re: Re-Post:: Same Connection Guaranteed during a Transaction?
Posted by Gwyn Evans <gw...@gmail.com>.
Just from the docs, that looks to be solved by the <selectKey>
statements in 2.0 - Is that an option?
/Gwyn
On 31/07/06, Fred Janon <fj...@yahoo.com> wrote:
> Sorry for the repost, but I am atill looking for an answer to my
> question. Basically I need to do an INSERT and get the ID (PK) of the
> insert in mySQL. MySQL has the "last_insert_id()" function to do that
> but it has to be executed in the same connection as the INSERT,
> otherwise it returns 0. I have 2 mapped statements in my XML maps, one
> to do the insert and one to do the last_insert_id(). I need to find a
> way to execute them in the same connection. I tried to include the 2
> stements in a transaction but they are still executed in 2 connections.
>
Re: Same Connection Guaranteed during a Transaction?
Posted by Larry Meadors <lm...@apache.org>.
Ouch, ok.
On 7/24/06, Fred Janon <fj...@yahoo.com> wrote:
> I am using iBatis v1 not v2, there is no <selectKey> in V1...
>
> Larry Meadors wrote:
> > Why not just use a selectKey element?
> >
> > Larry
> >
> >
> > On 7/24/06, Fred Janon <fj...@yahoo.com> wrote:
> >> I am trying to get the last_insert_id() in mySQL after an insert. I read
> >> in a forum that during a transaction, the same connection was guaranteed
> >> to be used for the duration of the transaction. That's not my experience
> >> using Spring and iBatis 1.3.1. Using the code below, I get 2 different
> >> connection ids. I am a bit lost with the concept of a transaction if the
> >> connection is not maintained.
> >>
> >> Any comments?
> >>
> >> Thanks
> >>
> >> Fred
> >>
> >> getSqlMap().startTransaction();
> >> connectionId =
> >> (Integer)getSqlMapTemplate().executeQueryForObject("ConnectionId",null);
> >> System.out.println("iBatisTasks createTask connectionId: " +
> >> connectionId);
> >>
> >> getSqlMapTemplate().executeUpdate("createTask", task);
> >>
> >> Integer taskId = new Integer(0);
> >> taskId =
> >> (Integer)getSqlMapTemplate().executeQueryForObject("lastTaskId",null);
> >> System.out.println(">>>>>>>>>> iBatisTasks createTask id: " + taskId);
> >>
> >> connectionId =
> >> (Integer)getSqlMapTemplate().executeQueryForObject("ConnectionId",null);
> >> System.out.println("iBatisTasks createTask connectionId: " +
> >> connectionId);
> >>
> >> getSqlMap().commitTransaction();
> >>
> >>
> >>
> >>
> >
>
>
>
Re: Same Connection Guaranteed during a Transaction?
Posted by Fred Janon <fj...@yahoo.com>.
I am using iBatis v1 not v2, there is no <selectKey> in V1...
Larry Meadors wrote:
> Why not just use a selectKey element?
>
> Larry
>
>
> On 7/24/06, Fred Janon <fj...@yahoo.com> wrote:
>> I am trying to get the last_insert_id() in mySQL after an insert. I read
>> in a forum that during a transaction, the same connection was guaranteed
>> to be used for the duration of the transaction. That's not my experience
>> using Spring and iBatis 1.3.1. Using the code below, I get 2 different
>> connection ids. I am a bit lost with the concept of a transaction if the
>> connection is not maintained.
>>
>> Any comments?
>>
>> Thanks
>>
>> Fred
>>
>> getSqlMap().startTransaction();
>> connectionId =
>> (Integer)getSqlMapTemplate().executeQueryForObject("ConnectionId",null);
>> System.out.println("iBatisTasks createTask connectionId: " +
>> connectionId);
>>
>> getSqlMapTemplate().executeUpdate("createTask", task);
>>
>> Integer taskId = new Integer(0);
>> taskId =
>> (Integer)getSqlMapTemplate().executeQueryForObject("lastTaskId",null);
>> System.out.println(">>>>>>>>>> iBatisTasks createTask id: " + taskId);
>>
>> connectionId =
>> (Integer)getSqlMapTemplate().executeQueryForObject("ConnectionId",null);
>> System.out.println("iBatisTasks createTask connectionId: " +
>> connectionId);
>>
>> getSqlMap().commitTransaction();
>>
>>
>>
>>
>
Re: Same Connection Guaranteed during a Transaction?
Posted by Larry Meadors <lm...@apache.org>.
Why not just use a selectKey element?
Larry
On 7/24/06, Fred Janon <fj...@yahoo.com> wrote:
> I am trying to get the last_insert_id() in mySQL after an insert. I read
> in a forum that during a transaction, the same connection was guaranteed
> to be used for the duration of the transaction. That's not my experience
> using Spring and iBatis 1.3.1. Using the code below, I get 2 different
> connection ids. I am a bit lost with the concept of a transaction if the
> connection is not maintained.
>
> Any comments?
>
> Thanks
>
> Fred
>
> getSqlMap().startTransaction();
> connectionId =
> (Integer)getSqlMapTemplate().executeQueryForObject("ConnectionId",null);
> System.out.println("iBatisTasks createTask connectionId: " + connectionId);
>
> getSqlMapTemplate().executeUpdate("createTask", task);
>
> Integer taskId = new Integer(0);
> taskId =
> (Integer)getSqlMapTemplate().executeQueryForObject("lastTaskId",null);
> System.out.println(">>>>>>>>>> iBatisTasks createTask id: " + taskId);
>
> connectionId =
> (Integer)getSqlMapTemplate().executeQueryForObject("ConnectionId",null);
> System.out.println("iBatisTasks createTask connectionId: " + connectionId);
>
> getSqlMap().commitTransaction();
>
>
>
>
Re-Post:: Same Connection Guaranteed during a Transaction?
Posted by Fred Janon <fj...@yahoo.com>.
Sorry for the repost, but I am atill looking for an answer to my
question. Basically I need to do an INSERT and get the ID (PK) of the
insert in mySQL. MySQL has the "last_insert_id()" function to do that
but it has to be executed in the same connection as the INSERT,
otherwise it returns 0. I have 2 mapped statements in my XML maps, one
to do the insert and one to do the last_insert_id(). I need to find a
way to execute them in the same connection. I tried to include the 2
stements in a transaction but they are still executed in 2 connections.
Thanks,
Fred
--- Fred Janon <fj...@yahoo.com> wrote:
> I am trying to get the last_insert_id() in mySQL after an insert. I
> read
> in a forum that during a transaction, the same connection was
> guaranteed
> to be used for the duration of the transaction. That's not my
> experience
> using Spring and iBatis 1.3.1. Using the code below, I get 2
> different
> connection ids. I am a bit lost with the concept of a transaction if
> the
> connection is not maintained.
>
> Any comments?
>
> Thanks
>
> Fred
>
> getSqlMap().startTransaction();
> connectionId =
>
(Integer)getSqlMapTemplate().executeQueryForObject("ConnectionId",null);
> System.out.println("iBatisTasks createTask connectionId: " +
> connectionId);
>
> getSqlMapTemplate().executeUpdate("createTask", task);
>
> Integer taskId = new Integer(0);
> taskId =
>
(Integer)getSqlMapTemplate().executeQueryForObject("lastTaskId",null);
> System.out.println(">>>>>>>>>> iBatisTasks createTask id: " +
> taskId);
>
> connectionId =
>
(Integer)getSqlMapTemplate().executeQueryForObject("ConnectionId",null);
> System.out.println("iBatisTasks createTask connectionId: " +
> connectionId);
>
> getSqlMap().commitTransaction();
>
> > begin:vcard
> fn:fjanon
> n:Janon;Fred
> email;internet:fjanon@yahoo.com
> title:Sr S/W Engineer & Architect
> x-mozilla-html:TRUE
> url:http://www.geocities.com/fjanon
> version:2.1
> end:vcard
>
>
Same Connection Guaranteed during a Transaction?
Posted by Fred Janon <fj...@yahoo.com>.
I am trying to get the last_insert_id() in mySQL after an insert. I read
in a forum that during a transaction, the same connection was guaranteed
to be used for the duration of the transaction. That's not my experience
using Spring and iBatis 1.3.1. Using the code below, I get 2 different
connection ids. I am a bit lost with the concept of a transaction if the
connection is not maintained.
Any comments?
Thanks
Fred
getSqlMap().startTransaction();
connectionId =
(Integer)getSqlMapTemplate().executeQueryForObject("ConnectionId",null);
System.out.println("iBatisTasks createTask connectionId: " + connectionId);
getSqlMapTemplate().executeUpdate("createTask", task);
Integer taskId = new Integer(0);
taskId =
(Integer)getSqlMapTemplate().executeQueryForObject("lastTaskId",null);
System.out.println(">>>>>>>>>> iBatisTasks createTask id: " + taskId);
connectionId =
(Integer)getSqlMapTemplate().executeQueryForObject("ConnectionId",null);
System.out.println("iBatisTasks createTask connectionId: " + connectionId);
getSqlMap().commitTransaction();
Re: Re: insert sql exception how to
Posted by Edwin Lukaweski <se...@rogers.com>.
Thank you all for the suggestions. They are all good.
We are currently using DB2, but need to target Oracle and MySql.
I will check to see if these have a REPLACE. I know DB2 and Oracle have
stored procedures, but not sure about MySql.
Thanks,
Edwin
----- Original Message -----
From: "Larry Meadors" <lm...@apache.org>
To: <us...@ibatis.apache.org>
Sent: Sunday, July 23, 2006 2:10 AM
Subject: Re: Re: insert sql exception how to
> FYI: if your database allows stored procedures, you will most likely
> increase performance 2x to 3x by making this a stored procedure...if
> that is a possibility, I would strongly suggest that you do it that
> way instead.
>
> Larry
>
>
> On 7/23/06, Jerome Gagner <ph...@gmail.com> wrote:
>> What database are you using? I know a lot of the major databases have
>> "REPLACE" functionality, which would insert or update based on a key.
>>
>> -J
>>
>> On 7/22/06, Brandon Goodin <br...@gmail.com> wrote:
>> > Yes you would have to check the nested exceptions to see if a SQL
>> > exception
>> > exists.
>> >
>> >
>> > Brandon
>> >
>> > On 7/22/06, Edwin Lukaweski < serjann@rogers.com> wrote:
>> > >
>> > >
>> > >
>> > > Thank you for the answer.
>> > >
>> > > 1) I am already commited to iBatis DAO. So, I cannot switch to
>> > > Spring
>> > >
>> > > 2) I realize that I could do a SELECT first, but I am trying to
>> > optimize resources so it is best to handle the pre-existance on an
>> > exception
>> > basis.
>> > >
>> > > 3) Do I have to crawl down the exception chain, from
>> > > daoexception, to
>> > find the SQLException?
>> > >
>> > > Thanks,
>> > >
>> > > Edwin
>> > >
>> > >
>> > >
>> > > ----- Original Message -----
>> > > From: Brandon Goodin
>> > > To: user-java@ibatis.apache.org
>> > > Sent: Saturday, July 22, 2006 4:11 PM
>> > > Subject: Re: insert sql exception how to
>> > >
>> > > If you are just starting into this project I would recommend that you
>> > > use
>> > Spring for your DAO factory. As far as SQL Exceptions you can use the
>> > getErrorCode to interpret what error it is. This will not be
>> > cross-database
>> > compatible. You could simply avoid the SQLException by performing a
>> > simple
>> > select on the database before you insert. If you have the ID then you
>> > make
>> > your change. If you don't have the ID you go ahead and update.
>> > >
>> > > Brandon
>> > >
>> > >
>> > > On 7/22/06, Edwin Lukaweski <se...@rogers.com> wrote:
>> > > >
>> > > >
>> > > >
>> > > > Hi:
>> > > >
>> > > > I need some hints as to how to handle a situation with iBatis.
>> > > >
>> > > > I would like to perform an INSERT, then catch the sqlexception
>> > > > and
>> > determine if it is caused by a duplicate key. If so, I will want to
>> > perform
>> > an UPDATE to the record.
>> > > >
>> > > > I realize that DAO throws a daoexception, but I am not quite
>> > > > sure
>> > how to handle it.
>> > > >
>> > > > Does anybody have any ideas?
>> > > >
>> > > > Thanks in advance,
>> > > > Edwin
>> > > >
>> > >
>> > >
>> > >
>> >
>> >
>>
>
Re: Re: insert sql exception how to
Posted by Larry Meadors <lm...@apache.org>.
FYI: if your database allows stored procedures, you will most likely
increase performance 2x to 3x by making this a stored procedure...if
that is a possibility, I would strongly suggest that you do it that
way instead.
Larry
On 7/23/06, Jerome Gagner <ph...@gmail.com> wrote:
> What database are you using? I know a lot of the major databases have
> "REPLACE" functionality, which would insert or update based on a key.
>
> -J
>
> On 7/22/06, Brandon Goodin <br...@gmail.com> wrote:
> > Yes you would have to check the nested exceptions to see if a SQL exception
> > exists.
> >
> >
> > Brandon
> >
> > On 7/22/06, Edwin Lukaweski < serjann@rogers.com> wrote:
> > >
> > >
> > >
> > > Thank you for the answer.
> > >
> > > 1) I am already commited to iBatis DAO. So, I cannot switch to Spring
> > >
> > > 2) I realize that I could do a SELECT first, but I am trying to
> > optimize resources so it is best to handle the pre-existance on an exception
> > basis.
> > >
> > > 3) Do I have to crawl down the exception chain, from daoexception, to
> > find the SQLException?
> > >
> > > Thanks,
> > >
> > > Edwin
> > >
> > >
> > >
> > > ----- Original Message -----
> > > From: Brandon Goodin
> > > To: user-java@ibatis.apache.org
> > > Sent: Saturday, July 22, 2006 4:11 PM
> > > Subject: Re: insert sql exception how to
> > >
> > > If you are just starting into this project I would recommend that you use
> > Spring for your DAO factory. As far as SQL Exceptions you can use the
> > getErrorCode to interpret what error it is. This will not be cross-database
> > compatible. You could simply avoid the SQLException by performing a simple
> > select on the database before you insert. If you have the ID then you make
> > your change. If you don't have the ID you go ahead and update.
> > >
> > > Brandon
> > >
> > >
> > > On 7/22/06, Edwin Lukaweski <se...@rogers.com> wrote:
> > > >
> > > >
> > > >
> > > > Hi:
> > > >
> > > > I need some hints as to how to handle a situation with iBatis.
> > > >
> > > > I would like to perform an INSERT, then catch the sqlexception and
> > determine if it is caused by a duplicate key. If so, I will want to perform
> > an UPDATE to the record.
> > > >
> > > > I realize that DAO throws a daoexception, but I am not quite sure
> > how to handle it.
> > > >
> > > > Does anybody have any ideas?
> > > >
> > > > Thanks in advance,
> > > > Edwin
> > > >
> > >
> > >
> > >
> >
> >
>
Javadocs for iBatis 1.3.1 or 1.x.x?
Posted by Fred Janon <fj...@yahoo.com>.
I can't find the javadocs for v1. I got a couple of pdfsbut no
javadocs. The javadocs for 2.0 are in the zip file but not in 1.3.1. I
looked in the wiki, no trace of them. Do they exist?
Thanks
Fred
Re: Re: insert sql exception how to
Posted by Jerome Gagner <ph...@gmail.com>.
What database are you using? I know a lot of the major databases have
"REPLACE" functionality, which would insert or update based on a key.
-J
On 7/22/06, Brandon Goodin <br...@gmail.com> wrote:
> Yes you would have to check the nested exceptions to see if a SQL exception
> exists.
>
>
> Brandon
>
> On 7/22/06, Edwin Lukaweski < serjann@rogers.com> wrote:
> >
> >
> >
> > Thank you for the answer.
> >
> > 1) I am already commited to iBatis DAO. So, I cannot switch to Spring
> >
> > 2) I realize that I could do a SELECT first, but I am trying to
> optimize resources so it is best to handle the pre-existance on an exception
> basis.
> >
> > 3) Do I have to crawl down the exception chain, from daoexception, to
> find the SQLException?
> >
> > Thanks,
> >
> > Edwin
> >
> >
> >
> > ----- Original Message -----
> > From: Brandon Goodin
> > To: user-java@ibatis.apache.org
> > Sent: Saturday, July 22, 2006 4:11 PM
> > Subject: Re: insert sql exception how to
> >
> > If you are just starting into this project I would recommend that you use
> Spring for your DAO factory. As far as SQL Exceptions you can use the
> getErrorCode to interpret what error it is. This will not be cross-database
> compatible. You could simply avoid the SQLException by performing a simple
> select on the database before you insert. If you have the ID then you make
> your change. If you don't have the ID you go ahead and update.
> >
> > Brandon
> >
> >
> > On 7/22/06, Edwin Lukaweski <se...@rogers.com> wrote:
> > >
> > >
> > >
> > > Hi:
> > >
> > > I need some hints as to how to handle a situation with iBatis.
> > >
> > > I would like to perform an INSERT, then catch the sqlexception and
> determine if it is caused by a duplicate key. If so, I will want to perform
> an UPDATE to the record.
> > >
> > > I realize that DAO throws a daoexception, but I am not quite sure
> how to handle it.
> > >
> > > Does anybody have any ideas?
> > >
> > > Thanks in advance,
> > > Edwin
> > >
> >
> >
> >
>
>
Re: insert sql exception how to
Posted by Brandon Goodin <br...@gmail.com>.
Yes you would have to check the nested exceptions to see if a SQL exception
exists.
Brandon
On 7/22/06, Edwin Lukaweski <se...@rogers.com> wrote:
>
> Thank you for the answer.
>
> 1) I am already commited to iBatis DAO. So, I cannot switch to Spring
>
> 2) I realize that I could do a SELECT first, but I am trying to
> optimize resources so it is best to handle the pre-existance on an exception
> basis.
>
> 3) Do I have to crawl down the exception chain, from daoexception, to
> find the SQLException?
>
> Thanks,
> Edwin
>
>
> ----- Original Message -----
> *From:* Brandon Goodin <br...@gmail.com>
> *To:* user-java@ibatis.apache.org
> *Sent:* Saturday, July 22, 2006 4:11 PM
> *Subject:* Re: insert sql exception how to
>
> If you are just starting into this project I would recommend that you use
> Spring for your DAO factory. As far as SQL Exceptions you can use the
> getErrorCode to interpret what error it is. This will not be cross-database
> compatible. You could simply avoid the SQLException by performing a simple
> select on the database before you insert. If you have the ID then you make
> your change. If you don't have the ID you go ahead and update.
>
> Brandon
>
> On 7/22/06, Edwin Lukaweski <se...@rogers.com> wrote:
> >
> > Hi:
> >
> > I need some hints as to how to handle a situation with iBatis.
> >
> > I would like to perform an INSERT, then catch the sqlexception and
> > determine if it is caused by a duplicate key. If so, I will want to perform
> > an UPDATE to the record.
> >
> > I realize that DAO throws a daoexception, but I am not quite sure
> > how to handle it.
> >
> > Does anybody have any ideas?
> >
> > Thanks in advance,
> > Edwin
> >
> >
>
>
Re: insert sql exception how to
Posted by Edwin Lukaweski <se...@rogers.com>.
Thank you for the answer.
1) I am already commited to iBatis DAO. So, I cannot switch to Spring
2) I realize that I could do a SELECT first, but I am trying to optimize resources so it is best to handle the pre-existance on an exception basis.
3) Do I have to crawl down the exception chain, from daoexception, to find the SQLException?
Thanks,
Edwin
----- Original Message -----
From: Brandon Goodin
To: user-java@ibatis.apache.org
Sent: Saturday, July 22, 2006 4:11 PM
Subject: Re: insert sql exception how to
If you are just starting into this project I would recommend that you use Spring for your DAO factory. As far as SQL Exceptions you can use the getErrorCode to interpret what error it is. This will not be cross-database compatible. You could simply avoid the SQLException by performing a simple select on the database before you insert. If you have the ID then you make your change. If you don't have the ID you go ahead and update.
Brandon
On 7/22/06, Edwin Lukaweski <se...@rogers.com> wrote:
Hi:
I need some hints as to how to handle a situation with iBatis.
I would like to perform an INSERT, then catch the sqlexception and determine if it is caused by a duplicate key. If so, I will want to perform an UPDATE to the record.
I realize that DAO throws a daoexception, but I am not quite sure how to handle it.
Does anybody have any ideas?
Thanks in advance,
Edwin
Re: insert sql exception how to
Posted by Brandon Goodin <br...@gmail.com>.
If you are just starting into this project I would recommend that you use
Spring for your DAO factory. As far as SQL Exceptions you can use the
getErrorCode to interpret what error it is. This will not be cross-database
compatible. You could simply avoid the SQLException by performing a simple
select on the database before you insert. If you have the ID then you make
your change. If you don't have the ID you go ahead and update.
Brandon
On 7/22/06, Edwin Lukaweski <se...@rogers.com> wrote:
>
> Hi:
>
> I need some hints as to how to handle a situation with iBatis.
>
> I would like to perform an INSERT, then catch the sqlexception and
> determine if it is caused by a duplicate key. If so, I will want to perform
> an UPDATE to the record.
>
> I realize that DAO throws a daoexception, but I am not quite sure how
> to handle it.
>
> Does anybody have any ideas?
>
> Thanks in advance,
> Edwin
>
>