At 05:08 PM 23/11/2005 +0100, you wrote:

>How can I use fields from a MasterSource in a TIB_Query as Parameters
>for a selectable Stored Procedure.
>
>SelectSQL: select * from PhoneSelect(:idMaster);
>
>where idMaster is the FieldName in the MasterSource dataset.
>
>I have tried MasterLinks, but this only added a where-clause to the query.
>
>I have searched in help files and in source, but did not fnd any solution.
>Thanks for help,

Masterlinking is not appropriate for this task, since the stored procedure
output has only a virtual relationship with the "master" set. The word
"parameter" is used by all of us quite loosely; but, in fact, the input
"parameter" for a SP is not a parameter (in the Delphi sense of a filter on
a real set) but an **argument**.

It's not desirable to "parameterize" (in the Delphi sense) the output
(Fields[] array) of a selectable SP. You can - but it is horribly
wasteful. That's why we write SSPs so that we utilised the input arguments
of the SSP to do the restricting work that we use the WHERE clause for in
dynamic SELECTs.

You have some alternatives here.

One is to use a TIB_StoredProc instead of a TIB_Query for the SSP. In this
case you can set this up formally as master-detail, using MasterParamLinks
to pass the input to the dataset's input parameter, provided you set the
StoredProcForSelect property true. The main disadvantages of
TIB_StoredProc are that 1) it outputs a unidirectional output set and 2) it
uses more resources than a TIB_Query and 3) you will have some tricky work
to do if your master set is updatable, to avoid cursor exceptions in the
detail set.

The other - and the one I prefer - is to use tib_query and do the linking
yourself. This is simple. It is a question of writing the appropriate
(simple) handler to manage the child set when something happens in the
parent set. You can code this all in one function. The OnDataChange event
of the parent set's datasource will be the place to call your function,
since that event fires not just when a field's data changes but also when
the set's current row changes.

Another - worth seriously considering if your PhoneSelect procedure
actually does something as simple as pulling a set of entries structured,
say, as "MasterID", "PhoneType", "PhoneNumber" - is to do this set with a
view instead of a SSP. In that case, the normal master-detail rules apply
- the relevant rows are retrievable by way of actual Delphi parameters,
i.e. WHERE criteria in a dynamic SELECT.

I have some strong feelings against using SSPs where they are not
needed. Of course, my arguments run counter to the notion that some people
hold to, that putting ALL of your derived sets into SSPs somehow makes your
database "object-relational". My argument says "Phooey to that. Firebird
isn't an O-R database; it's about as pure relational DBMS as it gets."

Anyway...whatever...my recommendation is to use SSPs if you really need
them to "cook" your data. There are situations where SSPs can be
life-savers: when the dynamic SQL available can't achieve the output you
want, or does it too inefficiently. But, if your case is as simple as it
seems, the SSP is probably a gross overkill.

Helen