RE: EOF inserts PK automatically
RE: EOF inserts PK automatically
- Subject: RE: EOF inserts PK automatically
- From: "Ruenagel, Frank" <email@hidden>
- Date: Sun, 7 Nov 2004 17:11:51 +0100
There is a feature in SQL-Server which allows the insert in
<IDENTITY>-Columns, its called "SET IDENTITY_INSERT". See
the SQL-Server docs. I quote:
"If the value inserted is larger than the current identity value for the
table, SQL Server automatically uses the new inserted value as the current
identity value"
This could offer a chance.
1. you want to insert a record with EOF
2. Fetch the max(PK) from the identity-colum wich SQL
3. increase it by 1
4. use this value as pk
5. insert the record with the new PK
But there is a danger if (2.) and (5.) are
different transactions. This means: in the little time frame
between (2.) and (5.) your concurring ACCESS may insert another record......
If this happens the insert fails, the exception has to be caught and 2-5 has
to
be performed anew. For 2-4 i think the
databaseContextNewPrimaryKey-Method of
EODatabaseContext.Delegate could be used.
Post it tho the list if you have a better solution. You are not alone with
this problem.
Frank
> -----Original Message-----
> From: Sako! [mailto:email@hidden]
> Sent: Saturday, November 06, 2004 1:11 PM
> To: Chuck Hill
> Cc: email@hidden
> Subject: Re: EOF inserts PK automatically
>
>
> Hello Chuck,
> thank you for answering.
>
> Chuck Hill wrote:
>
> >
> > On Nov 5, 2004, at 1:42 AM, Sako! wrote:
> >
> >> Hello Pierre,
> >> i have MS sql,
> >> what do you mean with "Other applications you have would
> do best to
> >> rely on the same sequence."?
> >> how can i rely on the same sequence?
> >> the real problem is, i create the object in WO add it to
> the EC, at
> >> this time the Object has correct PK(hopfullely).
> >
> > No. At this point the object has an internal, temporary
> EOGlobalID.
> > The PK is null.
> >
> In fact temporary EOGlobalID or real PK, its not very
> important for me.
> what i need is that the EOF writes the correct PK in the DB.
>
> >
> >> before i call EC.SaveChanges(). another Application has already
> >> inserted a row in the Table with the same PK. after that
> WO tries to
> >> insert row with invalid PK.
> >> this problem is also explaind in the WO documentation
> unfortunately
> >> without really good solution :(
> >>
> > I don't know where you got that, its wrong. The real PK is
> generated
> > during save changes as part of a transaction. There is no
> danger of
> > duplication. PK numbers can be skipped but never duplicated.
> >
> I think i miss something or something is here wrong.
> When i don t set the PK as a ClassProperty, EOF creates its own table
> "EO_PK_TABLE" in the database, and saves the last PK in the
> column pk.
> Another applications like ACCESS (what i really have as another
> application) for example do not use this technique.
>
> let me give an example:
> 1. EOF adds a row with the PK "27", and CHANGES the pk
> column in the
> table EO_PK_TABLE to "27"
> 2. ACCESS adds a row with the PK "28".
> 3. EOF does not mention what happens to the table and pk.
> tries to write
> with a PK "28" <== which is already used.
>
> Mr. Chuck, i know you are experienced developer here, but I
> am sure what
> I am telling you and its 100% correct.
>
> I cant change ACCESS now. its expensive and complicated.
> WO must be flexible with the database and ACCESS.
> So I thought I use the database itself.
> There is a feature in MS-SQL <IDENTITY>, what ALWAYS allows
> the database
> to create the PK automatically.
> The problem is, this works only if all applications never
> try to write
> in the PK column.
> Its possible and easy to make ACCESS stop write in the PK column. And
> now my question: "how can i make EOF stop inserting the PK?"
>
> This PK conflict is even in the WO books a big problem, even
> Apple has
> no good suggestion for that.
> If you have any suggestions for me please let me know.
>
> > In EOF we trust.
> >
> > Chuck
> >
> Regards.
> Sarkis.
>
> >>
> >>
> >> ----- Original Message -----
> >> From: email@hidden
> >> To: email@hidden
> >> Sent: Friday, November 05, 2004 10:06 AM
> >> Subject: RE: EOF inserts PK automatically
> >>
> >> Sako!
> >>
> >> The exact behavior depends on the database specific
> adaptor plug-in.
> >> The Oracle adaptor, as many others, gets its primary keys from a
> >> sequence. Thus no two WebObjects applications can get the same
> >> primary key. Other applications you have would do best to rely on
> >> the same sequence.
> >>
> >> Pierre
> >> -----Original Message-----
> >> From: Sako! [mailto:email@hidden]
> >> Sent: Friday, November 05, 2004 9:41 AM
> >> To: Pierre Bernard
> >> Subject: Re: EOF inserts PK automatically
> >>
> >> thank you for answering.
> >>
> >> i understand what you explained, but this ideal situation
> will work
> >> fine if only WO has access to the DataBase.
> >> i have problem with it.
> >> an Example:
> >> 1. WO creates an PK.
> >> 2. another Application WRITES in the DataBase.
> >> 3. WO writes << an error happens. cos the PK is already used.
> >>
> >> so i thought. if i use the feature from the DB. i will solve this
> >> problem.
> >>
> >> acording to "That said, WebObjects relies on the database for
> >> providing the primary key it used. It just ask for it
> earlier than
> >> what you are aiming for."
> >> i made some tests. the result was something diffrent.
> >> i can with 100% say WO doesnt relies on the DB always. WO
> Saves the
> >> last PK in the applications(i suppose).
> >>
> >> regards.
> >>
> >> Sako
> >> ----- Original Message -----
> >> From: email@hidden
> >> To: email@hidden ; email@hidden
> >> Sent: Wednesday, November 03, 2004 5:09 PM
> >> Subject: RE: EOF inserts PK automatically
> >>
> >> Sako
> >>
> >> That would be aganst the very nature of WebObjects. EOF
> references
> >> objects internally using a global ID which is based on the primary
> >> key of the database row matching the object. If you let your
> >> database create the primary key during the transaction
> which inserts
> >> the new row, WebObjects would not know the generated key.
> Moreover
> >> with the primary key generated only this late in the process,
> >> WebObjects would not know the value of the foreign key to use by
> >> other rows to reference the newly created one.
> >>
> >> That said, WebObjects relies on the database for providing the
> >> primary key it used. It just ask for it earlier than what you are
> >> aiming for.
> >>
> >> Pierre
> >> -----Original Message-----
> >> From:
> webobjects-dev-bounces+pierre.bernard=email@hidden
> >> [mailto:webobjects-dev-
> >> bounces+pierre.bernard=email@hidden]On Behalf Of Sako!
> >> Sent: Wednesday, November 03, 2004 4:23 PM
> >> To: WebObjectsDev
> >> Subject: EOF inserts PK automatically
> >>
> >> Hello,
> >>
> >> i create an object and insert it in the ec, and fire save changes
> >> with this simple code
> >> my table has {(id as PK ),(name)}
> >>
> >>
> >> EOClassDescription classDescriptionAFDetails =
> >> EOClassDescription.classDescriptionForEntityName("Test2");
> >>
> >>
> >> EOGenericRecord newTest2Object = (EOGenericRecord)
> >>
> classDescriptionAFDetails.createInstanceWithEditingContext(nul
> l, null);
> >>
> >>
> >> newTest2Object.takeValueForKey("name ", "name");
> >>
> >>
> >> session.defaultEditingContext().insertObject(newTest2Object);
> >>
> >>
> >> session.defaultEditingContext().saveChanges();
> >>
> >> EOF adds the PK automatically.
> >>
> >> i use MS-SQL server and it supports adding the PK also.
> this mean i
> >> just need to add the name with sql like this
> >>
> >> INSERT INTO test2 (name) VALUES ("my new name");
> >>
> >> what i look for is, i would like to use the feature from
> the db. to
> >> do that i have to prvent EOF to add the PK automatically.
> >>
> >> do i have chance to do that?
> >>
> >> thanks.
> >>
> >> sako
> >>
> >>
> >>
> **********************************************************************
> >> This email and any files transmitted with it are intended
> solely for
> >> the use of the individual or entity to whom they are addressed.
> >> If you have received this email in error please notify the sender
> >> of this message. (email@hidden)
> >> This email message has been checked for the presence of computer
> >> viruses; however this protection does not ensure this message is
> >> virus free.
> >> Banque centrale du Luxembourg; Tel ++352-4774-1; http://www.bcl.lu
> >>
> **********************************************************************
> >>
> >>
> >>
> **********************************************************************
> >> This email and any files transmitted with it are intended
> solely for
> >> the use of the individual or entity to whom they are addressed.
> >> If you have received this email in error please notify the sender
> >> of this message. (email@hidden)
> >> This email message has been checked for the presence of computer
> >> viruses; however this protection does not ensure this message is
> >> virus free.
> >> Banque centrale du Luxembourg; Tel ++352-4774-1; http://www.bcl.lu
> >>
> **********************************************************************
> >> _______________________________________________
> >> Do not post admin requests to the list. They will be ignored.
> >> Webobjects-dev mailing list (email@hidden)
> >> Help/Unsubscribe/Update your Subscription:
> >>
>> village.net
>>
>> This email sent to email@hidden
>
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
.de
This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden