Subject Re: [IBO] mystery with ParamByName
Author Helen Borrie
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
>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.

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
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.

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.