Andrei,
This is not how would I read the spec. From a user's perspective AUTO would
be to rely on the persistence provider to do what's needed for the particular
database, without any extra effort from the user.
thanks,
-marina
Andrei Ilitchev wrote:
> GeneratoryType.AUTO causes generation of a sequence defined in a generator
> (SequenceGenerator or TableGenerator) named SEQ_GEN.
> If SEQ_GEN is not defined AUTO generates a TableSequence that uses default
> TopLink values:
> tableName = SEQUENCE;
> pkColumnName = SEQ_NAME;
> valueColumnName = SEQ_COUNT;
> pkColumnValue = SEQ_GEN;
> allocationSize = 50;
>
> So to set AUTO to use customary table:
> @TableGenerator(
> name="SEQ_GEN",
> table=_at_Table(name="MY_SEQUENCE"),
> pkColumnName="MY_SEQ_NAME",
> valueColumnName="MY_SEQ_COUNT",
> pkColumnValue="MY_SEQ"
> allocationSize = 10;
> )
>
> So to set AUTO to use sequence:
> @SequenceGenerator(name="SEQ_GEN", sequenceName="MY_SEQ",
> allocationSize=25)
>
> The errors you got indicate that sequence table doesn't exist.
>
> ----- Original Message -----
> From: "Shelly (Donna) McGowan" <Shelly.McGowan_at_Sun.COM>
> To: "Andrei Ilitchev" <andrei.ilitchev_at_oracle.com>
> Cc: "Ming Zhang" <Ming.Zhang_at_Sun.COM>; "Peter Krogh"
> <peter.krogh_at_oracle.com>; "Mitesh Meswani" <Mitesh.Meswani_at_Sun.COM>; "Mike
> Keith" <michael.keith_at_oracle.com>; <ejb3-toplink-ext_at_Sun.COM>;
> <persistence_at_glassfish.dev.java.net>
> Sent: Thursday, November 03, 2005 3:03 PM
> Subject: Re: Primary Key generation type IDENTITY
>
> >
> > Andrei,
> >
> > While we're on the subject. Can you also define the behavior when
> > GeneratoryType.AUTO is used? From the spec, AUTO indicates the
> > persistence provider will choose the appropriate strategy for a
> > particular database. When running against Oracle DB, the entity fails
> > during persist with the error shown below.
> >
> >
> > Id annotated as follows:
> >
> > @Id(generate = GeneratorType.AUTO)
> > public Integer getId()
> > {
> > return id;
> > }
> > public void setId(Integer id)
> > {
> > this.id= id;
> > }
> >
> >
> > Any special configuration/table creation to be done for AUTO?
> >
> > Shelly
> >
> >
> >
> >
> > persist DataType|#]
> >
> > [#|2005-11-03T09:56:40.969-0500|INFO|sun-appserver-pe9.0|javax.enterprise.system.stream.out|_ThreadID
> > =31;_ThreadName=p: thread-pool-1; w: 6;|
> > [TopLink Warning]: 2005.11.03
> > 09:56:40.927--ClientSession(33495740)--Exception [TOPLINK-4002] (Oracle
> > TopLink Essentials - 10g release 4 (10.1.4.0.0) (Build 051023Dev)):
> > oracle.toplink.essentials.exceptions.DatabaseException
> > Internal Exception: java.sql.SQLException: [Oracle] #21 ORA-00942: table
> > or view does not exist
> > Error Code: 942
> > Call:UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + 50 WHERE SEQ_NAME =
> > 'SEQ_GEN' Query:DataModifyQuery()
> > |#]
> >
> > [#|2005-11-03T09:56:40.974-0500|INFO|sun-appserver-pe9.0|javax.enterprise.system.stream.out|_ThreadID
> > =31;_ThreadName=p: thread-pool-1; w: 6;|Unexpection Exception :Exception
> > [TOPLINK-4002] (Oracle TopLink Essentials - 10g release 4 (10.1.4.0.0)
> > (Build 051023Dev)): oracle.toplink.essentials.exceptions.DatabaseException
> > Internal Exception: java.sql.SQLException: [Oracle] #21 ORA-00942: table
> > or view does not exist
> > Error Code: 942
> > Call:UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + 50 WHERE SEQ_NAME =
> > 'SEQ_GEN'Query:DataModifyQuery()|#]
> >
> >
> >
> > Andrei Ilitchev wrote On 11/03/05 13:43,:
> >>>>Does Toplink support GeneratorType.IDENTITY?
> >>
> >> Yes it does.
> >>
> >>>>What is the expected DDL for the target table for various dbs?
> >>
> >> On Sybase, SqlServer, Informix, MySQL4 the PK is created as Identity:
> >> CREATE TABLE EMPLOYEE (EMP_ID NUMERIC(15) IDENTITY NOT NULL,...
> >>
> >> There is no Identity field notion in Oracle database, so one should use
> >> GeneratorType.SEQUENCE instead.
> >> However TopLink implementation allows using IDENTITY on Oracle too - the
> >> result is Oracle sequence
> >> named SEQ_GEN_SEQUENCE with default (for sequences in TopLink) increment
> >> 50.
> >> If you define sequence generator with this name you can specify another
> >> sequence name and preallocation value, for instance
> >> @SequenceGenerator(name="SEQ_GEN_SEQUENCE", sequenceName="SEQ",
> >> allocationSize=1)
> >> would cause creation of Oracle sequence named SEQ with increment 1.
> >>
> >> Currently TopLink doesn't support GeneratorType.IDENTITY for any other
> >> databases.
> >>
> >> Andrei
> >>
> >> ----- Original Message -----
> >> From: "Ming Zhang" <ming.zhang_at_Sun.COM>
> >> To: "Peter Krogh" <peter.krogh_at_oracle.com>
> >> Cc: "Mitesh Meswani" <mitesh.meswani_at_Sun.COM>; "Mike Keith"
> >> <michael.keith_at_oracle.com>; <ejb3-toplink-ext_at_Sun.COM>;
> >> <persistence_at_glassfish.dev.java.net>
> >> Sent: Wednesday, November 02, 2005 7:16 PM
> >> Subject: Re: Primary Key generation type IDENTITY
> >>
> >>
> >>
> >>>Hi Peter,
> >>>
> >>>Do you have any update for the following 2 questions?
> >>>Thanks,
> >>>Ming
> >>>
> >>>Mitesh Meswani wrote:
> >>>
> >>>
> >>>>Mike,
> >>>>Thanks for correcting. My original question below was not correct.
> >>>>
> >>>>Peter,
> >>>>The correct question this time. Does Toplink support
> >>>>GeneratorType.IDENTITY? What is the expected DDL for the target table
> >>>>for
> >>>>various dbs?
> >>>>
> >>>>Thanks,
> >>>>Mitesh
> >>>>
> >>>>Mike Keith wrote:
> >>>>
> >>>>
> >>>>>Sounds like there is some confusion around this issue. Maybe I can
> >>>>>help.
> >>>>>
> >>>>>- There is no IdentityGenerator in the spec. When the generation
> >>>>>type is set to IDENTITY then it means that an IDENTITY column, such
> >>>>>as is available in SQL Server and Sybase, is used. This is different
> >>>>>from SEQUENCE, which requires a database sequence be set up.
> >>>>>- When IDENTITY is used then the table must be generated to have an
> >>>>>IDENTITY column in it (e.g. in SQL Server it might look something like:
> >>>>>
> >>>>>CREATE TABLE Employee (
> >>>>> id int IDENTITY(10,2),
> >>>>> name varchar(50)
> >>>>>)
> >>>>>
> >>>>>The identifier attribute must be mapped to the identity column.
> >>>>>
> >>>>>-Mike
> >>>>>
> >>>>>
> >>>>>
> >>>>>>-----Original Message-----
> >>>>>>From: Mitesh Meswani [mailto:mitesh.meswani_at_Sun.COM]
> >>>>>>Sent: Thursday, October 20, 2005 1:56 PM
> >>>>>>To: Peter Krogh
> >>>>>>Cc: Ming Zhang; ejb3-toplink-ext_at_Sun.COM;
> >>>>>>persistence_at_glassfish.dev.java.net
> >>>>>>Subject: Re: Primary Key generation type IDENTITY
> >>>>>>
> >>>>>>
> >>>>>>Hi Peter,
> >>>>>>
> >>>>>>I will try to clarify the question on Ming's behalf:
> >>>>>>Does toplink support IdentityGenerator for generated pk values. If
> >>>>>>yes,
> >>>>>>what is the expected DDL for running against various dbs.
> >>>>>>
> >>>>>>Thanks,
> >>>>>>Mitesh
> >>>>>>
> >>>>>>Peter Krogh wrote:
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>>I am not sure that I fully understand your questions, but
> >>>>>>
> >>>>>>let me give
> >>>>>>
> >>>>>>
> >>>>>>>them a shot.
> >>>>>>>
> >>>>>>>1. Are the sequence name "SEQ_GEN_SEQUENCE" and its
> >>>>>>
> >>>>>>increment/initial
> >>>>>>
> >>>>>>
> >>>>>>>values changeable for IDENTITY type PK?
> >>>>>>>I think that this is what the SeqeunceGenerator annotation is for.
> >>>>>>>
> >>>>>>>2. Is there an IdentityGenerator annotation similar to
> >>>>>>>@SequenceGenerator? What's the usage? In case of @SequenceGenerator,
> >>>>>>>the above values can be set in its elements. For example:
> >>>>>>> @SequenceGenerator(name="mySequenceGenerator", sequenceName =
> >>>>>>>"SEQ_NAME", initialValue=1, allocationSize=1)
> >>>>>>>
> >>>>>>>Again, I believe that the correct one to use is the
> >>>>>>>SeqeunceGenerator. The table based sequencing is called
> >>>>>>
> >>>>>>TableGenerator.
> >>>>>>
> >>>>>>
> >>>>>>>The error that you are getting is because the allocation size is set
> >>>>>>>to 50. You must either set the increment by on your sequence to 50,
> >>>>>>>or change the allocation size using SequenceGenerator like you
> >>>>>>>indicated above.
> >>>>>>>
> >>>>>>>-----Original Message-----
> >>>>>>>*From:* Ming Zhang [mailto:ming.zhang_at_Sun.COM]
> >>>>>>>*Sent:* Thursday, October 20, 2005 12:38 PM
> >>>>>>>*To:* Peter Krogh
> >>>>>>>*Cc:* ejb3-toplink-ext_at_Sun.COM
> >>>>>>>*Subject:* Re: Primary Key generation type IDENTITY
> >>>>>>>
> >>>>>>> I an using DDL. Added the "create sequence" in it:
> >>>>>>> create table datatypes(ID INTEGER, shortData smallint, longData
> >>>>>>> number, floatData real,
> >>>>>>> sqldatedata date, utildatedata date, timeStampData timestamp(9),
> >>>>>>> integerData integer,
> >>>>>>> byteData number, booleanData number, characterData char(100),
> >>>>>>> CONSTRAINT DATATYPES_PK PRIMARY KEY (ID));
> >>>>>>>
> >>>>>>> create sequence SEQ_GEN_SEQUENCE START WITH 1 INCREMENT BY 1;
> >>>>>>>
> >>>>>>> Now I got "increment does not match its pre-allocation size":
> >>>>>>> Caused by: Exception [TOPLINK-7027] (Oracle TopLink Essentials -
> >>>>>>> 10g release 4 (10.1.4.0.0) (Build 051010Dev)):
> >>>>>>> oracle.toplink.essentials.exceptions.ValidationException
> >>>>>>> Exception Description: The sequence named [SEQ_GEN_SEQUENCE] is
> >>>>>>> setup incorrectly. Its increment does not match its
> >>>>>>> pre-allocation size.
> >>>>>>> at
> >>>>>>>
> >>>>>>
> >>>>>>oracle.toplink.essentials.exceptions.ValidationException.seque
> >>>>>>nceSetupIncorrectly(ValidationException.java:1200)
> >>>>>>
> >>>>>>
> >>>>>>> at
> >>>>>>>
> >>>>>>
> >>>>>>oracle.toplink.essentials.sequencing.StandardSequence.createVe
> >>>>>>ctor(StandardSequence.java:146)
> >>>>>>
> >>>>>>
> >>>>>>> ...
> >>>>>>>
> >>>>>>> But still have following questions:
> >>>>>>> 1. Are the sequence name "SEQ_GEN_SEQUENCE" and its
> >>>>>>> increment/initial values changeable for IDENTITY type PK?
> >>>>>>> 2. Is there an IdentityGenerator annotation similar to
> >>>>>>> @SequenceGenerator? What's the usage? In case of
> >>>>>>> @SequenceGenerator, the above values can be set in its elements.
> >>>>>>> For example:
> >>>>>>> @SequenceGenerator(name="mySequenceGenerator", sequenceName =
> >>>>>>> "SEQ_NAME", initialValue=1, allocationSize=1)
> >>>>>>>
> >>>>>>> Thanks,
> >>>>>>> Ming
> >>>>>>>
> >>>>>>>
> >>>>>>> Peter Krogh wrote:
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>> It appears that this sequence is not created on the DB.
> >>>>>>>>
> >>>>>>>> How are you creating this? Using the DDL generation?
> >>>>>>>
> >>>>>>Manually?
> >>>>>>
> >>>>>>
> >>>>>>>> Check the DB and see if that sequence is created
> >>>>>>>>
> >>>>>>>> -----Original Message-----
> >>>>>>>> *From:* Ming Zhang [mailto:ming.zhang_at_Sun.COM]
> >>>>>>>> *Sent:* Wednesday, October 19, 2005 8:54 PM
> >>>>>>>> *To:* ejb3-toplink-ext_at_Sun.COM
> >>>>>>>> *Subject:* Primary Key generation type IDENTITY
> >>>>>>>>
> >>>>>>>> Hello TopLink experts,
> >>>>>>>>
> >>>>>>>> I have some general questions regarding the IDENTITY PK
> >>>>>>>> generation type. How is the ID annotated in this
> >>>>>>>
> >>>>>>case and is
> >>>>>>
> >>>>>>
> >>>>>>>> there a IdentityGenerator annotation similar to
> >>>>>>>> @SequenceGenerator annotation? What's the specific
> >>>>>>>
> >>>>>>usage? How
> >>>>>>
> >>>>>>
> >>>>>>>> do we handle these in DDL?
> >>>>>>>>
> >>>>>>>> I tried @Id(generate = GeneratorType.IDENTITY) in
> >>>>>>>
> >>>>>>my apps and
> >>>>>>
> >>>>>>
> >>>>>>>> was able to compile deploy. But got following exception
> >>>>>>>> during runtime:
> >>>>>>>> Caused by: Exception [TOPLINK-4002] (Oracle TopLink
> >>>>>>>> Essentials - 10g release 4 (10.1.4.0.0) (Build 051010Dev)):
> >>>>>>>> oracle.toplink.essentials.exceptions.DatabaseException
> >>>>>>>> Internal Exception: java.sql.SQLException:
> >>>>>>>
> >>>>>>[sunm][Oracle JDBC
> >>>>>>
> >>>>>>
> >>>>>>>> Driver][Oracle]ORA-02289: *sequence does not exist*
> >>>>>>>> Error Code: 2289
> >>>>>>>> Call:SELECT SEQ_GEN_SEQUENCE.NEXTVAL FROM DUAL
> >>>>>>>> Query:ValueReadQuery()
> >>>>>>>> at
> >>>>>>>>
> >>>>>>>
> >>>>>>oracle.toplink.essentials.exceptions.DatabaseException.sqlExce
> >>>>>>ption(DatabaseException.java:310)
> >>>>>>
> >>>>>>
> >>>>>>>> at
> >>>>>>>>
> >>>>>>>
> >>>>>>oracle.toplink.essentials.internal.databaseaccess.DatabaseAcce
> >>>>>>ssor.basicExecuteCall(DatabaseAccessor.java:550)
> >>>>>>
> >>>>>>
> >>>>>>>> at
> >>>>>>>>
> >>>>>>>
> >>>>>>oracle.toplink.essentials.internal.databaseaccess.DatabaseAcce
> >>>>>>ssor.executeCall(DatabaseAccessor.java:436)
> >>>>>>
> >>>>>>
> >>>>>>>> Thanks,
> >>>>>>>> Ming
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>> 6f4mWKmVZd/5GiAuNBf0Rw)--
> >>>>>>>>
> >>>>>>>>
> >>>>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>
> >>
> >>
> >
> >