Return-Path: Delivered-To: apmail-ibatis-user-java-archive@www.apache.org Received: (qmail 91125 invoked from network); 15 Sep 2006 17:53:49 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 15 Sep 2006 17:53:49 -0000 Received: (qmail 11177 invoked by uid 500); 15 Sep 2006 17:53:45 -0000 Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org Received: (qmail 11162 invoked by uid 500); 15 Sep 2006 17:53:44 -0000 Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user-java@ibatis.apache.org Delivered-To: mailing list user-java@ibatis.apache.org Received: (qmail 11151 invoked by uid 99); 15 Sep 2006 17:53:44 -0000 X-ASF-Spam-Status: No, hits=0.5 required=10.0 tests=DNS_FROM_RFC_ABUSE,HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (hermes.apache.org: domain of jeffgbutler@gmail.com designates 64.233.184.227 as permitted sender) Received: from [64.233.184.227] (HELO wr-out-0506.google.com) (64.233.184.227) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 15 Sep 2006 10:53:42 -0700 Received: by wr-out-0506.google.com with SMTP id i20so1099478wra for ; Fri, 15 Sep 2006 10:52:21 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=tJC74in/bEYHTac1FaoUPOrtuxPQGz9DNaP1bAplZC1BIDOQx2HJojq1rxouejhWqZfLF6Wyc8ozBxE096A0jFl4L6XVmdasqV7iC9fspYr3CH6KvQMko7os6jqc1qx5rzsoNzb4KkQmFu1FyUoGO/llsn/gbsWuaxnf5Pf0otw= Received: by 10.90.66.9 with SMTP id o9mr1600964aga; Fri, 15 Sep 2006 10:52:20 -0700 (PDT) Received: by 10.90.74.4 with HTTP; Fri, 15 Sep 2006 10:52:20 -0700 (PDT) Message-ID: Date: Fri, 15 Sep 2006 12:52:20 -0500 From: "Jeff Butler" To: user-java@ibatis.apache.org Subject: Re: finding unique keys In-Reply-To: <1aec01c6d8e2$08efce60$0601a8c0@RIGCISERVER> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_42660_517316.1158342740786" References: <1a9701c6d8d0$ab1030c0$0601a8c0@RIGCISERVER> <1aa601c6d8d5$dab841a0$0601a8c0@RIGCISERVER> <1ad601c6d8dd$233c8a60$0601a8c0@RIGCISERVER> <1aec01c6d8e2$08efce60$0601a8c0@RIGCISERVER> X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N ------=_Part_42660_517316.1158342740786 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Call the getErrorCode() method on the exception. This will be -803 for duplicate keys on DB2. Jeff Butler On 9/15/06, Edwin Lukaweski wrote: > > Great! > > Now....I guess when the insert fails, via iBatis and an SQL_Map, I > will get an SQLExceptiion. Is there an easy way to find the -803? > > I want to intercept it and re-throw only non 803 exceptions. > > thanks, > Edwin > > > ----- Original Message ----- > *From:* Jeff Butler > *To:* user-java@ibatis.apache.org > *Sent:* Friday, September 15, 2006 12:08 PM > *Subject:* Re: finding unique keys > > > I just looked to see how Spring does it with DB2. They do look for -803 > as the error code from the SqlException. So I think your plan sounds good. > > Jeff Butler > > > > On 9/15/06, Edwin Lukaweski wrote: > > > > Thanks for all the feed back!!! > > > > Some answers: > > > > 1) I am using DB2 for z/OS version 7 > > > > 2) Yes, UUID's are by definition 'universally unique', excpept whe > > two > > are gengerated, say by two different JVMs, within the same millisecond. > > The, they turn out to be duplicates. > > > > 3) I like the idea of doing an optimistic INSERT. I have always had > > trouble figuring which sql code to look for that represents this > > situation. > > As best I can tell, it is SQLCODE 803. So, if I see this code in an > > exception, loop again, otherwise throw a real sqlexception. > > > > What do you think? > > > > thanks, > > Edwin > > > > ----- Original Message ----- > > From: "Larry Meadors" > > To: < user-java@ibatis.apache.org> > > Sent: Friday, September 15, 2006 11:03 AM > > Subject: Re: finding unique keys > > > > > > > +1 ;-) > > > > > > Larry > > > > > > > > > On 9/15/06, Jeff Butler < jeffgbutler@gmail.com> wrote: > > >> > > >> Some thoughts... > > >> > > >> 1. Aren't UUID's "universally unique" by definition? Are you sure > > you > > >> need > > >> to do this? > > >> > > >> 2. Table locking is almost always a bad idea. I had a similar > > situation > > >> to > > >> yours in that past, and decided to take the optomistic route, > > meaning... > > >> > > >> - generate the key > > >> - insert the record > > >> - if the insert blows because of a duplicate key, then generate a new > > key > > >> and try again > > >> > > >> Since the likelihood of generating a duplicate UUID should be very > > low, > > >> this > > >> approcah will work in the vast majority of cases. > > >> > > >> > > >> Jeff Butler > > >> > > >> > > >> > > >> > > >> On 9/15/06, Edwin Lukaweski < serjann@rogers.com> wrote: > > >> > Thanks for the answer. > > >> > > > >> > I am stuck with an existing table definition, in a schema, that > > I > > >> cannot > > >> > modify at this point due to being in production. > > >> > > > >> > So, this is the best method I can come up with. > > >> > > > >> > also....now you have tweaked my curiosity. In addition to the > > advice > > >> > I > > >> > am seeking, it I could alter the table definition to use some form > > of > > >> > auto-increment, how would I retrieve the generated value in iBatis? > > >> > > > >> > So.....I now have two questions. > > >> > > > >> > thanks, > > >> > Ediwn > > >> > > > >> > ----- Original Message ----- > > >> > From: "Larry Meadors" < lmeadors@apache.org> > > >> > To: < user-java@ibatis.apache.org> > > >> > Sent: Friday, September 15, 2006 10:14 AM > > >> > Subject: Re: finding unique keys > > >> > > > >> > > > >> > > One question: Why not just let the database do it and have it > > tell > > >> > > you > > >> > > the generated key? > > >> > > > > >> > > Larry > > >> > > > > >> > > > > >> > > On 9/15/06, Edwin Lukaweski < serjann@rogers.com> wrote: > > >> > >> > > >> > >> > > >> > >> Hi: > > >> > >> > > >> > >> I have a situation for which I need some advice while using > > >> > >> iBatis. > > >> > >> > > >> > >> What I would like to do is: > > >> > >> > > >> > >> 1) generate a unique UUID style value in my Java program > > >> > >> > > >> > >> 2) lock a table, say tabxx > > >> > >> > > >> > >> 3) SELECT on the table to see if the UUID exists as a key > > >> > >> > > >> > >> 4) if so, add 1 to the key and go to step 3 > > >> > >> > > >> > >> 5) if the key does NOT exist, insert the record > > >> > >> > > >> > >> 6) unlock the table > > >> > >> > > >> > >> Can anyone get me advice as to how to do this with iBatis? > > >> > >> > > >> > >> Thanks, very musch, in advance > > >> > >> > > >> > >> Edwin > > >> > >> > > >> > > > > >> > > > >> > > > >> > > > >> > > >> > > > > > > > > > > ------=_Part_42660_517316.1158342740786 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Call the getErrorCode() method on the exception.  This will be -803 for duplicate keys on DB2.
 
Jeff Butler

 
On 9/15/06, Edwin Lukaweski <serjann@rogers.com> wrote:
Great!
 
    Now....I guess when the insert fails, via iBatis and an SQL_Map, I will get an SQLExceptiion. Is there an easy way to find the -803?
 
    I want to intercept it and re-throw only non 803 exceptions.
 
thanks,
Edwin
 
----- Original Message -----
Sent: Friday, September 15, 2006 12:08 PM
Subject: Re: finding unique keys

 
I just looked to see how Spring does it with DB2.  They do look for -803 as the error code from the SqlException.  So I think your plan sounds good.
 
Jeff Butler


 
On 9/15/06, Edwin Lukaweski <serjann@rogers.com > wrote:
Thanks for all the feed back!!!

Some answers:

   1) I am using DB2 for z/OS version 7

   2) Yes, UUID's are by definition 'universally unique', excpept whe two
are gengerated, say by two different JVMs,  within the same millisecond.
The, they turn out to be duplicates.

   3) I like the idea of doing an optimistic INSERT. I have always had
trouble figuring which sql code to look for that represents this situation.
As best I can tell, it is SQLCODE 803. So, if I see this code in an
exception, loop again, otherwise throw a real sqlexception.

What do you think?

thanks,
Edwin

----- Original Message -----
From: "Larry Meadors" < lmeadors@apache.org>
To: < user-java@ibatis.apache.org>
Sent: Friday, September 15, 2006 11:03 AM
Subject: Re: finding unique keys


> +1 ;-)
>
> Larry
>
>
> On 9/15/06, Jeff Butler < jeffgbutler@gmail.com> wrote:
>>
>> Some thoughts...
>>
>> 1. Aren't UUID's "universally unique" by definition?  Are you sure you
>> need
>> to do this?
>>
>> 2. Table locking is almost always a bad idea.  I had a similar situation
>> to
>> yours in that past, and decided to take the optomistic route, meaning...
>>
>> - generate the key
>> - insert the record
>> - if the insert blows because of a duplicate key, then generate a new key
>> and try again
>>
>> Since the likelihood of generating a duplicate UUID should be very low,
>> this
>> approcah will work in the vast majority of cases.
>>
>>
>> Jeff Butler
>>
>>
>>
>>
>> On 9/15/06, Edwin Lukaweski < serjann@rogers.com> wrote:
>> > Thanks for the answer.
>> >
>> >    I am stuck with an existing table definition, in a schema, that I
>> cannot
>> > modify at this point due to being in production.
>> >
>> >    So, this is the best method I can come up with.
>> >
>> >    also....now you have tweaked my curiosity. In addition to the advice
>> > I
>> > am seeking, it I could alter the table definition to use some form of
>> > auto-increment, how would I retrieve the generated value in iBatis?
>> >
>> >    So.....I now have two questions.
>> >
>> > thanks,
>> > Ediwn
>> >
>> > ----- Original Message -----
>> > From: "Larry Meadors" < lmeadors@apache.org>
>> > To: < user-java@ibatis.apache.org>
>> > Sent: Friday, September 15, 2006 10:14 AM
>> > Subject: Re: finding unique keys
>> >
>> >
>> > > One question: Why not just let the database do it and have it tell
>> > > you
>> > > the generated key?
>> > >
>> > > Larry
>> > >
>> > >
>> > > On 9/15/06, Edwin Lukaweski < serjann@rogers.com> wrote:
>> > >>
>> > >>
>> > >> Hi:
>> > >>
>> > >>     I have a situation for which I need some advice while using
>> > >> iBatis.
>> > >>
>> > >>     What I would like to do is:
>> > >>
>> > >>     1) generate a unique UUID style value in my Java program
>> > >>
>> > >>     2) lock a table, say tabxx
>> > >>
>> > >>     3) SELECT on the table to see if the UUID exists as a key
>> > >>
>> > >>     4) if so, add 1 to the key and go to step 3
>> > >>
>> > >>     5) if the key does NOT exist, insert the record
>> > >>
>> > >>     6) unlock the table
>> > >>
>> > >>     Can anyone get me advice as to how to do this with iBatis?
>> > >>
>> > >> Thanks, very musch, in advance
>> > >>
>> > >> Edwin
>> > >>
>> > >
>> >
>> >
>> >
>>
>>
>




------=_Part_42660_517316.1158342740786--