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.

asked 07 Dec '16, 15:19

dharrel's gravatar image

dharrel
11112
accept rate: 0%

edited 08 Dec '16, 03:58

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676


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.

permanent link

answered 07 Dec '16, 15:59

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 31%

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:

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

permanent link

answered 08 Dec '16, 03:49

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676
accept rate: 33%

edited 08 Dec '16, 03:57

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

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

permanent link

answered 07 Dec '16, 15:48

Mark%20Culp's gravatar image

Mark Culp
23.4k9132275
accept rate: 40%

edited 07 Dec '16, 15:56

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

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.

(08 Dec '16, 09:06) Volker Barth

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...
(08 Dec '16, 09:22) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×106
×61
×35
×25

question asked: 07 Dec '16, 15:19

question was seen: 379 times

last updated: 08 Dec '16, 10:30