Firebird4-Execute block and parameters FAILs

[hamacker]

Query sintax perfect outside Zeos:

execute block (
  P_UUID_AGENDA varchar(36)=:P0     ,
  P_UUID_COLABORADOR varchar(36)=:P1,
  P_DT_NUTIL date=:P2          ,
  P_DESCRICAO varchar(4096)=:P3    )
as
begin
  if (not exists(select * from AGENDA_NUTIL
                where UUID_COLABORADOR=:P_UUID_COLABORADOR
                   and DT_NUTIL=:P_DT_NUTIL)) then
  begin
    update or insert into AGENDA_NUTIL (
      UUID_AGENDA, UUID_COLABORADOR, DT_NUTIL, DESCRICAO)
    values (
      :P_UUID_AGENDA, :P_UUID_COLABORADOR, :P_DT_NUTIL, :P_DESCRICAO)
    matching (UUID_AGENDA);
  end
end

Execute in Lazarus 2.3(rc)/Win32/fpc 3.2/Zeos 7.2.14.0/Firebird 4:

      
      qt1.ParamCheck:=true;
      qt1.prepare;   
      qz1.Params[0].AsString:='babla';
      qz1.Params[1].AsString:='blabla';
      qz1.Params[2].AsDateTime:=StrToDate('01/01/1970');
      qz1.Params[3].AsString:='blabla'; 
      qz1.ExecSQL;  -- fail here

Returning error: invalid request BLR at offset 131 undefined parameter number. Error code: -104. Invalid token The SQL: execute block (repete entire query)
I try turn on/off ParamCheck and the same result. Do I something wrong?

[marsupilami]

Hello hamacker,

I assume that Zeos recognizes ":P_DT_NUTIL" and ":P_UUID_AGENDA, :P_UUID_COLABORADOR, :P_DT_NUTIL, :P_DESCRICAO" as parameters. Please choose another ParamChar (maybe ~?) and use that. This could look like this:

Code: Select all

execute block ( /* note the ~ here */
  P_UUID_AGENDA varchar(36)=~P0     ,
  P_UUID_COLABORADOR varchar(36)=~P1,
  P_DT_NUTIL date=~P2          ,
  P_DESCRICAO varchar(4096)=~P3    )
as
begin
  if (not exists(select * from AGENDA_NUTIL
                where UUID_COLABORADOR=:P_UUID_COLABORADOR
                   and DT_NUTIL=:P_DT_NUTIL)) then
  begin
    update or insert into AGENDA_NUTIL (
      UUID_AGENDA, UUID_COLABORADOR, DT_NUTIL, DESCRICAO)
    values (
      :P_UUID_AGENDA, :P_UUID_COLABORADOR, :P_DT_NUTIL, :P_DESCRICAO)
    matching (UUID_AGENDA);
  end
end

Best regards,

Jan

[hamacker]

Hi I try
qt1.ParamChar:='?'
and not run!
and change to ~, and now it´s run!
Thanks a lot.