BorlandTalk.com

Mark A. Deal · Guest

How do you handle Identity columns with SQL Server and ClientDataSets?

It would appear that the identity value is not actually generated until you
ApplyUpdates. Therefore, I am not sure how the links work (or do not) using
detail datasets since no link has been generated until the master is updated
to the physical server.

As an aside, using standard TADOQuery datasets, I have traditionally simply
performed a query to return the maximum value from a table's primary key
field and then assigned that value + 1 to my new record.

Any thoughts on this? Anybody have any convenient ways of handling this?
I'm not having much luck because I get the error, "No corresponding Master
record."

TIA

--
Mark A. Deal
Document & Data Solutions, LLC
http://www.docsol.com
Time Matters AIC
HotDocs Certified Consultant
GhostFill Certified Consultant

Andrew · Guest

"Mark A. Deal" <nospam (AT) idaknow (DOT) com> wrote

Mark A. Deal · Guest

Brian:

A couple of things, and hope you will indulge me.

1. First of all, I want to thank you for all of your help throughout the
years I have been doing this. You and several others have walked me through
some of the most fun learning experiences of my programming life and you
have ALWAYS been there with the answer I was looking for. Thank you, and I
owe you a very nice dinner sometime. I say we all meet in Chicago and go to
Gibson's for dinner. <G>

2. I am now one of 10 moderators for a very active (non Borland) listserve
and it gives me a much greater appreciation for the time you and others put
in to helping so many people. Thank you, and I owe you a drink sometime.
My I recommend the martinis at Gibson's?

3. Believe it or not, I remember you from my early days moving from Paradox
for DOS to my first OPAL application. I can specifically remember one night
I was ready to throw in the towel (as it were) and change career paths.
Right then, on CServe, you and another gentleman named Michael responded
with the explanation I needed to put me over the top. Whether you care or
not, you changed somebody's life you have never met.

Thanks.

(I eventually build an Paradox Windows app that supported 100+ simultaneous
users with 100+ tables with a master table with 30,000 records. Access
sucks.)

I would say, "where do I send the check" but you actually do not appear to
be too "googleable". Sure, you show up one or two places, but you appear to
spend a lot more time helping neophytes like me than you do marketing
yourself. <G>

With all of that said, I'll get off of the couch and ask you:

Okay, how do YOU handle ClientDataSets with AutoInc fields as key fields?
The use of GUIDS almost makes me toss cookies, so I tend to keep my database
layer pretty basic and concentrate my rules in my application which flies in
the face of realistic remote access expectations now that I am working with
Intraweb. I have complete control of the database and applications...what
do you recommend?

How do you earn a living?

And where do I send the check?

Thanks!

--
Mark A. Deal
Document & Data Solutions, LLC
http://www.docsol.com
Time Matters AIC
HotDocs Certified Consultant
GhostFill Certified Consultant

Kevin Frevert · Guest

"Mark A. Deal" <nospam (AT) idaknow (DOT) com> wrote

Del Murray · Guest

Mark,

Kevin is on track. It is not a good idea (in my humble but money making
opinion) to use identity fields directly for keys. If you have a database
disaster, it's a real pain to reload the data and maintain the keys such
that the same old master gets tied back to the same old detail. Consider
that the identity in the master is a "unique key" for that table , but when
that same value is used in the detail for the "foreign key", it is not
unique .... and the detail then has to have its own unique key so that when
updating those rows, the db manager can find the correct row to update,
which is, after all , the purpose of the "unique key" and the reason the
"identity" fields were invented to begin with. So you then have the
"identity " field serving two different , and almost mutually exclusive,
rolls in two tables and the foreign/master keys are a mixed bag also. One
should always try to find some unique values in the master that make it
different from every other master. If there are none, then the master is
redundant (one to many .. not multiple duplicates to many). That combination
is what should also exist in a detail and the two things become a link. Now,
in a highly normalized database (4th degree), the repetition of the keys in
the detail is considered to be unnecessary. The solution to that (if you
want a highly normalized database) is to have a unique single field in a
master (which you must create somehow) and use it to link to the single
foreign key in the detail. By doing this , you have kept the stored data to
a minimum and allow the db manager to access the data in an efficient manner
since if the keys are in an index.

All that hot air being said, what I do is let the db manager create the
single unique key for me when he creates the identity field in the master
(which he will never duplicate and I dont have to cook up some cock-a-mamy
scheme that works for multi-user and has all sorts of locking and re-reading
tricks in it. I then use the identity field as a key by moving it to another
field in the master where it will never be altered upon reload and then
using that field, not the 'identity' field to link to the detail. In the
detail, I have a field of the same name where this foreign key is populated
and the detail has its own identity field. The single draw back to this is
that I must add the master before I add the details meaning that I must do
it in code, not by "magic" via the db manager or the code behind the ADO
object which I have no control over. I use a tADODataset for the master,
batchoptimistic locking and when I call "updatebatch", ADO will cause the
data to be written to the master, then when the db assigns the identity
field, ADO will cause my tADODataset, to be repopulated automatically with
the identityfield because he always want to keep the dataobject and the data
in synch after an update. To create an empty master to add a new record, I
simply do this ...
"select * from myMaster where 1=2". 1 never equals 2 so an empty recordset
is created which knows all of the colums in the table and I am now ready to
start adding fields.

I find that this looks very straight forward in the code, doesn't make me
have to go somewhere else in the program to see how a key is created, gives
me full control of what is happening and when, and runs very very fast, and
on my ISAPI apps with manu hundreds of users, it run like a cat with a can
on its tail.

Now I'll shut up. Hope this helps.

PS .. this works for me, others may have other techniques. Mine is not wrong
... just different .. if there were a right way to do this, there would be
only one computer program in the whole world that did everything for
everyone and perhaps the career change would have been a good thing.

Mark A. Deal · Guest

I'd love to see any demo that you have related to this.

Thanks!

--
Mark A. Deal
Document & Data Solutions, LLC
http://www.docsol.com
Time Matters AIC
HotDocs Certified Consultant
GhostFill Certified Consultant

Kevin Frevert · Guest

I've just uploaded an example (it uses the BDE, but concepts still apply) to
the .attachments.

When I get a chance, I'll write another example using ADO/Clientdatasets.

Good luck,
krf

"Mark A. Deal" <nospam (AT) idaknow (DOT) com> wrote

Kevin Frevert · Guest

"Brian Bushay TeamB" <BBushay (AT) Nmpls (DOT) com> wrote

Kevin Frevert · Guest

I'm not sure what your asking. I don't override identity fields and I can't
think of a situation where I ever would. Backup? Another format?

krf

"Brian Bushay TeamB" <BBushay (AT) Nmpls (DOT) com> wrote

Mark A. Deal · Guest

Thank you, Kevin, the stored procedure is a godsend and brilliant in its
simplicity.

However, I continue to have issues with nested ClientDataSets where I
attempt to update or insert into the MASTER (as in the key to the whole
database) and constantly get exceptions telling me that "no key has been
defined" etc.

I have found that a simple Master == > Detail relationship is pretty easy to
implement with ClientDataSets but impossible to debug when you have a single
master with more than three details. I know how to assign the new inserted
values but still get killed at apparently random places where it works once
and then fails often.

Very frustrating.

If anybody has any examples, articles or ideas about ClientDataSets and
master ==> detail relationships made up of

Master ==> Detail
==> Detail ==> Detail

type relationships then I am all ears. <G>

Thank you, again, to everybody for your input. It is seriously appreciated.

--
Mark A. Deal
Document & Data Solutions, LLC
http://www.docsol.com
Time Matters AIC
HotDocs Certified Consultant
GhostFill Certified Consultant

Kevin Frevert · Guest

"Brian Bushay TeamB" <BBushay (AT) Nmpls (DOT) com> wrote

Kevin Frevert · Guest

Mark,

I still haven't had time to come up with a demo yet, but if I get time
this weekend I'll try to come up with something.

So I'll know how to build the demo, are you wanting to use the
Master->Detail->SubDetail in nested datasets? Linked through a TDataSource?
"Do-it-yourself" through the different change (OnAfter...) events?

krf

"Mark A. Deal" <nospam (AT) idaknow (DOT) com> wrote