Developer Forum

Okay, I'm stumped. I'm trying to create a 'briefcase' style form with
a master-detail. I'm using ClientDataSets and dbExpress in Win32
Delphi.

I've read Cary Jensen's articles online, but they don't really apply to
what I'm doing, because the source of the data is NOT a SQL Select
query, nor is it a TTable, but rather a stored proc.

So if you have a master stored proc that retrieves a row, say by some
field ID, and you get the details through a stored proc as well by
passing in that same ID to get the child data, how do you create a
master-detail nested client dataset relationship on the client with
cached updates that you can post back using OTHER stored procedures (or
SQL INSERT/UPDATE , maybe).

I've tried hooking up the 2 CDS to the stored procs via provider,
linked the CDS' on MasterSource and MasterField, and then created a
third provider and CDS to point to the master CDS. When I run I get
indexing errors, or I get an error that says the master's stored proc
doesn't know the type of my parameter, and it gives me a parameter name
that's not the name of its parameter as stored, but rather the name of
the linked child field (which is, of course, NOT the parameter).

If someone can create an example of an updateable master-detail
(hopefully nested) CDS system for even a single record and detail that
allows me to add new master-details in a single transaction, I'd like
to see it.

Thanks

Randy
--

It's hard to help you since you don't mention which DB you're using or
what the errors are. In InterBase procs which return a result set look
very much like tables in SQL and the way of using them w/ CDS is
essentially identical to using tables except that you put the master ID
in a proc argument rather than a where clause. Or no argument at all if
you're using MasterSource / MasterField.

Can you explain more about the error you're getting?

--
Craig Stuntz [TeamB] Vertex Systems Corp. Columbus, OH
Delphi/InterBase Weblog : http://www.yqcomputer.com/
Borland newsgroup denizen Sergio Gonzez has a new CD of
Irish music out, and it's good: http://www.yqcomputer.com/

Sorry, let me elaborate a bit more...

First , the database is MS SQL Server 2000. I've been experimenting
with both dbExpress and BDE drivers (don't wanna go ADO because of the
.net factor).

Anyway...

I created an IndexDef for the detail client data set. That got rid of
the index error issues. The problem is that when I try to open the
master and detail CDS, I can't seem to get it to send the parameter up
to the source stored proc that's supposed to populate the detail. It
keeps saying I didn't supply it. I'm thinking I need to hook an event
on the detail CDS. I tried deliberately assigning it in the
BeforeOpen, but something must be happening after that because it's
getting whacked or otherwise not being sent up through the
DataSetProvider to the StoredProc object.

Without supplying some kind of visual diagram or sending a .zip out
with the data module in it, it's hard to describe the pipeline, but
basically I'd like to be able to do a completely disconnected
master/detail with updates where all the database side work is done by
stored procs, not Query objects or Table objects. I'm hopeful that's
enough to run with.

Randy

No, you don't, but you're right about needing an index / index name.
But could you give the precise error message, please?

--
Craig Stuntz [TeamB] Vertex Systems Corp. Columbus, OH
Delphi/InterBase Weblog : http://www.yqcomputer.com/
All the great TeamB service you've come to expect plus (New!)
Irish Tin Whistle tips: http://www.yqcomputer.com/

---------------------------
De *** Exception Notification
---------------------------
Project TestUpdateObject.exe raised exception class EDatabaseError with
message 'Database Server Error: SQL State: 42000, SQL Error Code: 201
Procedure 'castCustomerRequestDetailGet' expects parameter
'@iRequestId', which was not supplied.
'.
---------------------------
Break Continue Help
---------------------------
--

> You should link with master detail relation not ClientDataSets but

Um, yeah but I'm using TSQLStoredProc in this case, NOT TSQLDataSet.
So the parameters are not query parameters that can just be parsed,
they are stored proc parameters. The server side work could come from
anywhere.

Randy


Haven't figured out how to do that with stored procs...

I don't agree with that as blanket advice.

It can be *painfully* slow if you have a large number of master
records and a small number of detail records per master.

In fact, the only way it will give an overall performance increase is
with poFetchDetailsOnDemand.

So it really depends on what you're doing.

--
Craig Stuntz [TeamB] Vertex Systems Corp. Columbus, OH
Delphi/InterBase Weblog : http://www.yqcomputer.com/
Useful articles about InterBase development:
http://www.yqcomputer.com/

MasterField / MasterTable are not for parameterized queries.

Use MF/MT when the detail dataset will contain *all* records and you
want to display a filtered view in the detail CDS based on the master
CDS cursor.

Use linked master and detail (non-Client) datasets and DataSetField on
the CDS tier when the detail SQL is parameterized.

--
Craig Stuntz [TeamB] Vertex Systems Corp. Columbus, OH
Delphi/InterBase Weblog : http://www.yqcomputer.com/
IB 6 versions prior to 6.0.1.6 are pre-release and may corrupt
your DBs! Open Edition users, get 6.0.1.6 from http://www.yqcomputer.com/

"Randy Magruder" < XXXX@XXXXX.COM > ???????/???????? ? ????????


You should link with master detail relation not ClientDataSets but
TSQLDataSets. So the full scenario will look like this:

1. Drop master TSQLDataSet and detail TSQLDataSet

2. Link them with master detail relation

3. Attach TDataSeProvider to master TSQLDataSet

4. Drop TClientDataSet (for the master) and link it to provider

5. Append all fields to master TClientDataSet, and you will see among
them TDataSetField which corresponds to detail TSQLDataSet

6. Drop another TClientDataSet (for the detail) and assign its
DataSetField property to TDataSetField of master TClientDataSet

Craig,

How do you link two non-CDS Stored Procs (except by manual coding and
synchronization)

Randy
--

You either:

1) Make the detail proc return all records for all masters and link the
master and detail CDSs using MF/MT, or
2) Make the detail proc return records for a single master record based
on a param and pass the param using TDataSource to connect the two
source SQL datasets. You then use DataSetField to connect the detail
CDS.

--
Craig Stuntz [TeamB] Vertex Systems Corp. Columbus, OH
Delphi/InterBase Weblog : http://www.yqcomputer.com/
Everything You Need to Know About InterBase Character Sets:
http://www.yqcomputer.com/

Yes, and both methods work for that (provided you don't use
poFetchDetailsOnDemand when using TDataSource to link). It's just that
the one you're suggesting is much slower in many cases, which is why I
disagreed with your blanket advice.

--
Craig Stuntz [TeamB] Vertex Systems Corp. Columbus, OH
Delphi/InterBase Weblog : http://www.yqcomputer.com/
Please read and follow Borland's rules for the user of their
server: http://www.yqcomputer.com/

"Randy Magruder" < XXXX@XXXXX.COM >

> Um, yeah but I'm using TSQLStoredProc in this case, NOT TSQLDataSet>
> So the parameters are not query parameters that can just be parsed>
> they are stored proc parameters. The server side work could come fro>
> anywhere>
> Rand>
>> >> 2. Link them with master detail relatio>
> Haven't figured out how to do that with stored procs..>
>> >> 3. Attach TDataSeProvider to master TSQLDataSe>> >>> >> 4. Drop TClientDataSet (for the master) and link it to provide>> >>> >> 5. Append all fields to master TClientDataSet, and you will se>> >> among them TDataSetField which corresponds to detail TSQLDataSe>> >>> >> 6. Drop another TClientDataSet (for the detail) and assign it>> >> DataSetField property to TDataSetField of master TClientDataSet
Switch to the TSQLDataSets then and have them execute yours stored procs.

> 1) Make the detail proc return all records for all masters and link

Ouch.


What properties did you have in mind? I don't see anything
TSQLStoredProc or TSQLQuery to link to a datasource to get parameter
info. We're probably just not communicating well, I'm just looking for
the stuff you're talking about in the components.

Randy

You have to do this for briefcase mode. It *could* be faster over a
slow network for non-briefcase mode. But it depends on the records you
return, as explained elsewhere.


Same way you link any two master/detail queries with a TDataSource. I
only use regular datasets to do this, so I can't say if it works with
the StoredProc component, but I tend to consider the storedproc
components to be unnecessary anyway.

For TSQLQuery just make sure the detail DS's param has the same name
as the master DS's field, set the DataSource property of the detail DS
to a TDataSource connected to the master. Then instantiate persistent
fields in the master CDS. You'll see a TDataSetField for the detail.
Specify this as the DataSetField property of the detail DS.

--
Craig Stuntz [TeamB] Vertex Systems Corp. Columbus, OH
Delphi/InterBase Weblog : http://www.yqcomputer.com/
How to ask questions the smart way:
http://www.yqcomputer.com/ ~esr/faqs/smart-questions.html