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)--
>>>>>>>
>>>>>>>
>>>>>>
>>>>
>>>>
>>>>
>>
>
>