From derby-user-return-4150-apmail-db-derby-user-archive=db.apache.org@db.apache.org Tue May 02 00:48:39 2006 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 32133 invoked from network); 2 May 2006 00:48:38 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 2 May 2006 00:48:38 -0000 Received: (qmail 99533 invoked by uid 500); 2 May 2006 00:48:37 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 99477 invoked by uid 500); 2 May 2006 00:48:37 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 99466 invoked by uid 99); 2 May 2006 00:48:37 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 01 May 2006 17:48:37 -0700 X-ASF-Spam-Status: No, hits=1.4 required=10.0 tests=DNS_FROM_RFC_ABUSE,DNS_FROM_RFC_WHOIS,HTML_MESSAGE X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: local policy) Received: from [206.190.39.119] (HELO web50813.mail.yahoo.com) (206.190.39.119) by apache.org (qpsmtpd/0.29) with SMTP; Mon, 01 May 2006 17:48:35 -0700 Received: (qmail 24113 invoked by uid 60001); 2 May 2006 00:48:11 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=ZxlPYInCliDM73VDhnVQG6IHvfFuO+KQCTYWV/LQ+57qREYOTng10U98RCvF5wn7LQme9ZKsIsYSnd92O68rLv0IGWDycci62neSL0aI0O91ggPC9rBruWGmi+NYHyNTfAgJZJpwYynSFw+DrvNdNdzdEJVBnGh5uEYyrmyTa9c= ; Message-ID: <20060502004811.24111.qmail@web50813.mail.yahoo.com> Received: from [84.254.11.33] by web50813.mail.yahoo.com via HTTP; Mon, 01 May 2006 17:48:11 PDT Date: Mon, 1 May 2006 17:48:11 -0700 (PDT) From: Panayiotis Vlissidis Subject: Re: Are Sequences(Generators) supported? To: Derby Discussion , msegel@segel.com In-Reply-To: <200605011450.29326.msegel@segel.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1881324196-1146530891=:92765" Content-Transfer-Encoding: 8bit X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N --0-1881324196-1146530891=:92765 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Well thanks for the reply, although I prefer to use the platform dependent way even if that sounds weird! I've taken care though to abstract all the methods in an interface and implement as needed. So I guess I'll just have to go the way Masoud Kalali suggested if I finally decide to switch to Derby(eventhough I'll have to change a few more lines of code). Thanks everyone. You've been most helpful. Michael Segel wrote: There are other ways of doing this, however each method has their own good points or bad points. One way you can do this is to create a counter table. (This allows you to have multiple counters within a single table.) Then you can write your own procedure that will fetch the value of the counter, increment that value by a step and update that row to reflect the new value.) This is different in that you can create/delete/reset counters as needed. So it can be used for a couple of different types of applications and it does not Note that there are issues like row level locking, etc ... are left to the developer to deal with... ;-) But that will let you have a way to generate a sequence that is platform independent, as long as they allow for stored procedures. Also note that your performance will vary.... On Sunday 30 April 2006 5:14 am, Panayiotis Vlissidis wrote: > Thanks for the quick response!. > > I would rather not create a table as I have a generic code that updates the > database tables(deletes, inserts, modifies tables) that works with the DB's > metadata and a static declaration of all the DB schema that might change as > the application's version changes. I would prefer not to have to declare > the generators as tables as errors might occur(such as forgetting to > declare the generator or deleting by mistake) that would cause the > generators tables to be deleted and the sequence to be lost! > > So is this the only way for creating a generator? No built-in support for > it? Is there any plan for supporting such a feature in the future, and if > so when would that be? > > Thanks again for any responses. > > Masoud Kalali wrote: > > > Yes It is supported , you can build a table with a field that will > automatically increment due to inserting data. > for example : > > create table tinc{ > idenCol integer not null generate always as identity, > } > > also in case that you need sometimes to insert some values for the > identity field you can use : > > create table tinc{ > idenCol integer not null generate by default as identity, > } > > AFAIR , in Interbase we defined a generator and then we used that > generator in a trigger which put a new value for a column , Am i correct ? > it is a long time that i did not used that Interbase/FierBird thingy > > Panayiotis Vlissidis wrote: > > Hello all! > > > > I just found out about derby( through Java DB) and I would like to know > > if the feature of Oracle sequences(or Firebird generators) is supported > > in order to automatically retreive and store a unique number, is > > supported in any way? > > > > Thanks in advance. > > > > Panayiotis Vlissidis --------------------------------- New Yahoo! Messenger with Voice. Call regular phones from your PC and save big. --0-1881324196-1146530891=:92765 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Well thanks for the reply, although I prefer to use the platform dependent way even if that sounds weird! I've taken care though to abstract all the methods in an interface and implement as needed.

So I guess I'll just have to go the way Masoud Kalali suggested if I finally decide to switch to Derby(eventhough I'll have to change a few more lines of code).

Thanks everyone. You've been most helpful.
 
Michael Segel <msegel@segel.com> wrote:
There are other ways of doing this, however each method has their own good
points or bad points.

One way you can do this is to create a counter table.
(This allows you to have multiple counters within a single table.)
Then you can write your own procedure that will fetch the value of the
counter, increment that value by a step and update that row to reflect the
new value.)

This is different in that you can create/delete/reset counters as needed. So
it can be used for a couple of different types of applications and it does
not
Note that there are issues like row level locking, etc ... are left to the
developer to deal with... ;-)

But that will let you have a way to generate a sequence that is platform
independent, as long as they allow for stored procedures.

Also note that your performance will vary....




On Sunday 30 April 2006 5:14 am, Panayiotis Vlissidis wrote:
> Thanks for the quick response!.
>
> I would rather not create a table as I have a generic code that updates the
> database tables(deletes, inserts, modifies tables) that works with the DB's
> metadata and a static declaration of all the DB schema that might change as
> the application's version changes. I would prefer not to have to declare
> the generators as tables as errors might occur(such as forgetting to
> declare the generator or deleting by mistake) that would cause the
> generators tables to be deleted and the sequence to be lost!
>
> So is this the only way for creating a generator? No built-in support for
> it? Is there any plan for supporting such a feature in the future, and if
> so when would that be?
>
> Thanks again for any responses.
>
> Masoud Kalali wrote:
>
>
> Yes It is supported , you can build a table with a field that will
> automatically increment due to inserting data.
> for example :
>
> create table tinc{
> idenCol integer not null generate always as identity,
> }
>
> also in case that you need sometimes to insert some values for the
> identity field you can use :
>
> create table tinc{
> idenCol integer not null generate by default as identity,
> }
>
> AFAIR , in Interbase we defined a generator and then we used that
> generator in a trigger which put a new value for a column , Am i correct ?
> it is a long time that i did not used that Interbase/FierBird thingy
>
> Panayiotis Vlissidis wrote:
> > Hello all!
> >
> > I just found out about derby( through Java DB) and I would like to know
> > if the feature of Oracle sequences(or Firebird generators) is supported
> > in order to automatically retreive and store a unique number, is
> > supported in any way?
> >
> > Thanks in advance.
> >
> > Panayiotis Vlissidis

New Yahoo! Messenger with Voice. Call regular phones from your PC and save big. --0-1881324196-1146530891=:92765--