--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...> wrote:

> At 05:11 PM 15/11/2004 +0000, you wrote:
> >TIB_Query ParamByName() mystery, can't figure out
> >Dataset's atributtes:
> >SQL :select * from Tu(:TuID )
> >InsertSQL: execute procedure InsTu(:Tu,:SoID )
> >DeleteSQL: execute procedure DelTu(:TuID,:SoID )
> >CalculatedFields: TuID decimal(15,0)
> >KeyLinks : SoID
> >MasterParamLinks: TuID=TuID
> >
> >CREATE PROCEDURE TU(TUID DECIMAL(15, 0))
> >RETURNS(SOID DECIMAL(15, 0),Name VARCHAR(33))
> >
> >Place, where got error (TIB_StatementError: fieldname SoID not found):
> >qry->ParamByName("SoID")->Assign(qry2->FieldByName("SoID")); <- error
> >qry->Delete();
> >
> >Tried to change qry->ParamByName("SoID") to FieldByName("SoID") error:
> >Tu.SoID is readonly - this is OK as I guessed
> >
> >Don't understand why this happens - why can't use ParamByName? I
> >thought, that ParamByName are for the SP params.
>
> The info you provided here is inadequate to advise you about what's

going

> on...but if SoID is not returned as a field by your SSP then it is not
> valid as an input argument to your xxxxSQL statements.

Helen, what additional info is needed?
SoID is returned as a field by SSP, so I guess SoID must be valid as
an input argument.

> Some confusion comes from Delphi's use of the concept of

"parameters" for

> two quite different things. Let's call the input parameters for a

SP by

> their proper name, "arguments". Let's limit the concept of

"parameters" to

> the columns referred to in search expressions. Delphi rolls them all
> together and handles them all as Params. When designing your

application

> it's important for you to understand that they are different.
>
> Using a calculated field (SoID) as input to a bound xxxxxSQL

statement is

SoID isn't declared as calculated field - it's returned by the SSP,
SoID is PK and set as keylinks. TuID is declared as calculated field.

> not valid for statements bound to table-based sets. It is not valid

for

> virtual sets, either. On the server side, the underlying reasons are
> different, but Delphi doesn't know that.
>
> When you bind a xxxxSQL to a dataset, the binding is between fields

in the

> dataset and fields in the set that the xxxxSQL statement searches
> for. When the xxxxSQL is an execute call to a stored procedure,

there are

> no "search fields", since the call is not an update, insert or delete
> statement. So, Delphi must be able to bind fields in the \\ output

set of

> the SELECT statement // to the \\ input arguments of the EXECUTE

PROCEDURE

> statement//. A calculated field is not an output field, so it's not

valid

> to be a parameter for the bound EXECUTE PROCEDURE statement.

SoID is returned as a field from the SSP.
Correct me, if I understood wrong: TuID isn't needed to be declared as
calculated field - it's only a argument to SP?
I commented calculated field TuID declaration with the insert/delete
SP. No errors until I uncomment for example delete SP and try to set
DeleteSQL SoID argument: qry->ParamByName("SoID")->Assign(...) - error
SoID is not found.
How this could be? if SoID is not calculated field, it's returned from
the SSP, so SoID must exist and bind.

> There isn't much information provided...but possibly you are using sets
> from stored procedures to implement a self-referencing table ("tree")
> structure. As a rule, it's better to use views for this, if you
> can. Stored procedure output sets are convenient, but they cause your
> output sets to be detached from the ongoing state changes in the
> database. In order to get a fresh view of database state after a DML
> operation, you have no choice but to completely re-run BOTH the

master and

> detail procedures in order to get the latest structure to work with.

I could provide detailed SSP (uses several joins to return SoID) text
with the involved table structure if needed.