How do I store the value of the field returned with the "insert" command in a variable?

0

I have a Procedure that performs the "inserts" and "updates" in the tables. The need to create it was to try to centralize all the scan functions before inserting or updating records. Today there was a need to return the value of the table's "ID" field so that my application can locate the registry and perform other procedures.

Stored Procedure:

SET TERM ^ ;

CREATE OR ALTER procedure sp_insupd (
    iaction varchar(3),
    iusuario varchar(20),
    iip varchar(15),
    imodulo varchar(30),
    ifieldsvalues varchar(2000),
    iwhere varchar(1000),
    idesclogs varchar(200))
returns (
    oid integer)
as
declare variable vdesc varchar(10000);
begin
  if (iaction = 'ins') then
  begin
    vdesc = idesclogs;

    /*** o erro está na linha abaixo ***/
    execute statement 'insert into '||:imodulo||' '||:ifieldsvalues||' returning ID into '||:oid||';';
  end else
  if (iaction = 'upd') then
  begin
    execute statement 'select '||:idesclogs||' from '||:imodulo||' where '||:iwhere into :vdesc;

    execute statement 'execute procedure SP_CREATE_AUDIT('''||:imodulo||''');';

    execute statement 'update '||:imodulo||' set '||:ifieldsvalues||' where '||:iwhere||';';
  end

  insert into LOGS(USUARIO, IP, MODULO, TIPO, DESCRICAO) values (
  :iusuario, :iip, :imodulo, (case :iaction when 'ins' then 1 when 'upd' then 2 end), :vdesc);
end^

SET TERM ; ^

The error in the above line is occurring due to syntax error. The Procedure is compiled normally, ie the error does not occur in the compilation, because the line in question is executed through the "execute statement". When there was no need to return the value of the "ID" field, Procedure would normally work with the line like this:

...
execute statement 'insert into '||:imodulo||' '||:ifieldsvalues||';';
...

What would be the correct way for the value of the "ID" field to be stored in the "OID" variable?

    
asked by anonymous 15.11.2017 / 03:52

1 answer

0

I decided to publish the same question in the Stack Overflow English version because I was very urgently in a solution. I got an answer and I decided to post here so I can help someone in the future.

The line mentioned in the code that was causing error has been replaced by the following ...

execute statement 'insert into '||:modulo||' '||:ifieldsvalues||' returning ID' into :OID;

Question link in Stack Overflow English version

    
15.11.2017 / 19:03