1

Topic: To change a body in procedures

Colleagues, I welcome!
Whether the request to prompt there are whom ready decisions or thoughts how in large quantities to change a body of procedures?
The task following:
It is required to book audit on usage of procedures at formation of reports.
In a DB is about 700 procedures for reports.
The first that came to mind it to add a line "insert into..." In a body of procedure after begin, but everyone to correct hands .
Thanks.

2

Re: To change a body in procedures

1. To derive the code of the necessary procedures in a script ([CREATE OR] ALTER PROCEDURE).
2. To process the received script, interposing that is necessary where follows.
3. To roll a script on basis.

3

Re: To change a body in procedures

IBExpert;
Alexander, thanks. Fulfilled extraction of meta data in IBExpert, but a part of procedures were exported with a following type:
[spoiler]

CREATE OR ALTER PROCEDURE RPT_PROC (
FIELD1 INTEGER;
FIELD2 INTEGER;
FIELD3 DATE;
FIELD4 DATE;
FIELD5 DATE;
FIELD6 DATE;
FIELD7 INTEGER;
FIELD8 INTEGER;
FIELD9 INTEGER;
FIELD10 INTEGER)
RETURNS (
R_FIELD1 VARCHAR (30);
R_FIELD2 VARCHAR (255);
R_FIELD3 DOUBLE PRECISION;
R_FIELD4 DOUBLE PRECISION;
R_FIELD5 VARCHAR (30))
AS
BEGIN
SUSPEND;
END;

[/spoiler]
Though in the procedure there is a data handling.
Adjustments in IBE
[spoiler]
[img=https://s8.hostingkartinok.com/uploads/images/2017/10/c2612ae68ccd7d5b515800428c444081.png]
[/spoiler]

4

Re: To change a body in procedures

JohnyCage wrote:

It is required to book audit on usage of procedures at formation of reports.
In a DB is about 700 procedures for reports.
The first that came to mind it to add a line "insert into..." In a body of procedure after begin, but everyone to correct hands .
Thanks.

What then not to correct reversely, I would cause not "insert into" and procedure , would transfer in it parameters type the name of procedure from which was caused . Then in procedure  it is possible also the data  in different places if it is required, and to include/switch off  on the basis of a generator/variable/record state in the table.
It is necessary - included, it is not necessary - ungeared. And it directly on operating basis, without changing meta data and control it is concentrated in one place.

5

Re: To change a body in procedures

fraks;
Precisely. Thanks so it will be valid better and more conveniently.

6

Re: To change a body in procedures

JohnyCage wrote:

Fulfilled extraction of meta data in IBExpert, but a part of procedures were exported with a following type:

These are stubs for  dependences. Search more low for complete definitions - they there are. Here this part needs to be selected and processed.
And if the server allows to modify system tables directly it is possible to be transversed simply on RDB$PROCEDURES, to pull out RDB$PROCEDURE_SOURCE each necessary procedure, to find in it the first begin, to interpose after it necessary and  RDB$PROCEDURE_SOURCE. Then simply to recompile all procedures and to receive a working script.
Certainly, to do it it is necessary not on working basis.

7

Re: To change a body in procedures

IBExpert;
Thanks. I will try.

8

Re: To change a body in procedures

fraks;
And casually do not prompt as to receive a name of executable procedure? The information something did not find...

9

Re: To change a body in procedures

, and I on a subject title thought that it about cosmetic procedures for a weight loss)))
+ For separate procedure of a broad gull
From within procedures to receive her name in any way it is impossible, it is necessary to set a constant

10

Re: To change a body in procedures

Vasily No2;
And thought. I thank. I will then use Alexander's council. (RDB$PROCEDURES)

11

Re: To change a body in procedures

JohnyCage wrote:

It is required to book audit on usage of procedures at formation of reports.

For this purpose it is not required to change a body of procedures. It is enough to include audit.

12

Re: To change a body in procedures

No2 wrote:

From within procedures to receive her name in any way it is impossible, it is necessary to set a constant

Yes it is fine, and as MON$CALL_STACK

13

Re: To change a body in procedures

vvvait wrote:

yes it is fine, and as MON$CALL_STACK

Unfortunately it is not so efficient.

14

Re: To change a body in procedures

afgm, by no means

create or alter procedure SET_CALLER_NAME
as
declare variable NM varchar (100);
declare variable TR integer;
begin
TR = current_transaction;
in autonomous transaction
do select cs. MON$OBJECT_NAME
from MON$CALL_STACK cs
where exists (select *
from MON$CALL_STACK cs1
join MON$STATEMENTS s on s. MON$STATEMENT_ID = cs1.MON$STATEMENT_ID and s. MON$STATE = 1
where s. MON$TRANSACTION_ID =:TR
and upper (cs1.MON$OBJECT_NAME) = ' SET_CALLER_NAME'
and cs1.MON$CALLER_ID = cs. MON$CALL_ID)
into:NM;
RDB$SET_CONTEXT (' USER_TRANSACTION ', ' CALLER_NAME ':NM);
end
create or alter procedure TEST_A
returns (NM varchar (100))
as
begin
execute procedure SET_CALLER_NAME;
NM = RDB$GET_CONTEXT (' USER_TRANSACTION ', ' CALLER_NAME ');
RDB$SET_CONTEXT (' USER_TRANSACTION ', ' CALLER_NAME ', null);
suspend;
end
create or alter procedure TEST_B
returns (NM varchar (100))
as
begin
execute procedure SET_CALLER_NAME;
NM = RDB$GET_CONTEXT (' USER_TRANSACTION ', ' CALLER_NAME ');
RDB$SET_CONTEXT (' USER_TRANSACTION ', ' CALLER_NAME ', null);
suspend;
end
select TEST_B.NM, TEST_A.NM
from TEST_B
join TEST_A on 0 = 0

15

Re: To change a body in procedures

Dimitry Sibiryakov wrote:

it is passed...
For this purpose it is not required to change a body of procedures. It is enough to include audit.

! Strange that more nobody writes also all invent crutches. Study . It is the strong piece.

16

Re: To change a body in procedures

vvvait wrote:

afgm, by no means

And now we test it on... Not to the most extremal loading.

17

Re: To change a body in procedures

afgm, well I do not know, superfluous 0,5 - 1 msec it is critical? Can on a heap of connections will it is necessary try worse.

18

Re: To change a body in procedures

vvvait wrote:

afgm, well I do not know, superfluous 0,5 - 1 msec it is critical? Can on a heap of connections will it is necessary try worse.

For difficult handlings where many procedures an overhead projector  very notable are caused.
And the amount transaction will repeatedly increase that can even be more sick.

19

Re: To change a body in procedures

vvvait;
Here an example showing that many connections is not necessary

-- The simple table
CREATE TABLE TEST_DATA_TABLE (
ID INTEGER;
CAPTION VARCHAR (50)
);
-- With the trigger on an insertion
CREATE OR ALTER TRIGGER TEST_DATA_TABLE_BI FOR TEST_DATA_TABLE
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new.id is null) then
new.id = gen_id (gen_test_data_table_id, 1);
end
-- We interpose such 
create or alter procedure TEST_DATA_TABLE_INS (
CAPTION varchar (50))
as
begin
insert into TEST_DATA_TABLE (CAPTION)
values (:CAPTION);
end
-- The code of an insertion
execute block as
declare variable n int = 10000;
begin
while (n> 0) do
begin
execute procedure test_data_table_ins (' TEST_CAPTION ');
-- execute procedure SET_CALLER_NAME;
n = n - 1;
end
end
/*
TOTAL:
Without   94ms
With  causing 10s 857ms
*/

20

Re: To change a body in procedures

afgm, well it also is 1 msec on a call

21

Re: To change a body in procedures

vvvait wrote:

afgm, well it also is 1 msec on a call

It is twisted from loading.
monitoring tables with high frequency - it is expensive
Pack of superfluous transactions - it is expensive