Eclipse Community Forums: EclipseLink » Database generated primary key

Help

Home

Home » Eclipse Projects » EclipseLink » Database generated primary key - problems with insert

Show: Today's Messages :: Show Polls :: Message Navigator

Database generated primary key - problems with insert [message #379887]

Sat, 23 August 2008 18:57

Bill Blalock
Messages: 118
Registered: July 2009
Location: Alabama

Senior Member

I am working with a database where primary key (INTEGER) is generated by
the database.

The DDL for the field of that table is

xfrCtrlKey FOR COLUMN XFRKY INTEGER
GENERATED ALWAYS AS IDENTITY
(START WITH 5 INCREMENT BY 5),

The server rejects the SQL executed by DatabaseSession.insertObject().
The error returned is:

Internal Exception: java.sql.SQLSyntaxErrorException: [SQL0798] Value
cannot be specified for GENERATED ALWAYS column XFRCTRLKEY.

How can I get around this?

I tried several methods to exclude XFRCTRLKEY from the INSERT SQL
- custom SQL in Workbench
-
session.getDescriptor(Xfrctl00.class).getQueryManager().setI nsertSQLString
- create an InsertObjectQuery, setHintString(), then execute

These would all change the INSERT portion of the SQL but
session.insertObject() and session.executeQuery() would still put the
value of the primary key into the parameter list

INSERT IGNORE INTO .... VALUES(? (xfrCtrlKey) ? ... ?)

I finally got around the problem with

session.getDescriptor(Xfrctl00.class).getQueryManager().setI nsertSQLString()
and building the full INSERT SQL without the primary key.

Not a good solution!

I spend the better part of a day on this. In my search I ran across a
hint that Eclipselink identy annotators could be used for this situation.

?? Can Ecliselink annotators be used to prevent the database insert from
including the primary key in this situation? If so, how?????

Does anyone have any other ideas?

Thanks for your time.

Bill Blalock

Report message to a moderator

Re: Database generated primary key - problems with insert [message #379890 is a reply to message #379887]

Sun, 24 August 2008 07:46

Doug Clarke
Messages: 155
Registered: July 2009

Senior Member

Bill,

The database platform dictates how primary key sequencing is performed. If
the platform supports identity sequencing where the value is generated by
the database then the PK value will not be included in the INSERT
statement but will be read back into the application after the INSERT.

The DB2 platform does support identity sequencing and the value is read
back in using "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1", as
defined in DB2Platform.buildSelectQueryForIdentity().

How are you configuring your entity to have identity sequencing used?

Doug

Report message to a moderator

Re: Database generated primary key - problems with insert [message #379891 is a reply to message #379890]

Sun, 24 August 2008 12:08

Bill Blalock
Messages: 118
Registered: July 2009
Location: Alabama

Senior Member

Thanks Doug:

The pointer helped get me looking in the right place. I've learned a good
bit but I still haven't go it.

> The DB2 platform does support identity sequencing and the value is read back
> in using "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1", as defined in
> DB2Platform.buildSelectQueryForIdentity().

> How are you configuring your entity to have identity sequencing used?

This is where I am going wrong! I need some help doing this. Some
background first.

Using Eclipselink workbench with Eclipse 3.3 IDE, DB2Platform and jt0pen
6.1 toolkit JDBC 4.0 driver.

The project has four descriptors for four tables, two of the tables are in
one database and use generated primary keys (IDENTITYS).

The other two tables are in different databases, they are logs and do not
have any sort of key (to satisfy Eclipselink workbench I specified the
timestamp keys in these two tables as primary keys). They will be
accessed with ReadAllQuery.

After I looked at the documentation for "identity sequencing" it seems
that "native sequencing" needs to be specified at session level. Problem,
that selection is not available in the Project or Session.

Is that setting only valid for Oricle? Seems, so. After further study I
came across this page which indicates that "Native Sequencing with a
Non-Oricle Database Platform (sequence type) is only done through Java.
http://wiki.eclipse.org/Introduction_to_Relational_Projects_ %28ELUG%29#Sequencing_in_Relational_Projects

After studying these sections of the documentation
http://wiki.eclipse.org/Introduction_to_Relational_Projects_ %28ELUG%29#Default_Sequencing
http://wiki.eclipse.org/Configuring_a_Database_Login_%28ELUG %29#Using_the_Platform_Default_Sequence
http://wiki.eclipse.org/Configuring_a_Relational_Descriptor_ %28ELUG%29#Configuring_the_Platform_Default_Sequence

I concluded that i needed to associate a noneexistent sequence
(XFRCTRL_SEQ) with the descriptor so the runtime will create a
DefaultSequence using the IDENTIFY field of the database.

session.getDescriptor(Xfrctl00.class).setSequenceNumberField Name( "XFRCTRLKEY");
// primary key field

session.getDescriptor(Xfrctl00.class).setSequenceNumberName( "XFRCTRL_SEQ");

That caused session.insertObject() to throw an error.

Oh boo! What am I doing wrong here? Thanks for the pointer but I need a
little more help!

Regards
Bill Blalock

Exception:

java.lang.NullPointerException
at
org.eclipse.persistence.internal.queries.DatasourceCallQuery Mechanism.insertObject(DatasourceCallQueryMechanism.java:300 )
at
org.eclipse.persistence.internal.queries.StatementQueryMecha nism.insertObject(StatementQueryMechanism.java:163)
at
org.eclipse.persistence.internal.queries.StatementQueryMecha nism.insertObject(StatementQueryMechanism.java:178)
at
org.eclipse.persistence.internal.queries.DatabaseQueryMechan ism.insertObjectForWrite(DatabaseQueryMechanism.java:465)
at
org.eclipse.persistence.queries.InsertObjectQuery.executeCom mit(InsertObjectQuery.java:80)
at
org.eclipse.persistence.internal.queries.DatabaseQueryMechan ism.executeWrite(DatabaseQueryMechanism.java:243)
at
org.eclipse.persistence.queries.WriteObjectQuery.executeData baseQuery(WriteObjectQuery.java:60)
at
org.eclipse.persistence.queries.DatabaseQuery.execute(Databa seQuery.java:666)
at
org.eclipse.persistence.internal.sessions.AbstractSession.in ternalExecuteQuery(AbstractSession.java:2207)
at
org.eclipse.persistence.internal.sessions.AbstractSession.ex ecuteQuery(AbstractSession.java:1178)
at
org.eclipse.persistence.internal.sessions.AbstractSession.ex ecuteQuery(AbstractSession.java:1162)
at
org.eclipse.persistence.internal.sessions.AbstractSession.ex ecuteQuery(AbstractSession.java:1108)
at
org.eclipse.persistence.internal.sessions.AbstractSession.in sertObject(AbstractSession.java:2196)
at eclipselinkrcp1.Application.start(Application.java:87)
at
org.eclipse.equinox.internal.app.EclipseAppHandle.run(Eclips eAppHandle.java:169)
at
org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher .runApplication(EclipseAppLauncher.java:106)
at
org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher .start(EclipseAppLauncher.java:76)
at
org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseS tarter.java:363)
at
org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseS tarter.java:176)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAcce ssorImpl.java:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMe thodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java: 508)
at org.eclipse.equinox.launcher.Main.basicRun(Main.java:447)
at org.eclipse.equinox.launcher.Main.run(Main.java:1173)
at org.eclipse.equinox.launcher.Main.main(Main.java:1148)

Report message to a moderator

Re: Database generated primary key - problems with insert [message #379892 is a reply to message #379890]

Sun, 24 August 2008 14:04

Bill Blalock
Messages: 118
Registered: July 2009
Location: Alabama

Senior Member

Doug Clarke wrote:

> Bill,

> The database platform dictates how primary key sequencing is performed. If
> the platform supports identity sequencing where the value is generated by
> the database then the PK value will not be included in the INSERT
> statement but will be read back into the application after the INSERT.

> The DB2 platform does support identity sequencing and the value is read
> back in using "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1", as
> defined in DB2Platform.buildSelectQueryForIdentity().

> How are you configuring your entity to have identity sequencing used?

> Doug

Doug:

I tried this to configure the identity sequence:

session.getLogin().addSequence(new TableSequence("XFRCTRL_SEQ"));
session.getDescriptor(Xfrctl00.class).setSequenceNumberField Name( "XFRCTRLKEY");
// primary key field
session.getDescriptor(Xfrctl00.class).setSequenceNumberName( "XFRCTRL_SEQ");

(see
http://wiki.eclipse.org/Configuring_a_Relational_Descriptor_ %28ELUG%29#Configuring_a_Sequence_by_Name
)

Failed again. Drat.

the error is at
org.eclipse.persistence.internal.queries.DatasourceCallQuery Mechanism.insertObject(DatasourceCallQueryMechanism.java:300 )

I ran it under debug and the problem is the DatabaseSessionImpl class is
returning a SequenceManger whose getSequenceing() method is returning null.

Looks like I still haven't got the sequence set up right.

Any thoughts???

Thanks
Bill Blalock

java.lang.NullPointerException
at
org.eclipse.persistence.internal.queries.DatasourceCallQuery Mechanism.insertObject(DatasourceCallQueryMechanism.java:300 )
at
org.eclipse.persistence.internal.queries.StatementQueryMecha nism.insertObject(StatementQueryMechanism.java:163)
at
org.eclipse.persistence.internal.queries.StatementQueryMecha nism.insertObject(StatementQueryMechanism.java:178)
at
org.eclipse.persistence.internal.queries.DatabaseQueryMechan ism.insertObjectForWrite(DatabaseQueryMechanism.java:465)
at
org.eclipse.persistence.queries.InsertObjectQuery.executeCom mit(InsertObjectQuery.java:80)
at
org.eclipse.persistence.internal.queries.DatabaseQueryMechan ism.executeWrite(DatabaseQueryMechanism.java:243)
at
org.eclipse.persistence.queries.WriteObjectQuery.executeData baseQuery(WriteObjectQuery.java:60)
at
org.eclipse.persistence.queries.DatabaseQuery.execute(Databa seQuery.java:666)
at
org.eclipse.persistence.internal.sessions.AbstractSession.in ternalExecuteQuery(AbstractSession.java:2207)
at
org.eclipse.persistence.internal.sessions.AbstractSession.ex ecuteQuery(AbstractSession.java:1178)
at
org.eclipse.persistence.internal.sessions.AbstractSession.ex ecuteQuery(AbstractSession.java:1162)
at
org.eclipse.persistence.internal.sessions.AbstractSession.ex ecuteQuery(AbstractSession.java:1108)
at
org.eclipse.persistence.internal.sessions.AbstractSession.in sertObject(AbstractSession.java:2196)
at eclipselinkrcp1.Application.start(Application.java:90)
at
org.eclipse.equinox.internal.app.EclipseAppHandle.run(Eclips eAppHandle.java:169)
at
org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher .runApplication(EclipseAppLauncher.java:106)
at
org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher .start(EclipseAppLauncher.java:76)
at
org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseS tarter.java:363)
at
org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseS tarter.java:176)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAcce ssorImpl.java:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMe thodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java: 508)
at org.eclipse.equinox.launcher.Main.basicRun(Main.java:447)
at org.eclipse.equinox.launcher.Main.run(Main.java:1173)
at org.eclipse.equinox.launcher.Main.main(Main.java:1148)

Report message to a moderator

Re: Database generated primary key - problems with insert [message #379893 is a reply to message #379890]

Sun, 24 August 2008 14:40

Bill Blalock
Messages: 118
Registered: July 2009
Location: Alabama

Senior Member

Doug:

I have tried also sequencing at the descriptor level using the Workbench
see
http://wiki.eclipse.org/Configuring_a_Relational_Descriptor_ %28ELUG%29#How_to_Configure_Sequencing_at_the_Descriptor_Lev el_Using_Workbench

Checked "Use sequencing"

Name: SEQ
Table: XFRCTL00
Field: XFRCTRLKEY

No joy, still same error, INSERT includes XFRCTRLKEY field.

When viewing the table XFRCTL00 in the Workbench navigator (database) the
row for column XFRCTRLKEY is checked for "primary key" and "unique" but
has no value in the IDENTITY cell?

Is that correct? I guess so since this is not a Sybase, SQL Server or
Informix database.

Thanks

Report message to a moderator

Re: Database generated primary key - problems with insert [message #379896 is a reply to message #379893]

Mon, 25 August 2008 10:22

Doug Clarke
Messages: 155
Registered: July 2009

Senior Member

Bill,

Just to avoid any confusion I wanted to clarify your usage. Most new
projects requiring ORM functionality are using EclipseLink JPA. This uses
annotations and/or JPA features of your IDE. The workbench utility
provided for EclipseLink is to assist customers wishing to use our native
API and metadata. Are you sure this is the approach you wish to take?

Doug

Report message to a moderator

Re: Database generated primary key - problems with insert [message #379897 is a reply to message #379896]

Mon, 25 August 2008 10:54

Bill Blalock
Messages: 118
Registered: July 2009
Location: Alabama

Senior Member

Doug Clarke wrote:

> Bill,

> Just to avoid any confusion I wanted to clarify your usage. Most new
> projects requiring ORM functionality are using EclipseLink JPA. This uses
> annotations and/or JPA features of your IDE. The workbench utility
> provided for EclipseLink is to assist customers wishing to use our native
> API and metadata. Are you sure this is the approach you wish to take?

> Doug
Doug:

My inexperience with JPA is showing here. Previously I used straight JDBC
and the iBatis.

I started reading the Eclipselink User Guide. Most of the articles showed
how to use the Workbench and then Java for relational database projects.
So that was how I got started using the workbench. Later I learned that
that Eclipse 3.4 supported EclipseLink. I decided for now to stick with
the workbench and Eclipse 3.3 so I wouldn't be trying to learn in 3.4
while learning to use Eclipselink.

Should I start over with Eclipse 3.4 and use the annotations and/or JPA
features? I am not adverse to this if it will be "better" in the long run.

Basically I don't know enough to intellegently answer the question "Are
you sure this is the approach you wish to take?" I'd appreciate your
input.

Would you give me a list of the topics in Eclipselink User's Guide I
should read to become familiar with "annotations and/or JPA features of
your IDE" and the order to read them? At least to get started. Part of
my problem was I got lost in the User's Guide.

Thanks
Bill Blalock

Report message to a moderator

Re: Database generated primary key - problems with insert [message #379900 is a reply to message #379896]

Mon, 25 August 2008 19:58

Bill Blalock
Messages: 118
Registered: July 2009
Location: Alabama

Senior Member

Doug Clarke wrote:

> Bill,

> Just to avoid any confusion I wanted to clarify your usage. Most new
> projects requiring ORM functionality are using EclipseLink JPA. This uses
> annotations and/or JPA features of your IDE. The workbench utility
> provided for EclipseLink is to assist customers wishing to use our native
> API and metadata. Are you sure this is the approach you wish to take?

> Doug

Doug:

I am going to try using EclipseLink JPA for this project.

I see now that EclipseLink JPA can be used with Eclipse 3.3, I
misunderstood a post about using EclipseLink with 3.4 and thought that 3.4
was a prerequisit for using EclipseLink. I can see how Dali could make
working with EclispeLink easier.

In the Eclipselink Wiki I still find that the "path" for using Eclipselink
with annotations and/or JPA features crosses the "path" for using
Eclipselink with Workbench and native API frequently, it is easy for a new
comer to become lost. That being said it is great to have so much
documentation -- great job guys.

One suggestion for the Eclipselink Wiki -- please fix the link "JPA
Specification" refer to frequently.

Thank you
Bill Blalock

Report message to a moderator

Previous Topic:	Dynamic Weaving in Eclipse RCP
Next Topic:	Support for DB2 on iSeries?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 24 00:33:53 EDT 2013