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:
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. |
There is a global variable available called @@procid (as part of the TSQL support) that may help. Doing something along the lines of
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. 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.
(07 Dec '16, 16:13)
dharrel
|
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:
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:
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.
(08 Dec '16, 09:51)
dharrel
Replies hidden
Just to "delay" the message output, nothing particular and not really meaningful, I guess...
(08 Dec '16, 10:29)
Volker Barth
|
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
You can also just use the OBJECT_ID() function - e.g.
FWIW: The OBJECT_ID() function simply queries the sysobject table to get the answer! HTH 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.
(07 Dec '16, 16:11)
dharrel
Replies hidden
Ah, so then you want to use OBJECT_NAME() function - see http://dcx.sap.com/index.html#sqla170/en/html/81f83e6a6ce210148bb8a319b96e3f9a.html
(07 Dec '16, 17:49)
Mark Culp
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.
(08 Dec '16, 08:58)
dharrel
Replies hidden
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.
(08 Dec '16, 09:06)
Volker Barth
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
(08 Dec '16, 09:22)
Volker Barth
|