Re: EOF inserts PK automatically
Re: EOF inserts PK automatically
- Subject: Re: EOF inserts PK automatically
- From: Kieran Kelleher <email@hidden>
- Date: Sat, 6 Nov 2004 09:44:50 -0500
Sako,
We have a similar situation where "other" applications other than WO interact with the database.
The solution here is to programmatically do inserts following the exact same SQL scheme as WO does.
SELECT PK from eo_pk_table where NAME='tablename' for update;
In code calculate the new pk, myPk = PK+1
Then immediately update the eo_pk_table...
UPDATE eo_pk_table set PK=myPk where NAME='tablename'
Now put myPk in your record and insert it at you leisure since you have already "reserved" this pk for your "other" app.
We wrote a VB class for managing the eo_pk_table from external VB based apps.
With regards Access, you need to prohibit record insert privileges for the user/pwd used for Access ODBC connections.
Also remove auto increment in your database tables .... that feature in the case of WO apps might be useful for initially importing stuff from command line to populate a PK or for tables that are read only entities for WO and being inserted from one external source.
Regards, Kieran
________________________________________________________________
Dev Config = OS X 10.3.5 / Java 1.4.2_05 / WO 5.2.3 / XCode v1.5 / MySQL 4.0.20 / Connector-J 3.0.11
Deploy Config = OS X 10.3.5 Server / Java 1.4.2_05 / WO 5.2.3 / MySQL 4.0.20 / Connector-J 3.0.11
My Blog: http://webobjects.webhop.org/
On Nov 6, 2004, at 7:10 AM, Sako! wrote:
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(null, 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:
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
_______________________________________________
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