Question Re: obtain object_id value for executing SQL Anywhere code in Technology Q&A

obtain object_id value for executing SQL Anywhere code

dhkom — Wed, 07 Dec 2016 15:19:34 GMT

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.

Re: obtain object_id value for executing SQL Anywhere code

MarkCulp — Wed, 07 Dec 2016 15:48:52 GMT

Each type of object (procedure, table, etc) has an object_id column in the corresponding SYS* table (e.g. sysprocedure, systable, etc). So to get the object id of an entity you need to query the associated SYS table.

For example, to find the object id of a procedure, use

select object id from sysprocedure where proc_name = 'my_procedure';

You can also just use the OBJECT_ID() function - e.g.

select object_id( 'myuser.my_procedure' );

FWIW: The OBJECT_ID() function simply queries the sysobject table to get the answer!

HTH

Re: obtain object_id value for executing SQL Anywhere code

Former Member — Wed, 07 Dec 2016 15:59:01 GMT

There is a global variable available called @@procid (as part of the TSQL support) that may help.

Doing something along the lines of

select proc_name from sys.sysprocedure where proc_id=@@procid;

seems to fix your purpose.

This was added back in version 5 and is still documented for V16 and still seems to work in V17. Future versions may or may not continue this tradition.

Re: obtain object_id value for executing SQL Anywhere code

dhkom — Wed, 07 Dec 2016 16:11:30 GMT

Thanks, but my objective was to determine the name of the object - in particular the name of the server code that is executing. Your suggestion has me know the name that I was looking to calculate.

My larger objective, if this helps, is to be able to emit debugging code from any trigger or stored procedure that includes the name of the stored procedure - without having to code the name myself.

Re: obtain object_id value for executing SQL Anywhere code

dhkom — Wed, 07 Dec 2016 16:13:35 GMT

Thanks. I was aware of the @@procid technique. I was hoping to find a similar technique to determine the id of an executing trigger (@@triggerid does not exist), or better yet, a single way to find the object_id of the executing SP or trigger.

Re: obtain object_id value for executing SQL Anywhere code

MarkCulp — Wed, 07 Dec 2016 17:49:03 GMT

Ah, so then you want to use OBJECT_NAME() function - see http://dcx.sap.com/index.html#sqla170/en/html/81f83e6a6ce210148bb8a319b96e3f9a.html

Re: obtain object_id value for executing SQL Anywhere code

VolkerBarth — Thu, 08 Dec 2016 03:49:08 GMT

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

Re: obtain object_id value for executing SQL Anywhere code

dhkom — Thu, 08 Dec 2016 08:58:15 GMT

Indeed - but to use OBJECT_NAME(), I need an object_id value. In stored procedures I have @@Procid. Volker, below, provided a detailed solution for obtaining object_id values within Triggers and Events. He admits the solution is undocumented, and thus could stop working in a future release. It would be nice if an @@ObjectID were available in any server code which could feed into OBJECT_NAME().

Actually, I am wondering whether OBJECT_NAME() is very useful without something like an @@ObjectID global variable.

Re: obtain object_id value for executing SQL Anywhere code

VolkerBarth — Thu, 08 Dec 2016 09:06:09 GMT

He admits the solution is undocumented.

No, I'm just another customer, so I would not even claim that this "solution" is one - it's just a guess that the @@procid has particular values within triggers and events...

Some SAP engineer would have to acknowledge that "solution" first before it could be claimed "undocumented" - and could then possibly tell if it's reliable or not.

Re: obtain object_id value for executing SQL Anywhere code

VolkerBarth — Thu, 08 Dec 2016 09:22:29 GMT

It would be nice if an @@ObjectID were available in any server code which could feed into OBJECT_NAME().

Hm, it seems naturally that running code has a global "self reference" via @@procid. But what would you expect that requested new "@@objectid" to refer to, as that generally may be relate to tables, columns, procedures, users, ... - all kinds of objects. So within running code, it could also make sense to know what table/column/... the code is working on currently...

IMHO, in case the @@procid really does have the guessed meaning for triggers/events, it would be more helpful to

either document it that way, or
add a builtin function (say, "object_id_to_proc_id()") that would map the internal proc id to the object id of the according procedure/trigger/event/whatever can run code...

Re: obtain object_id value for executing SQL Anywhere code

dhkom — Thu, 08 Dec 2016 09:51:27 GMT

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.

Re: obtain object_id value for executing SQL Anywhere code

VolkerBarth — Thu, 08 Dec 2016 10:29:59 GMT

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