Re: EOF inserts PK automatically
Re: EOF inserts PK automatically
- Subject: Re: EOF inserts PK automatically
- From: Mark Morris <email@hidden>
- Date: Sun, 7 Nov 2004 21:19:12 -0600
First. I've never used SQL Server, so there may be a gotcha in here
somewhere.
The problem as I understand it: EOF needs to know the PK upon insert,
but another app is using SQL Server's automatic PK generation.
Here's the idea: You can in a way avoid the problem by keeping the EOF
PK values separated from the SQL Server-generated values. For example
(assuming SQL Server adds 1 to the current max value), set the
EO_PK_TABLE value to the current max value + 1 as usual, but then
create a record with a PK of 1,000,000 higher than the current value.
This way, SQL Server will be creating new PKs in a completely different
range than will EOF. (Obviously make sure the lower value will never
in our lifetime reach the upper. ;-)
A variation would be to have EOF start negative and use increasingly
negative values (-1, -2, -3, etc.). You'd need to use a stored
procedure (EOModeler lets you declare a stored procedure as the source
for the PK). But you wouldn't have to worry about the PKs EVER
converging....
-- Mark
On Nov 7, 2004, at 6:59 PM, Marek Wawrzyczny wrote:
On 06/11/2004, at 23:10, Sako! wrote:
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.
Actually as far as I can tell, the EO_PK_TABLE is not the table that
WebObjects uses to get the next PK. It's easy to test this. On a
development (not your production) database, start up your application,
create some records in a table of your choice (either through another
application or through the database) and don't update the EO_PK_TABLE.
EOF will still generate records with the correct PK. In fact you can
drop the entire EO_PK_TABLE table altogether and WebObjects will still
happily create it and keep on working.
I am not sure what EOF needs EO_PK_TABLE for, but it does not seem to
need it for the generation of PKs. I remember reading on Omnigroup
that EOF always gets the max() value on the PK column prior to
generating the PK. You are always guaranteed to get a unique PK for
your records.
I think what you're asking for is how to stop WebObjects from trying
to generate the PK and make it use the SQL auto-increment feature...
and the answer is, you cannot.
Marek Wawrzyczny
software engineer
-------------------------->
ish group pty ltd
http://www.ish.com.au
7 Darghan St Glebe 2037 Australia
phone +61 2 9660 1400 fax +61 2 9660 7400
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
email@hidden
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