#67 Execute Block - does not work

[Augusto Costa]

I'm trying to use option firebird EXECUTE BLOCK and generates error in Query component ... could you verify it is a very interesting option Firebird.

Thank you,

[EgonHugeist]

Hi,

could you attach a little templete app and a running sql-cript to test it by our own? That would be nice.

Cheers, Michael

[Fr0sT.Brutal]

Partially confirmed.
Tested with this:

    execute block
    as
    begin
      execute procedure proc_log('info', 'test EB');
    end

and also with this:

    execute block
    RETURNS (
      id integer,
      name varchar(10)
    )
    as
    begin
      select first 1 id, name from PEOPLE
        into :id, :name;
      suspend;
    end

• everything's fine (turning ParamsCheck off, obviously). Troubles begin only when you wish to have input parameters:

    execute block (
      id integer = ?
    )
    RETURNS (
      name varchar(10)
    )
    as
    begin
      select name from PEOPLE
        where id = :id
        into :name;
      suspend;
    end

Zeos won't process this SQL throwing error "number of input parameters is less than expected" (text not precise, I've translated it). Manual adding a parameter doesn't help:

    ZQuery2.Prepare;
    ZQuery2.Params.CreateParam(ftInteger, 'id', ptInput);
    ZQuery2.ParamByName('id').AsInteger := 2;
    ZQuery2.Open; <- error

It happens because Statement object has 0 parameters (it has no idea of "?" parameters) so the param value won't be copied. But if I turn ParamCheck on and try SQL

    execute block (
      id integer = :id
    )
    as
    begin
      execute procedure proc_log(null, null);
    end

(simplified for not using block's parameters) - the engine would throw error "SCanNotRetrieveResultSetData" in method TZInterbase6PreparedStatement.ExecuteQueryPrepared.

Should be noted that I tried these tests with my current Zeos version. I don't know which number it has because I haven't found where to learn it.

[EgonHugeist]

Hi Frost.. Lorbs i did overlook your message here.

Well i'll try to find some time to verify your suggestions. I think the issue is related to https://sourceforge.net/p/zeoslib/tickets/96/
So i'll not this relation.

Michael

[EgonHugeist]

Hi all,

sorry for my long absence.. Got it running inbetween: R3881 \testing-7.2

Note:
You won't get it running using the default ParamChar ':' since FB needs it for it's own logic inside execute block. So replace the paramchar by something like '$' or '&' and it will work very well if ParamChaeck = True.

Example:
Query.SQL.Text := 'execute block (P1 integer = &P) returns (P2 integer) as begin select R1 from PROCEDURE1(:p1) into :P2; suspend; end';

Setting ParamCeck to False was broken too, i'm afraid! My fix also handles this case. So my current test succeeds.
Example:
Query.SQL.Text := 'execute block (P1 integer = ?) returns (P2 integer) as begin select R1 from PROCEDURE1(:p1) into :P2; suspend; end';

Hope someone confirms my findings or we'll close the ticked in a periode of two weeks -> all examples included and resolved.

Cheers, Michael

[EgonHugeist]

reopen if you aren't happy