Subject Re: [IBO] mystery with ParamByName
Author Gediminas
--- In, 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
> >
> >
> >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
> 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
> 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
> 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
> statement//. A calculated field is not an output field, so it's not
> 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.