At 07:14 PM 9/09/2011, Geoff Worboys wrote:

>Helen Borrie wrote:
>> It's an expression syntax and it's only available in DSQL.
>> Valid DML statements begin with SELECT, INSERT, UPDATE, DELETE or
>> EXECUTE PROCEDURE. There are no others.
>
>This may be true "officially" but the v2.1.4 release notes say:
>
> - - -
>The SQL language extension EXECUTE BLOCK makes "dynamic PSQL"
>available to SELECT specifications. It has the effect of allowing
>a self-contained block of PSQL code to be executed in dynamic SQL
>as if it were a stored procedure.
>
>[...]
>
>For the client, the call isc_dsql_sql_info with the parameter
>isc_info_sql_stmt_type returns
>
>* isc_info_sql_stmt_select if the block has output parameters.
>The semantics of a call is similar to a SELECT query: the client
>has a cursor open, can fetch data from it, and must close it after
>use.
>
>* isc_info_sql_stmt_exec_procedure if the block has no output
>parameters. The semantics of a call is similar to an EXECUTE query:
>the client has no cursor and execution continues until it reaches
>the end of the block or is terminated by a SUSPEND.
> - - -
>
>Which, by my reading, say that execute block can behave like a
>SELECT statement or an EXECUTE statement. So it may not be valid
>DML, but it can act like one. Yes?

This actually sounded plausible, so I got on ICQ with Vlad and asked him about it. He confirms that EXECUTE BLOCK entered the DML lexicon as verb #6 when he implemented it way back then (Fb 2.0). What I said earlier about EXECUTE BLOCK not being a DML verb was incorrect.

>Of course none of this helps the OP, in which the main problem
>was with keylinks rather than whether the DML was valid or not.

True, on reflection, IBO would be up the creek trying to work out keylinks for any executable statement that was being asked to act like a dataset.

It does seem that it will be OK to persevere with this EXECUTE BLOCK approach, if it is the best way to do that task; but to use a TIB_DSQL rather than a dataset for the statement.

Reminder to OP, unlike the dataset components, you have to explicitly Prepare an executable statement before attempting to assign to any parameters, viz., something along the lines of

if not (myDSQL.Prepared) then
myDSQL.Prepare;

H.