obtain object_id value for executing SQL Anywhere code

[#1]

How can I obtain the object_id value for executing SQL Anywhere code. If accessible, I could then obtain the name of the executing SQL Anywhere code with:

declare my_object_id integer;
declare executing_object_name char (128);

set my_object_id = ???;
set executing_object_name = object_name (my_object_id);

If I knew what to substitute into '???', I could obtain the name of any stored procedure or trigger that is currently executing.

Is there another way to do this?

Thanks, Dan.

[VolkerBarth]

Based on Nick's suggestion, here's test code that relies on the wild guess that for triggers, the @@procid value is 0x80000000 (= 2147483648) + the trigger_id value from systrigger...

I don't think that it's documented, but it seems reasonable that code within triggers (and possibly within event handlers) will have a fitting @@procid value, too. (And it somewhat resembles the documented fact that internal connections have particular ranges for their connection numbers.)

create table T_Test
(
    pk_Test int             not null    default autoincrement primary key,
    data    varchar(255)    not null
);

create or replace proc STP_Test()
begin
    declare my_name varchar(128);
    set my_name = (select proc_name from sys.sysprocedure where proc_id = @@procid);
    waitfor delay '00:00:01';
    message 'Procedure "' || my_name || '" has proc_id ' || @@procid;
end;

create or replace trigger TIU_Test
before insert, update on T_Test
for each row
begin
    declare my_name varchar(128);
    declare trigger_proc_id_offset unsigned int = 0x80000000;
    set my_name = (select trigger_name from sys.systrigger
                   where trigger_id = @@procid - trigger_proc_id_offset);
    waitfor delay '00:00:01';
    message 'Trigger "' || my_name || '" has proc_id ' || @@procid;
    call STP_Test();
end;

create or replace trigger TD_Test
before delete on T_Test
for each row
begin
    declare my_name varchar(128);
    declare trigger_proc_id_offset unsigned int = 0x80000000;
    set my_name = (select trigger_name from sys.systrigger
                   where trigger_id = @@procid - trigger_proc_id_offset);
    waitfor delay '00:00:01';
    message 'Trigger "' || my_name || '" has proc_id ' || @@procid;
    call STP_Test();
end;

insert T_Test (data) values ('abc'), ('def'), ('geh');
delete top 1 T_Test order by pk_Test desc;
call sa_server_messages(NULL, -100);

returns the following in my test on V17.0.4.2100:

Trigger "TIU_Test" has proc_id 2147483662
Procedure "STP_Test" has proc_id 527
Trigger "TIU_Test" has proc_id 2147483662
Procedure "STP_Test" has proc_id 527
Trigger "TIU_Test" has proc_id 2147483662
Procedure "STP_Test" has proc_id 527
Trigger "TD_Test" has proc_id 2147483663
Procedure "STP_Test" has proc_id 527

---

Here's for events:

create or replace event EV_Test
handler
begin
    declare my_name varchar(128);
    declare event_proc_id_offset unsigned int = 0xC0000000;
    set my_name = (select event_name from sys.sysevent
                   where event_id = @@procid - event_proc_id_offset);
    waitfor delay '00:00:01';
    message 'Event "' || my_name || '" has proc_id ' || @@procid;
end;
trigger event EV_Test;

shows the following message log entry:

Event "EV_Test" has proc_id 3221225473

[dhkom]

Thank's Volker for the very detailed response. Indeed this will work. It would be nice if there was a more direct way to get the object_id of executing server code. Perhaps an @@ObjectId global variable, as suggested in my comment to Mark, above.

I am curious why you use a 'waitfor delay' before the 'message' statement?

Thank you.

[VolkerBarth]

Just to "delay" the message output, nothing particular and not really meaningful, I guess...