firebird execute statement within an exists

Question

firebird execute statement within an exists

Navigation

#1 by (0 votes)

0

I would like to know how to step in the table name as a parameter

create or alter procedure TESTE (
    TABLENAME varchar(30),
    STATUS varchar(1))
returns (
    INCREMENT integer)
as
declare variable RESULT varchar(254);
begin
  if (STATUS = 'T') then
    begin
      for select rdb$generator_name from rdb$generators
      where rdb$system_flag is distinct from 1 and  rdb$generator_name = 'GEN_' || upper(:TABLENAME) || '_ID' into RESULT do
      execute statement 'select gen_id(' || upper(trim(RESULT)) || ', 0) from rdb$database' into increment;

/* funciona */
      while (exists(SELECT * FROM EMPRESAS WHERE EMPRESAS_ID = :incremento)) do
        incremento = incremento + 1;

/* nao funciona */
/*
      while (exists(execute statement 'select * from ' || trim(:TABLENAME) || ' where ' || trim(:TABLENAME) || '_ID = ' || :increment)) do
        increment = increment + 1;
 */
      suspend;

    end
end

firebird

asked by anonymous 02.01.2016 / 22:16

1 answer

Return separate characters in the same result Error sending input data to php with Ajax

score 0 · Answer 1

I solved the following form:

function TConexoesController.Ultimamo_Codigo (Table, Status: String): Integer; var Query: TFDQuery; begin Query: = TFDQuery.Create (nil); Query.Connection: = getConnection; try try with Query do begin Close; Unprepare; Sql.Clear; if Status = 'T' then Sql.Text: = 'execute block returns (ULTIMO integer)' + the 'declare variable increment integer; '+ 'begin' + 'increment = gen_id (gen _' + Table + '_ id, 1); '+ 'while (exists (select * from' + Table + 'where' + Table + '_ ID =: increment))' 'increment = increment + 1; '+ 'last = increment; '+ 'suspend; '+ end else Sql.Text: = 'select max (' + Table + '_ ID) the LAST from' + Table; Clipboard.AsText: = Sql.Text; Prepare; Open; end; if not Query.IsEmpty then result: = Query.FindField ('LAST') AsInteger else result: = 1; except on e: exception of gera_log (e.message); end; finally Query.Free; end;

end;