Parameter passed to it is segmented BLOB sub_type TEXT, same for returned value. In input BLOB each line of SQL statement is in separate segment making it slightly easier to parse SQL on per-line basis if needed. Format of output BLOB is free - one may follow same segmenting rule or not or even may have it stream, not segmented.

As a trivial sample of such procedure, here we have one adding INTO in INSERT statement – i.e.

 INSERT TEST VALUES(1)

becomes

 INSERT INTO TEST VALUES(1)

and therefore runs fine.

 set term ^;

 create or alter procedure IBE$SQL (f1 blob sub_type text) returns(t1 blob sub_type text) as
 declare variable tmp varchar(1024);
 begin
   tmp = upper(f1);
   if (OCTET_LENGTH(tmp) > 6) then begin
     if (substring(tmp from 1 for 6) = 'INSERT') then
       tmp = 'INSERT INTO' || substring(tmp from 7);
   end
   t1 = tmp;
 end^

 set term ;^

Separately stands a problem of changing procedure. Used is the fast-most method - procedure is loaded when first SQL statement is prepared and that loaded body remains the same during attachment existance. That's OK for production but may not be ideal for debugging procedure - one has to re-attach each time procedure is changed.

back to top of page
<< | Firebird Special Edition by IBExpert | DBEncryption Plugin for Firebird 3.0 >>